CODEWITHSUNDEEP
sqlwindowsoracle-databasebatch-file
Sunny
Sunny

Reputation: 8312

Batch file hangs at SQL Error

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 ?

Upvotes: 1

Views: 796

Answers (1)

Alex Poole
Alex Poole

Reputation: 191380

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.

Upvotes: 2

Related Questions