Back Trace Exception to Line That Raised It

View All Scripts Login to Run Script

Script Name Back Trace Exception to Line That Raised It
Description The DBMS_UTILITY.format_error_backtrace function, added in Oracle Database 10g Release 2, is a critical subprogram to call when logging exceptions. It returns a string that traces the error back to the line on which it was raised! Note: if you re-raise an exception as it propagates up the stack, you will lose the "original" line number. The back trace function always only traces back to the most recently raised exception.
Area PL/SQL General
Contributor Steven Feuerstein
Created Tuesday January 26, 2016

Statement 1

CREATE OR REPLACE PROCEDURE proc1 
IS 
BEGIN 
   DBMS_OUTPUT.put_line ('running proc1'); 
   RAISE NO_DATA_FOUND; 
END;

Procedure created.

Statement 2

CREATE OR REPLACE PROCEDURE proc2 
IS 
   l_str   VARCHAR2 (30) := 'calling proc1'; 
BEGIN 
   DBMS_OUTPUT.put_line (l_str); 
   proc1; 
END;

Procedure created.

Statement 3

CREATE OR REPLACE PROCEDURE proc3 
IS 
BEGIN 
   DBMS_OUTPUT.put_line ('calling proc2'); 
   proc2; 
END;

Procedure created.

Statement 4

The only way to "see" the line number on which the error was raised was to let the exception go unhandled.

Without Back Trace....

BEGIN  
   DBMS_OUTPUT.put_line ('Proc3 -> Proc2 -> Proc1 unhandled');  
   proc3;  
END;

ORA-01403: no data found ORA-06512: at "SQL_DKDUUASCAEZWMZONLLTKLQUSR.PROC1", line 5
ORA-06512: at "SQL_DKDUUASCAEZWMZONLLTKLQUSR.PROC2", line 6
ORA-06512: at "SQL_DKDUUASCAEZWMZONLLTKLQUSR.PROC3", line 5
ORA-06512: at line 3
ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-00100

Statement 5

Sure, that works fine and is very good info to have, but the error stack (error message) will contain the line number on which the error was raised!

Trap and Display Error Stack (Error Message)

BEGIN  
   DBMS_OUTPUT.put_line ('Proc3 -> Proc2 -> Proc1 unhandled');  
   proc3;  
EXCEPTION  
   WHEN OTHERS  
   THEN  
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);  
END;

Proc3 -> Proc2 -> Proc1 unhandled
calling proc2
calling proc1
running proc1
Proc3 -> Proc2 -> Proc1 unhandled
calling proc2
calling proc1
running proc1
ORA-01403: no data found
ORA-06512: at "SQL_DKDUUASCAEZWMZONLLTKLQUSR.PROC1", line 5
ORA-06512: at "SQL_DKDUUASCAEZWMZONLLTKLQUSR.PROC2", line 6
ORA-06512: at "SQL_DKDUUASCAEZWMZONLLTKLQUSR.PROC3", line 5

Statement 6

Now we trap the exception at the top level subprogram and view both the error stack and the back trace.

Add Back Trace to Error Handler

CREATE OR REPLACE PROCEDURE proc3   
IS   
BEGIN   
   DBMS_OUTPUT.put_line ('calling proc2');   
   proc2;   
EXCEPTION   
   WHEN OTHERS   
   THEN   
      DBMS_OUTPUT.put_line ('Error backtrace at top level:');   
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);   
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);   
END;

Procedure created.

Statement 7

BEGIN 
   DBMS_OUTPUT.put_line ('Proc3 -> Proc2 -> Proc1 backtrace'); 
   proc3; 
END;

Proc3 -> Proc2 -> Proc1 backtrace
calling proc2
calling proc1
running proc1
Error backtrace at top level:
ORA-01403: no data found
ORA-06512: at "SQL_DKDUUASCAEZWMZONLLTKLQUSR.PROC1", line 5
ORA-06512: at "SQL_DKDUUASCAEZWMZONLLTKLQUSR.PROC2", line 6

ORA-06512: at "SQL_DKDUUASCAEZWMZONLLTKLQUSR.PROC1", line 5
ORA-06512: at "SQL_DKDUUASCAEZWMZONLLTKLQUSR.PROC2", line 6
ORA-06512: at "SQL_DKDUUASCAEZWMZONLLTKLQUSR.PROC3", line 5

