Handling errors in PL/SQL

Handling errors in Oracle is very easy when you know the right path to (not so known) ready made procedures for that.

Small example

Suppose we have three simple PL/SQL procedures declared in next way:

create or replace procedure first_level_call as
  l_error varchar2(4000);
begin
  dbms_output.put_line('first_level call!');
  second_level_call;  
end;
/ 

"first_level_call" procedure calls "second_level_call" procedure:

create or replace procedure second_level_call as
begin
  dbms_output.put_line(chr(9)||'second_level call!');
  force_ora_error;
end;
/

"second_level_call" procedure calls "force_ora_error" procedure:

create or replace procedure force_ora_error as
  i pls_integer;
  j pls_integer;
begin
  i := 1;
  j := 0;
  dbms_output.put_line(chr(9)||chr(9)||' force_ora_error call!');
  i := 1/j;
  dbms_output.put_line('This line you will never see!');
end;
/

When you run "first_level_call" procedure, result looks like:

SQL> exec first_level_call;
first_level call!
        second_level call!
                 force_ora_error call!
BEGIN first_level_call; END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "DAMIRV.FORCE_ORA_ERROR", line 8
ORA-06512: at "DAMIRV.SECOND_LEVEL_CALL", line 4
ORA-06512: at "DAMIRV.FIRST_LEVEL_CALL", line 5
ORA-06512: at line 1


SQL> 

All looks clear and easy to understand:

1. ORA-01476: divisor is equal to zero (ORA error with description)
2. procedure "DAMIRV.FORCE_ORA_ERROR" line 8 (this is the line where error happened)
3. procedure "DAMIRV.SECOND_LEVEL_CALL" line 4 (this is the line where "force_orra_error" has been called)
4. procedure "DAMIRV.FIRST_LEVEL_CALL" line 5 (this is the line where "first_level_call" has been called)
5. at line 1 this is anonymous call from SQL*Plus ("exec first_level_call"-what started all other calls)

Remember that dbms_output.put_line commands gives output ONLY from of sqlplus like tools. When you run the same code from other Oracle tools (i.e. in forms or reports etc.) dbms_output.put_line makes no output-like it doesn't exist at all!

So if we want to make persistent error logging-we have to save error to some variable.

create or replace procedure first_level_call as
  l_error varchar2(4000);
begin
  dbms_output.put_line('first_level call!');
  second_level_call;  
exception
  when others then
    l_error:=SQLERRM;
    dbms_output.put_line(l_error);
end;
/ 

When you show the result of saved variable (l_error), result looks like:

SQL> exec first_level_call;
first_level call!
        second_level call!
                 force_ora_error call!
ORA-01476: divisor is equal to zero

PL/SQL procedure successfully completed.

SQL> 

Hmmmmmmm! ORA-01476: divisor is equal to zero is what we have saved! All important data like line numbers where error happened or even in what procedure error happened are lost for good!!

Solution

Solution is to use Oracle several predefined DBMS_UTILITY calls.

Place additional code in exception part of "first_level_call" procedure. "first_level_call" procedure now looks like:

create or replace procedure first_level_call as
  l_error varchar2(4000);
begin
  dbms_output.put_line('first_level call!');
  second_level_call;  
exception
  when others then
    l_error:=DBMS_UTILITY.FORMAT_ERROR_BACKTRACE||
             chr(10)||
             DBMS_UTILITY.format_call_stack;
    dbms_output.put_line(l_error);
end;
/ 

When you run "first_level_call" procedure again, all data are saved much more better then before:

SQL> exec first_level_call;
first_level call!
        second_level call!
                 force_ora_error call!
ORA-06512: at "DAMIRV.FORCE_ORA_ERROR", line 8
ORA-06512: at "DAMIRV.SECOND_LEVEL_CALL", line 4
ORA-06512: at "DAMIRV.FIRST_LEVEL_CALL", line 5

----- PL/SQL
Call Stack -----
  object      line  object
  handle    number  name
000007FF0DFD45A8         8  procedure DAMIRV.FIRST_LEVEL_CALL
000007FF0DF4FB58         1  anonymous block

PL/SQL procedure successfully completed.

SQL> 

Cheers!