0

I am trying to run a shell script (ksh) that runs oracle sqlplus to run a sql script in the background. Within the sql script I am using WHENEVER SQLERROR EXIT SQL.SQLCODE to trap the error code so I can check it and exit my program, else continue to next sql statement.

I'm not sure if my problem is in my shell script trapping the error or the use of WHENEVER? I am producing/invoking the ORA- error, but return code still shows as Return_code=0, (pass) and continues to run the next script. It should fail and exit the program.

Can someone help me configure this script properly? My if-then-else logic maybe flawed too. Thanks.

Here's my sql (whenever.sql) script to invoke error:

WHENEVER SQLERROR EXIT SQL.SQLCODE
begin
  SELECT COLUMN_DOES_NOT_EXIST FROM DUAL;
END;
/

Here's my script:

KEY=$BASEDIR/.keyinfo;
LOG=$BASEDIR/run_tst.log;

# Check before we run
if [ -f "$KEY" ]
then
IFS="
"
set -A arr $(cat $KEY)
echo "Running Test ===>$TIMESTAMP" >> $LOG 2>&1
/bin/sqlplus ${arr[0]}/${arr[1]} @whenever.sql &
pid1=$!
echo "Waiting for PID:$pid1" >> $LOG 2>&1
wait $pid1
ret=$?
echo "Return_code=$?"  >> $LOG 2>&1
if [ $ret !=0 ] #if not success
then
     exit $ret
     echo "Error found...Return_code=$?" >> $LOG 2>&1
     echo "Error found...exiting program ===>$TIMESTAMP" >> $LOG 2>&1
     exit 1
else
     /bin/sqlplus ${arr[0]}/${arr[1]} @tst2.sql
fi
else
   echo "key not found. Exiting.  ==>$TIMESTAMP" $LOG 2>&1
fi
exit 0

Results (Showing 0, since there is error, should be something else other than 0).

Running Test ===>20130825-09:25
Waiting for PID:6383
Return_code=0

I also tried WHENENVER SQLERROR EXIT 1 and still getting same result of Return_code=0

Output from testing:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

  SELECT COLUMN_DOES_NOT_EXIST FROM DUAL;
         *
ERROR at line 2:
ORA-06550: line 2, column 10:
PL/SQL: ORA-00904: "COLUMN_DOES_NOT_EXIST": invalid identifier
ORA-06550: line 2, column 3:
PL/SQL: SQL Statement ignored


Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
./run.sh[38]: test: Specify a parameter with this command.
Improve this question
8
  • It could be that sqlplus always returns 0 (success) even when the SQL it executes fails. I've known other SQL command interpreters work like that — and it makes them painful to use in shell scripts. Maybe you should run the broken SQL directly from your ksh command line and look at $? immediately after sqlplus exits. If it says 0, that is the problem. If not, let me know and I'll remove this comment. Commented Aug 26, 2013 at 4:39
  • if i'm putting the script in the background using &, wouldn't I want to look for $! instead? Commented Aug 26, 2013 at 15:04
  • 1
    If you're running a script in background (with &), your only interest in $! (the background process's PID) is perhaps to wait for it to finish. I don't think you can find the exit status of a background process in shell — though there might be a bash extension I've not noticed (discovered, searched for) to get it that I'm not aware of. When you run something in background, you're saying "get on with; I don't care about the result", roughly. In your script, you barely need to use background. You run the process, report its PID, and then wait for it. Commented Aug 26, 2013 at 15:10
  • 1
    OK; I RTFM'd — can't you? You can't run wait $! because of the C shell's baleful influence on bash. But, you can capture $! in a variable: x=$! and then wait $x, and the exit status $? of the wait is the exit status of the background process. Commented Aug 26, 2013 at 15:19
  • 1
    @AlexPoole: Yup. There's a good reason my comments aren't an answer! Once you start finding bugs, there are usually lots lurking where the first is found. The fragment echo "Return_code=$?" >> $LOG 2>&1 if [ $ret !=0 ] #if not success then exit $ret echo "Error found...Return_code=$?" >> $LOG 2>&1 echo "Error found...exiting program ===>$TIMESTAMP" >> $LOG 2>&1 exit 1 has numerous problems. The first and second echos should use $ret instead of $?; the test condition is malformed (if [ $ret != 0 ]); the exit $ret means the following echos are not executed. Commented Aug 26, 2013 at 17:44

2 Answers 2

Reset to default
1

When you display your return code with this:

ret=$?
echo "Return_code=$?"  >> $LOG 2>&1

... the $0 is now the result of the assignmment of ret on the previous line, not the earlier sqlplus, so it's always gping to be zero. You also have an early exit in the if block, and you won't see the messages after that, and your test condition is missing a space (and I prefer -ne to !=):

...
ret=$?
echo "Return_code=$ret"  >> $LOG 2>&1
if [ $ret -ne 0 ] #if not success
then
     echo "Error found...Return_code=$ret" >> $LOG 2>&1
     echo "Error found...exiting program ===>$TIMESTAMP" >> $LOG 2>&1
     exit $ret
else
...

I also prefer to enclose variables in braces, e.g. ${ret}, but I guess that's a matter of taste unless there is ambiguity. You can also call sqlplus with a -s flag to hide the banners in the log, incidentally.

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

4 Comments

Got it. With your suggestions, I am able to capture the error now without returning code of 0. Since I am performing a wait because I have to make sure that script finishes, should I not check for $! inestead? thanks.
@jdamae - $! gives you the pid of the process you just put in the background, not its status; you're already using that to set $pid and when you wait $pid, and immediately after that the $? is the result of the process that was in the background. You already seem to know how to do that part, and that just wait or wait $! would not work. But as others have said, putting this in the background at all seems a bit pointless at the moment.
@jdamae - also be aware that $ret won't usually be exactly what you expect from SQL.SQLCODE; the return code from a command will be in the range 0-255, so it'll be the mod of the real value (which could actually be zero, I suppose). The details may be shell-specific, but be careful.
let me add that, i'm going to build on this. the reason why i was using wait is because I would eventually run 1 or more sql statements in parallel within this script, thus setting them each in the background. Once all those sql statements were complete, run one last one and the reason for my main question was in the case one of the sql statments had an error that I would exit the program all together.
1

Try adding exit at the end of your script:

WHENEVER SQLERROR EXIT SQL.SQLCODE
begin
  SELECT COLUMN_DOES_NOT_EXIST FROM DUAL;
END;
EXIT;

EDIT: Assuming sqlplus returns the error code, try eliminating the background process in your script. It can be shortened to:

/bin/sqlplus ${arr[0]}/${arr[1]} @whenever.sql && /bin/sqlplus ${arr[0]}/${arr[1]} @another.sql || echo "Error..."
Improve this answer

2 Comments

No luck. I tried adding that. Still getting the same result where it returns 0 in my logfile and continues to next script. I also go this in the output: ./run.sh[38]: test: Specify a parameter with this command.
Getting close. Thanks. I managed to get out of the script upon error. Although, I'm still confused as to why return code is still the same. Return_code=0.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.