Suppress SQL*PLUS error in batch script

Question

I have a script db.bat as below:

@echo off

setlocal enabledelayedexpansion
 for /F "tokens=*" %%A in (user.txt) do (
    sqlplus -s %%A @fetch.sql  >> output.txt 

 )

where user.txt (list of all user details for which I need expiry date. this list may have around 40-50 rows) is:

dbuser/password@database1
readuser/p@ssw0rd@database1
adminuser/Pa$$word@database2
.......
.......
.......

and fetch.sql is:

set pagesize 20
set linesize 200

select username, expiry_date from user_users;
exit;

The problem I am facing here is, whenevey my script db.bat encounters any SQL ERRORS like given below, its not moving further and getting hanged at that point until I manually stop that.

SQL ERRORs:

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


ERROR:
ORA-28000: the account is locked

I have checked that there is a WHENEVER SQLERROR command that works in this situation but don't know how I can use it here.

Alex Poole · Accepted Answer · 2015-01-14 15:29:56Z

3

For those kinds of errors, SQL*Plus is 'hanging' at a username prompt, as it hasn't been able to connect. You don't see that because of the -s flag. By default it will allow three attempts, which is useful when running interactively, but isn't helpful when run from a script like this. You can make it exit after the failed login with the -l 'logon' option:

sqlplus -s -l %%A @fetch.sql  >> output.txt

answered Jan 14, 2015 at 15:29

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sunny Over a year ago

...I just observed that its getting hanged on ERROR: ORA-28001: the password has expired Changing password for USERNAME...despite using -l flag :-( any suggestion please ? :-)

Alex Poole Over a year ago

@Sunny - sorry, missed this comment notification; it's probably useful as a separate question/answer anyway...

Shankar · Accepted Answer · 2015-01-14 15:06:59Z

0

Try this, when using the fetch.sql in a script, you need to set the termout to off. The error is still there, only that your script will continue to execute after it.

set pagesize 20
set linesize 200

whenever sqlerror continue 
set termout off 

select username, expiry_date from user_users;
exit;

answered Jan 14, 2015 at 15:06

Shankar

8798 silver badges16 bronze badges

1 Comment

Sunny Over a year ago

If I am doing like this then there is only Error messages are recorded in output.txt.

Collectives™ on Stack Overflow

Suppress SQL*PLUS error in batch script

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related