Transactions - Oracle Database Transactions and Locking Revealed - page 83

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1 PLW-05018: unit SOME_PROC omitted optional AUTHID clause; default
value DEFINER used

6/10 PLW-06009: procedure "SOME_PROC" OTHERS handler does not end in
RAISE or RAISE_APPLICATION_ERROR

so, if you include WHEN OTHERS in your code and it is not followed by a RAISE or RAISE_APPLICATION_ERROR ,
be aware that you are almost certainly looking at a bug in your developed code, a bug placed there by you.

The difference between the two blocks of code, one with a WHEN OTHERS exception block and one without, is
subtle, and something you must consider in your applications. Adding an exception handler to a block of PL/SQL
code can radically change its behavior. A different way to code this—one that restores the statement-level atomicity to
the entire PL/SQL block—is as follows:

EODA@ORA12CR1> 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 /
I fired and updated 1 rows
I fired and updated 1 rows
Error!!!! ORA-02290: check constraint (EODA.SYS_C0061484) violated
PL/SQL procedure successfully completed.

EODA@ORA12CR1> select * from t;
no rows selected

EODA@ORA12CR1> select * from t2;

CNT
----------
0

■ the preceding code represents an exceedingly bad practice. in general, you should neither catch a WHEN
OTHERS nor explicitly code what oracle already provides as far as transaction semantics is concerned.

Caution

Here, by mimicking the work Oracle normally does for us with the SAVEPOINT , we are able to restore the original
behavior while still catching and “ignoring” the error. I provide this example for illustration only; this is an exceedingly
bad coding practice.