CODEWITHSUNDEEP
oracle-databasesqlplus
Fredv
Fredv

Reputation: 552

How to make a sqlplus quit when database is not available?

i have a problem with sqlplus on windows batch. I have SQLPLUS 10.2

i'm trying to connect to a database through a windows script.cmd

script.cmd only launches : sqlplus test/Test@mydatabase @script.sql

The problem is when the database is not available, the sqlplus says 

ERROR:
ORA-12541: TNS:no listener
Enter user-name :

and waits for input .. and blocks the .cmd

How can i adapt the script to stop immediately when the database is not avaliable or just to avoid waiting for user prompts ?

Thanks

Upvotes: 6

Views: 7719

Answers (4)

pacomix
pacomix

Reputation: 104

I see this question is old but this addresses the problem in a clean way too without the need to use the -l flag although the -l flag exist for that exact same reason:

sqlplus test/Test@mydatabase @script.sql | grep -q 'ERROR' && echo "ERROR CONNECTING" || echo "SUCCESS"

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191285

You can do sqlplus -l test/Test@mydatabase @script.sql; the -l flag means it will only try to connect once, and if it fails for any reason will exit instead of prompting. Look at the output of sqlplus -?, or see the documentation.

Upvotes: 17

CristiC
CristiC

Reputation: 22698

You can try with 2 connects.

First one will spool result to a file.

In the second one check what is in that file. If you don't encounter ORA-12541 or any other error messages then call the second script.

You can make all these commands inside one batch script and call it with 

SQLPLUS @script.sql

and inside use 

connect test/Test@mydatabase

Upvotes: -1

heximal
heximal

Reputation: 10517

may be you should think about using TNSPING utility. You can use it within CMD script before trying to connect to db with sqlplus. In this case you should analyse its output.

Upvotes: 0

Related Questions