Returning Error Codes from SQLPLUS
There may come a time when you need to return an error code from SQLPlus, either to a calling batch file, shell script, or Perl script.
SQLPlus has a WHENEVER directive available for handling errors it encounters. This command controls the behavior of SQLPlus when an OS or SQL error occurs. There are many options for this behavior.
More after the jump…
SQL errors include errors thrown by a single command entered into SQLPlus or an error raised by a PL/SQL block:
OS Errors include errors because of disk space and other OS type errors. This is not a common problem but can still cause a script run to fail:
10g Documentation for WHENEVER SQLERROR:
But alas all is not well. There are a certain class of errors (SP2 errors) that are thrown by SQLPLUS that are thrown by SQLPLUS but not caught nicely. These errors can only be caught by examining the output of SQLPLUS. More on that in future posts.