Loop

UNIX AIX
1

Hi,
I have a ksh script as following:

for ORACLE_SID in DB1 DB2
do

sqlplus -s system/$mypwd@$ORACLE_SID << EOF
whenever sqlerror exit sql.sqlcode;
set echo off
--set heading off

@/`hostname`/script.sql
exit;
EOF

done

My problem is that if DB1 is not up script fails and does not continue with DB2 which is up. How Can we do in a way that even if DB1 is not open, script goes to do with DB2?
Thans.

2

Is mypwd assigned but you chose not to show that for security reasons ?

Is hostname a placeholder, a variable missing a $, or an actual root directory ?

If you reverse the order of DB1 DB2, does DB2 work and DB1 fail ?

Are you certain it works properly if both DB1 and DB2 are up ?

You might comment out set echo off and see if that shows some anomaly in the sql script.

You could try running the inside of the loop in a sub-shell (or even make it a separate script). Putting the sqlplus in a child process or script is a stronger guarantee that the sqlplus outcome cannot affect the for loop.

As shown, there is no feedback from sqlplus to ksh. No check on status (and IIRC sqlplus always returns 0). So that for in ksh has no reason to not loop. Maybe add debug after the do and before the done.

One possibility is that the EOF does not match (maybe hidden whitespace or a CR), in which case the HereDoc extends to the end of the script and consumes the done. I would expect a ksh diagnostic in that case, but it might just exit.

1 Like
3

A command in backticks. I edited the initial post and added triple-backticks markups - now they are visible.

Actually the loop should not stop on error.
Ensure there is no
set -e
in the script.

4

Thanks.
I can only say that it works well when both DB1 and DB2 are up .
And there is no "set -e"
Regards.

5

Why not check the dB is up before attempting to execute the script? basic defensive coding

6

So cram it with debug and show the results. I don't have sqlplus or DB1 or DB2, but I wrote an external script called sqlplus and had it report all the args and the here document, and return a non-zero status, and be terminated by timeout, and not be found, and the loop still works for both DBs every time.

for ORACLE_SID in DB1 DB2
do

echo; echo "Looped for @$ORACLE_SID"    #.. <== Did it get here

sqlplus -s system/$mypwd@$ORACLE_SID << EOF
whenever sqlerror exit sql.sqlcode;
set echo off
--set heading off

@/`hostname`/script.sql
exit;
EOF

echo "Exit status: $?"    #.. <== Did it get here

done
7

Thanks.