Error Handling - Oracle SQL

[uform]

Hi,

One of my shell script calls the following SQL.

echo $ORA_PW|sqlplus $ORA_ID@$DB @$SQL/inventory.sql 1>$LOG/inventory.log 2>$STD_ERR/inventory.err

I usually read the log file to see if there are any SQL errors and abend the job
(autosys job which calls my shell script) if there are any.

Question:
I want to know what are the other possible ways to handle the SQL errors within shell script without reading log files ?

1) After calling the SQL , in the next line i tried putting " if [ $? != 0 ] " it always returning 0 irrespective of success/failure of above SQL.

Any HELP!!!!!

[Jonathan Fife]

If you add the following to the beginning of your SQL script it should give you a non-zero return code on errors:

whenever sqlerror exit sql.sqlcode

Just note that it will exit sqlplus at the point of the error and not continue on.

[spex]

Hi,

Here's the syntax for the "WHENEVER SQLERROR" command:

http://lbd.epfl.ch/f/teaching/courses/oracle8i/server.815/a66736/ch8b26.htm

Note that "WHENEVER SQLERROR" works for most, but not all Oracle errors.

PCS

[Sandman!]

Add the following two lines to the very top of your $SQL/inventory.sql file:

whenever sqlerror exit sql.sqlcode rollback
whenever oserror exit oscode rollback

this prevents your session from making half-baked changes to the database since all the insert/update/delete statements will be rolled back in case of SQL and/or OS errors. No need to rollback selects as they are read only.

hope it helps!