This is a guest post by David Clement, a veteran database engineer, a coworker and a friend of mine. You can find him online at davidclement.org.
Here is an interesting feature that’s worth knowing about because of the potential for strange bugs. When you run a SQL script in the Unix shell, if you use a user-defined exception, you have to take care to prevent failure and success from returning the same result.
The following code snippet shows a normal way to return an error code to the Unix shell from a SQL script. I saved this as return_error_code.sql:
variable v number; execute :v := &1 whenever sqlerror exit sql.sqlcode declare l_no_success_exp exception; pragma exception_init (l_no_success_exp, -20002); l_false_success_exp exception; pragma exception_init (l_false_success_exp, -20224); begin raise_application_error (-:v, 'Doh!'); end; -- anonymous block / exit
In either the Korn and bash shells, execute the script and check the return code, as follows:
sqlplus -s username/password @return_error_code 20002 echo $? sqlplus -s username/password @return_error_code 20224 echo $?
The shell variable $? is an 8-bit field, so it does not show the original Oracle error number or user-defined number. What you see in the first case is 20002 mod 256 = 34.
In the second case, however, 20224 mod 256 = 0.
So there’s the risk: if you define a PL/SQL exception that is evenly divisible by 256, in a Unix shell environment that error will appear as a success.