sqlplus automation problem in a .bat file

[Turin]

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.

[stecal]

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:

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---

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>

[stecal]

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

[Turin]

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

[JChiappa]

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

[Turin]

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

Regards