Statement 8

I show the back trace, but then re-raise.

Re-Raise Exception

CREATE OR REPLACE PROCEDURE proc1   
IS   
BEGIN   
   DBMS_OUTPUT.put_line ('running proc1');   
   RAISE NO_DATA_FOUND;   
EXCEPTION   
   WHEN OTHERS   
   THEN   
      DBMS_OUTPUT.put_line ('Error backtrace in block where raised:');   
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);   
      RAISE;   
END;

Procedure created.

Statement 9

The call to back trace in this upper-level subprogram no longer finds it way back to the line number of the original exception. That was wiped out with the call to RAISE;

Can't Trace All the Way Back

CREATE OR REPLACE PROCEDURE proc3   
IS   
BEGIN   
   DBMS_OUTPUT.put_line ('calling proc2');   
   proc2;   
EXCEPTION   
   WHEN OTHERS   
   THEN   
      DBMS_OUTPUT.put_line ('Error backtrace at top level:');   
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);   
END;

Procedure created.

Statement 10

BEGIN 
   DBMS_OUTPUT.put_line ('Proc3 -> Proc2 -> Proc1, re-reraise in Proc1'); 
   proc3; 
END;

Proc3 -> Proc2 -> Proc1, re-reraise in Proc1
calling proc2
calling proc1
running proc1
Error backtrace in block where raised:
ORA-06512: at "SQL_DKDUUASCAEZWMZONLLTKLQUSR.PROC1", line 5

Error backtrace at top level:
ORA-06512: at "SQL_DKDUUASCAEZWMZONLLTKLQUSR.PROC1", line 11
ORA-06512: at "SQL_DKDUUASCAEZWMZONLLTKLQUSR.PROC1", line 5
ORA-06512: at "SQL_DKDUUASCAEZWMZONLLTKLQUSR.PROC2", line 6
ORA-06512: at "SQL_DKDUUASCAEZWMZONLLTKLQUSR.PROC3", line 5

Statement 11

And see how the back trace changes!

Handle and Raise At Every Level

CREATE OR REPLACE PROCEDURE proc1  
IS  
BEGIN  
   DBMS_OUTPUT.put_line ('running proc1');  
   RAISE NO_DATA_FOUND;  
EXCEPTION  
   WHEN OTHERS  
   THEN  
      DBMS_OUTPUT.put_line ('Error stack in block where raised:');  
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);  
      RAISE;  
END;

Procedure created.

Statement 12

CREATE OR REPLACE PROCEDURE proc2 
IS 
BEGIN 
   DBMS_OUTPUT.put_line ('calling proc1'); 
   proc1; 
EXCEPTION 
   WHEN OTHERS 
   THEN 
      RAISE VALUE_ERROR; 
END;

Procedure created.

Statement 13

CREATE OR REPLACE PROCEDURE proc3  
IS  
BEGIN  
   DBMS_OUTPUT.put_line ('calling proc2');  
   proc2;  
EXCEPTION  
   WHEN OTHERS  
   THEN  
      DBMS_OUTPUT.put_line ('Error backtrace at top level:');  
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);  
END;

Procedure created.

Statement 14

BEGIN 
   DBMS_OUTPUT.put_line 
           ('Proc3 -> Proc2 -> Proc1, re-reraise in Proc1, raise VE in Proc2'); 
   proc3; 
END;

Proc3 -> Proc2 -> Proc1, re-reraise in Proc1, raise VE in Proc2
calling proc2
calling proc1
running proc1
Error stack in block where raised:
ORA-06512: at "SQL_DKDUUASCAEZWMZONLLTKLQUSR.PROC1", line 5

Error backtrace at top level:
ORA-06512: at "SQL_DKDUUASCAEZWMZONLLTKLQUSR.PROC2", line 9
ORA-06512: at "SQL_DKDUUASCAEZWMZONLLTKLQUSR.PROC1", line 11
ORA-06512: at "SQL_DKDUUASCAEZWMZONLLTKLQUSR.PROC1", line 5
ORA-06512: at "SQL_DKDUUASCAEZWMZONLLTKLQUSR.PROC2", line 5
ORA-06512: at "SQL_DKDUUASCAEZWMZONLLTKLQUSR.PROC3", line 5

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database