Re: How do I retrieve an error status during sqlplus execution???

Laura González wrote in message <3770DAAC.B23D71F8_at_stl.es>...
>I have several shell scripts with calls to sqlplus, similar to:
<snipped>

>Now, if I ask for the status immediatly after de 'end' of may sqlplus, I
>always obtain OK, even in the case of the full rollback segment - I
>supose this is because sqlplus is ok, the problem was inside it -.

By checking the status I assume you look at the UNIX process exit code of sqlplus using UNIX environment variable $? - in that case yes, it tells you the sqlplus program executed "ok" and not what and if SQL/Oracle related errors occured.

What you can do is to redirect the output to a file and then grep that file for Oracle errors. Something like this: --
# run sqlplus in silent mode to limit the std output sqlplus -s scott/tiger <<EOF >$$.log
SELECT error FROM dual;
EXIT;
EOF ERROR_MESSAGE=`grep ORA- $$.log`
# if grep's exit code is zero, then a match was found if [ "$?" = "0" ]
then
  echo "Oracle Error(s) occured: $ERROR_MESSAGE" fi
--

You can replace the string to grep for, with any other output that indicates an error.

Also, instead of redirecting STDOUT to a log file, you can use the SPOOL command in SQL*Plus to write to a log file.

Hopes this helps.

regards,
Billy Received on Thu Jun 24 1999 - 02:35:11 CDT