Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2008
    Posts
    2

    Question Unanswered: batch file for oracle for a specific schema

    Hi,

    I need to create a bat file for accessing an oracle system on a remote machine. I have the following details:

    1. IP of the machine.
    2. The port for oracle 1521
    3. schema name in which i need to do th changes
    4. password for the schema
    4. script path and name to be execute

    I tried using this but did not work:

    @ECHO OFF
    CLS
    sqlplus -S <sysdba>/<dba password>@<IP of the DB machine>:<Port>/<schema name> @<script path and name>
    ECHO.
    ECHO script run successfully!!
    ECHO.
    PAUSE
    EXIT

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, people *usually* access Oracle on a remote machine. In production systems, it is rare (and kind of silly) that you have a database server installed on your own PC, along with other software products you often use.

    Therefore, as you are starting SQL*Plus, you must have (at least) Oracle (client) on your PC. That's fine. Now locate TNSNAMES.ORA file and create a database alias for the remote database. If you are uncertain of how to do that, ask the DBA for the details. He/she might even send it to you. Because, it appears that you don't know SERVICE NAME. Your entry might look like
    Code:
    YOUR_DB_ALIAS = 
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = <IP address goes here> (PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = <service name goes here>)
        )
      )
    The rest is easy: don't connect as "sysdba", connect as user whose data you'd like to modify within the SQL script. For example: if there's the "TEST" table (copy of Scott's "DEPT" table), here's a sample TEST.SQL
    Code:
    update test set
      loc = null;
    
    exit;
    as well as TEST.BAT
    Code:
    sqlplus -s scott/tiger@ora10 @test.sql
    Now run it:
    Code:
    M:\>test
    
    M:\>sqlplus -s scott/tiger@ora10 @test.sql
    
    4 rows updated.
    
    M:\>
    That should be all.

  3. #3
    Join Date
    Dec 2008
    Posts
    2
    thanks
    it worked!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •