Batch file hangs at SQL Error

Question

I am using a batch file my.bat as given below:

@echo off
setlocal enabledelayedexpansion
for /F "tokens=*" %%A in (user.txt) do (
    sqlplus -s -l %%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 200
set linesize 200
select username, expiry_date from user_users;
exit;

The problem I am facing is, script my.bat captures here all the required details in output.txt along with SQL ERRORS (ORA-01017: invalid username/password; logon denied ,ORA-12154: TNS:could not resolve the connect identifier specified, ORA-28000: the account is locked ) but its getting hanged at the point whenever it encounteres below error message :

ERROR:
ORA-28001: the password has expired

Changing password for readuser

Please can you let me know how I can ignore this ERROR message too and keep my script running further ?

try this stackoverflow.com/questions/1966671/skip-when-error-occurs — Exhausted
– Exhausted, Commented Feb 5, 2015 at 11:20
@Exhausted - the issue seems to be with SQL*Plus 'hanging' at a password prompt, not with the batch file handling the error; it never sees the problem since SQL*Plus never returns. — Alex Poole
– Alex Poole, Commented Feb 5, 2015 at 11:34

Alex Poole · Accepted Answer · 2015-02-05 11:49:26Z

You can have whenever sqlerror exit failure in your SQL script, but because you only run that after a successful connection it won't catch this error.

You could instead launch SQL*Plus without logging in, using the /nolog switch, and then connect explicitly:

@echo off
setlocal enabledelayedexpansion
for /F "tokens=*" %%A in (user.txt) do (
  (
    echo whenever sqlerror exit failure
    echo connect %%A
    echo @fetch.sql
  ) | sqlplus -s /nolog
) >> output.txt

This also means your credentials aren't supplied on the command line; not sure about Windows so much but on other platforms that is a considerable security risk. Of course you still have them stored in a plain text file which itself isn't very secure, so that's more of a general point.

You could put your fetch.sql statements directly in the batch file if you prefer, by echoing those instead of the @ start command; same effect but one less file to maintain.

Collectives™ on Stack Overflow

Batch file hangs at SQL Error

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related