Database Reference
In-Depth Information
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:
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
