Transactions - Oracle Database Transactions and Locking Revealed - page 82

Here, we 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 the CNT column in T2 is incremented
accordingly.
Oracle considered the “statement” to be the block that the client submitted. This statement succeeded by
catching and ignoring the error itself, so the If error then rollback... didn't come into effect and Oracle didn't
roll back to the SAVEPOINT after execution. Hence, the partial work performed by P was preserved. The reason this
partial work is preserved in the first place is that we have statement-level atomicity within P : each statement in P is
atomic. P becomes the client of Oracle when it submits its two INSERT statements. Each INSERT either succeeds or fails
entirely. This is evidenced by the fact that we can see that the trigger on T fired twice and updated T2 twice, yet the
count in T2 reflects only one UPDATE . The second INSERT executed in P had an implicit SAVEPOINT wrapped around it.
the “WheN OtherS” CLaUSe
i consider virtually all code that contains a WHEN OTHERS exception handler that does not also include a RAISE or
RAISE_APPLICATION_ERROR to re-raise 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, when oracle 11 g release 1 was still on the drawing board, i was permitted to submit three requests for
new features in pL/sQL. i jumped at the chance, and 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— the most common cause —is a WHEN OTHERS not followed by a RAISE or RAISE_APPLICATION_ERROR . i felt
the world would be a safer place without this language feature. the pL/sQL implementation team could not do
this, of course, but they did the next best thing. they 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. For example:
EODA@ORA12CR1> alter session set
2 PLSQL_Warnings = 'enable:all'
3 /
Session altered.
EODA@ORA12CR1> create or replace procedure some_proc( p_str in varchar2 )
2 as
3 begin
4 dbms_output.put_line( p_str );
5 exception
6 when others
7 then
8 -- call some log_error() routine
9 null;
10 end;
11 /
SP2-0804: Procedure created with compilation warnings
EODA@ORA12CR1> show errors procedure some_proc
Errors for PROCEDURE P: