sqlplus automation problem in a .bat file
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: sqlplus automation problem in a .bat file

  1. #1
    Join Date
    Sep 2001
    Location
    Mexico
    Posts
    93

    sqlplus automation problem in a .bat file

    Hi friends, I have a big problem with sqlplus on windows 2000.
    I'm trying to monitor how many users are connected in each database server of my network, but some servers are not always up.

    I'm using sqlplus in windows 2k for generate this report, but when I try to connect to a not-running server it sends me an error (ORA-12560 TNS: protocol adapter error) and the .bat who calls cannot continue. Obviously this error is produced because the server is not running, but I need that my .bat continue executing the next instruction to obtain the rest of the information.

    I need to automate this .bat via the schedule task in order to run this script every 10 min and to know statistic information about my users.

    How can avoid that sqlplus stops and continue with the rest of my .bat ?
    This is my code:

    -- .bat file --
    sqlplus system/manager@server1 @extractinfo.sql param1
    sqlplus system/manager@server2 @extractinfo.sql param2
    sqlplus system/manager@server3 @extractinfo.sql param3
    sqlplus system/manager@server4 @extractinfo.sql param4

    Thanks in advance, any help will be very useful.

  2. #2
    Join Date
    May 2002
    Posts
    2,645

    Re: sqlplus automation problem in a .bat file

    Originally posted by Turin

    I'm using sqlplus in windows 2k for generate this report, but when I try to connect to a not-running server it sends me an error (ORA-12560 TNS: protocol adapter error) and the .bat who calls cannot continue. Obviously this error is produced because the server is not running,
    Well, obviously not. TNS-12560 is for other reasons. This is what it looks like when the server is not running:
    Code:
    SQL> conn system/manager@sys1
    ERROR:
    ORA-01034: ORACLE not available
    ORA-27101: shared memory realm does not exist
    SVR4 Error: 2: No such file or directory
    
    
    Warning: You are no longer connected to ORACLE.
    SQL>
    --OR---

    Code:
    SQL> conn / as sysdba
    Connected.
    SQL> shutdown
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> conn system/manager
    ERROR:
    ORA-01034: ORACLE not available
    
    
    Warning: You are no longer connected to ORACLE.
    SQL>

  3. #3
    Join Date
    May 2002
    Posts
    2,645
    Put an exit at the end of each called script, otherwise the first invocation of sqlplus will keep you there.


    sqlplus system/manager@server1 @extractinfo.sql param1
    sqlplus system/manager@server2 @extractinfo.sql param2
    sqlplus system/manager@server3 @extractinfo.sql param3
    sqlplus system/manager@server4 @extractinfo.sql param4

  4. #4
    Join Date
    Sep 2001
    Location
    Mexico
    Posts
    93
    I really like to automate it, in any case of error.

    How can avoid that sqlplus stops waiting for the user interaction when there is a connection error?

    Thanks again

  5. #5
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    Easy option : call sqlplus with NOLOG , this way :

    C:\>sqlplus /NOLOG @test_connection.sql

    and in your test_connection.sql script, you use something like :

    whenever sqlerror exit
    connect user/pass@alias
    exit


    Regards,

    Chiappa

  6. #6
    Join Date
    Sep 2001
    Location
    Mexico
    Posts
    93

    Talking THANK YOU VERY MUCH!

    Thank all of you!
    Your ansers were very useful. Now I can finish my script.

    Regards

Posting Permissions

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


Click Here to Expand Forum to Full Width