DBMS_UTILITY_FORMAT_ERROR_BACKTRACE

Oracle introduced this utility a while ago with 10g, but even now it's a hot topic in the interviews. I have witnessed this question quiet often, when they ask. Lets suppose you have three procedure proc1, proc2, proc3. Proc1 is called with proc2 and proc2 is called with proc3. How can you back trace your error in proc1 from proc3?

To find the exact position where the exception has occurred is always a tough job for the programmers to identify. This procedure back traces the exception by propagating through the nested programs to bring in the exact route of the exception propagation.

Lets look into this with a use case.

create or replace procedure p1 as
begin
  dbms_output.put_line('raising error at p1');
  raise no_data_found;
end;
----------------------------------------------------------------------
create or replace procedure p2 as
begin
p1;
end;
-----------------------------------------------------------------------
create or replace procedure p3 as
begin
p2;
exception
when others then
 dbms_output.put_line(' calling format error stack from P3 '); dbms_output.put_line(dbms_utility.FORMAT_ERROR_BACKTRACE);
end;

BEGIN
p3;
END;
raising error at p1
 calling format error stack from P3 
ORA-06512: at "HR.P1", line 4
ORA-06512: at "HR.P2", line 3
ORA-06512: at "HR.P3", line 3

Had we not handled this, we would get unhandled error. But please notice our original message of NO_DATA_FOUND is lost.

You have to use a combination of the 2 calls for the full end-to-end error stack otherwise all you get is "ORA-06512", which is not very useful by itself.

My point of showing this was to correct you from a wrong way of approaching on to backtrace utility.

 create or replace procedure p1 as
begin
 dbms_output.put_line ('raising error at p1');
 raise no_data_found;
 end;
-----------------------------------------------------------------------
 create or replace procedure p2 as
 begin
 p1;
 exception when others then
 dbms_output.put_line('calling format error stack from P2');
 dbms_output.put_line (dbms_utility.format_error_stack);

 dbms_output.put_line('calling format error backtrace stack from P2');
 dbms_output.put_line (dbms_utility.format_error_backtrace);
raise;
 end;

-----------------------------------------------------------------

 create or replace procedure p3 as
 begin
 p2;
 exception when others then
dbms_output.put_line('calling format error stack from P3');
 dbms_output.put_line (dbms_utility.format_error_stack);
 dbms_output.put_line('calling format error backtrace stack from P3');
 dbms_output.put_line (dbms_utility.format_error_backtrace);
end;

BEGIN
p3;
end;
 raising error at p1
calling format error stack from P2
ORA-01403: no data found
ORA-06512: at "HR.P1", line 4

calling format error backtrace stack from P2
ORA-06512: at "HR.P1", line 4
ORA-06512: at "HR.P2", line 3

calling format error stack from P3
ORA-01403: no data found
ORA-06512: at "HR.P2", line 10
ORA-06512: at "HR.P1", line 4
ORA-06512: at "HR.P2", line 3

calling format error backtrace stack from P3
ORA-06512: at "HR.P2", line 10
ORA-06512: at "HR.P1", line 4
ORA-06512: at "HR.P2", line 3
ORA-06512: at "HR.P3", line 3

Oracle database 12c introduced the UTL_CALL_STACK package to allow programmatic access to the call stack and error stack, giving much greater flexibility for debugging and error handling of PL/SQL code. This is only a replacement for the existing functionality if you need the extra level of control. The existing functionality in the DBMS_UTILITY package is still available and has not been deprecated. We can talk about that in any upcoming post.