Oracle Magazine - November/December 2011

of the PL/SQL stored procedure is the only one who knows when a transaction is complete. It is a bad programming practice to issue a COMMI T or a ROLLBACK in PL/SQL routines you develop.

Now, if I submit a slightly different block, I will get entirely different results:

SQL> begin 2 p;

3 exception
4 when others then
5 dbms_output.put_line(

'Error!!!! ' || sqlerrm );

6 end;

7 / Error!!!! ORA-02290: check constraint ( OPS$TKYTE.SYS_C0018095) violated PL/SQL procedure successfully completed.

SQL> select from t;

X —————————————

1

Here, I ran a block of code that ignored any and all errors, and the difference in outcome is huge. Whereas the first call to P effected no changes, this time the first INSERT succeeds and remains in the database. Oracle Database considered the statement to be the block the client submitted, but this statement succeeded by catching and ignoring the error! Hence, the partial work performed by P was preserved. The reason this partial work was preserved in the first place is that there is statement-level atomicity within P—each statement in P is atomic. P becomes the client of Oracle Database when it submits its two INSERT statements. Each INSERT either succeeds or fails entirely.

I consider virtually all code that contains a WHEN OTHERS exception handler that does not also include a RAISE or RAISE_ APPLICATION_ERROR to reraise the exception to be a bug. It silently ignores the error, and it changes the transaction semantics. Catching WHEN OTHERS and translating the exception into an old-fashioned return code changes the way the database is supposed to behave.

In fact, I believe this so strongly that when
Oracle Database 11 g Release 1 was still on
the drawing board and I was permitted to
submit three requests for new features in
PL/SQL, I jumped at the chance. My first
suggestion was simply, “Remove the WHEN
OTHERS clause from the language.” My
reasoning was simple: the most common
cause of developer-introduced bugs I see
is a WHEN OTHERS that is not followed by
a RAISE or a RAISE_APPLICATION_ERROR.
I felt that the world would be a safer place
without this language feature. The PL/SQL
implementation team could not honor my
request, of course, but it did the next-best
thing. It made it so that PL/SQL will generate
a compiler warning if you have a WHEN
OTHERS that is not followed by a RAISE or
RAISE_APPLICATION_ERROR call. Listing 4
demonstrates the compiler warning.

SQL> begin 2 savepoint sp;

3 p; 4 exception 5 when others then 6 rollback to sp;

7 dbms_output.put_line( 'Error!!!! ' || sqlerrm ); 8 end; 9 / Error!!!! ORA-02290: check constraint ( OPS$TKYTE.SYS_C0018095) violated PL/SQL procedure successfully completed.

SQL> select from t; no rows selected

Caution: The preceding code represents an
exceedingly bad practice! In general, you
should neither catch a WHEN OTHERS nor
explicitly code what Oracle Database already
provides in terms of transaction semantics.

SQL> begin 2 p;

3 end;

4 /

During your code reviews, you should regard all WHEN OTHERS exception blocks with suspicion and really investigate the code if the WHEN OTHERS is not followed by a RAISE or a RAISE_APPLICATION_ERROR! 

Tom Kyte is a database
evangelist in Oracle’s
Server Technologies
division and has worked
for Oracle since 1993. He
is the author of Expert
Oracle Database Architecture (Apress, 2005,
2010) and Effective Oracle by Design (Oracle
Press, 2003), among other books.