I am running an Oracle SQL script via a Unix shell script using sqlplus. However, even when a SQL statement inside the script fails (like a missing table or a primary key violation), the shell script continues to the next command. How can I capture these Oracle-specific errors so that the entire execution halts immediately upon the first failure? Should I check the exit status $? or is there a specific SQLPlus setting I'm missing?
3 answers
The key to stopping execution is the WHENEVER SQLERROR command, which must be placed inside your SQL script or "Here Document" (EOF block). By default, SQLPlus returns a success code (0) to the shell even if individual SQL statements fail. To change this, add WHENEVER SQLERROR EXIT SQL.SQLCODE at the very beginning of your SQL code. This tells SQLPlus to exit and return the specific Oracle error number to the operating system as soon as a failure is detected. In your Unix script, you can then catch this using $?.
Does this also handle OS-level errors, like if the database connection is lost or if there's a file permission issue while spooling?
Be careful with SQL.SQLCODE on Unix. Unix exit codes are limited to a range of 0–255. If an Oracle error is 1017, the shell will see 1017 % 256, which might result in a confusing number or even a 0 (false success). It's safer to use EXIT FAILURE or a fixed number like EXIT 1.
Great follow-up, Christopher. WHENEVER SQLERROR only catches SQL and PL/SQL block failures. For system-level issues, you should also include WHENEVER OSERROR EXIT FAILURE. Using both ensures that your shell script is protected against both internal data errors and external environment crashes.