User Exceptions Gotcha, When an Error Appears as a Success

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.

Share this:

• Share

• Twitter
• Facebook
• LinkedIn
• Email
• StumbleUpon
• Google +1

Related articles:

• Little known way to get the error message in PL/SQL
• How to find where an error was raised in PL/SQL
• What Error

Filed in Oracle, Tips on 28 Apr 11 | Tags: gotcha, linux, pl/sql, shell, sql, unix

---

Reader's Comments

1. Eric | 28 Apr 2011 8:09 am

   Yes, I found this a bit frustrating. I ended up doing :

   WHENEVER SQLERROR EXIT 1

   WHENEVER OSERROR EXIT 2

   then capturing all the output from the query run to a log file. Check the $? to see if greater then zero and grep to see the specific errors that did occur:

   rc=$( egrep -c “^(ORA-|SQL\*Loader-|SQL-|PLS-|EXP-|IMP-|INS-|TNS-|SP2-)” ${_log_file} )

   So far, this has been the best compromise I’ve come up with. Granted there can be a lot more Oracle error prefixes, but these seem to be the most common I expect to find. (I think)

   Cheers

   Eric

2. William Robertson | 29 Apr 2011 9:51 am

   I always thought “whenever sqlerror exit sql.sqlcode” was a bit pointless, even without the overflow. Does the calling script really need to analyse the exact Oracle error code? I’ve never seen a script that did – they only ever seem to check whether it’s non-zero. In fact I don’t even think it’s good script design to have 256 possible return codes. grep has three.

   If there really is some specific exit condition the caller needs to be aware of, you could always define an error_code bind variable, set it explicitly in your exception handler and have SQL*Plus use

   exit :error_code

   However I’ve never needed that, and I’ve always used Eric’s approach.

3. David Clement | 30 Apr 2011 10:21 am

   I agree. I can’t remember ever having a need for ‘whenever sqlerror exit sql.sqlcode’ in code that was going into production.

   Indeed, the motivation for the original post was a defect in legacy code, and when fixing that code, I made it return simply success or failure, with output captured in a log.

   Regards, David