Error Handling

There are three built in routines employed for handling errors efficienlty.
1. DBMS_UTILITY.FORMAT_ERROR_STACK
2. DBMS_UTILITY.FORMAT_CALL_STACK
3. DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

DBMS_UTILITY.FORMAT_ERROR_STACK: This routine is similar to SQLERRM routine, but there are no length constraints as in SQLERRM. We will see an example below in a while.

DBMS_UTILITY.FORMAT_CALL_STACK: Returns the execution call stack down to the point at which you will call this function. It is used mostly while debugging.

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE: Displays the call stack at the point where an exception was raised. It gives the error line number and the complete call stack throgh the procedure names. This routine is particularly useful to dispaly the error message and line number when exception is handled otherwise there is no other way to get the error line number if exception is handled.

Following example shows how to make use of all the three routines.
We will create five procedures p1, p2, p3, p4 and sp_error to illustrate this.
Procedure p1 raises an exception, sp_error calls p4, p4 calls p3, p3 calls p2, p2 calls p1.
So, when we call sp_error, it raises the exception which is propagated from p1.

srikar@ORCL> set echo on
srikar@ORCL> set feedback on
srikar@ORCL> show termout
termout ON
srikar@ORCL> @format_error_stackbacktrace.sql;
srikar@ORCL> create or replace procedure p1
  2  as
  3   l_error exception;
  4   pragma exception_init(l_error, -01408);
  5  begin
  6   raise l_error;
  7  end;
  8  /

Procedure created.

srikar@ORCL> create or replace procedure p2
  2  as
  3  begin
  4   p1;
  5  end;
  6  /

Procedure created.

srikar@ORCL> create or replace procedure p3
  2  as
  3  begin
  4   p2;
  5  end;
  6  /

Procedure created.

srikar@ORCL> create or replace procedure p4
  2  as
  3  begin
  4   p3;
  5  end;
  6  /

Procedure created.

srikar@ORCL> create or replace procedure sp_error
  2  as
  3  begin
  4   p4;
  5  
  6  exception
  7   when others then
  8   dbms_output.put_line ('The format error stack: '||dbms_utility.format_error_stack);
  9  
 10  end;
 11  /

Procedure created.

Now, lets execute the procedure sp_error which calls the procedue format_error_stack to display the error message, it dispalys the error message which is similar to SQLERRM.

srikar@ORCL> execute sp_error;
The format error stack: ORA-01408: such column list already indexed
PL/SQL procedure successfully completed.

Now lets modify the sp_error procedure to include all the three error routines

srikar@ORCL> create or replace procedure sp_error
  2  as
  3  begin
  4     p4;
  5  
  6  exception
  7     when others then
  8     dbms_output.put_line ('The format error stack: '||dbms_utility.format_error_stack);
  9     dbms_output.put_line ('The format error backtrace: '||dbms_utility.format_error_backtrace);
 10     dbms_output.put_line ('The format error callstack: '||dbms_utility.format_call_stack);
 11  
 12  end;
 13  /

Procedure created.

Lets execute the procedure sp_error

srikar@ORCL> execute sp_error;
The format error stack: ORA-01408: such column list already indexed

The format error backtrace: ORA-06512: at "SRIKAR.P1", line 6
ORA-06512: at "SRIKAR.P2", line 4
ORA-06512: at "SRIKAR.P3", line 4
ORA-06512: at "SRIKAR.P4", line 4
ORA-06512: at "SRIKAR.SP_ERROR", line 4

The format error callstack: ----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
000007FF56711398
   10  procedure SRIKAR.SP_ERROR
000007FF566FA308         1  anonymous block
PL/SQL procedure successfully completed.

From the above output, it is clear that format_error_stack shows the error message which is similar to SQLERRM, format_error_backtrace displays the error propagation and line number, format_call_stack dispalys the call stack where it is called.

Lets execute the procedure p4 which inturns calls p1 via p3, p2

srikar@ORCL> execute p4;
BEGIN p4; END;

*
ERROR at line 1:
ORA-01408: such column list already indexed
ORA-06512: at "SRIKAR.P1", line 6
ORA-06512: at "SRIKAR.P2", line 4
ORA-06512: at "SRIKAR.P3", line 4
ORA-06512: at "SRIKAR.P4", line 4
ORA-06512: at line 1

as the exception is not handled in any of these procedures (p1, p2, p3, p4), the error message and line number is propaged to the calling environment.

Lets change the procedure p4 to handle the exception

srikar@ORCL> create or replace procedure p4
  2  as
  3  begin
  4     p3;
  5  exception
  6     when others then
  7     raise;
  8  end;
  9  /
Procedure created.

srikar@ORCL> execute p4;
BEGIN p4; END;
*
ERROR at line 1:
ORA-01408: such column list already indexed
ORA-06512: at "SRIKAR.P4", line 7
ORA-06512: at line 1

as seen it only displays the error line number from the procedure p4 but the not actual propagated error line number which is line 6 from procedure p1.
So, if want to dispaly the exact line number of the error from where it is originated we use dbms_utility.format_error_backtrace.