DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

In Oracle 10g a new function was introduced to handle exceptions and it is FORMAT_ERROR_BACKTRACE.

One important thing in PL/SQL is when there is an error, the developer needs to know where the exception was raised, and this will become complex when there are chain of calls to procedures/ functions eg; proc1 calling proc2 calling proc3 and so on.

Prior to 10g one could get this error information only by allowing the exception to go unhandled.

I will explain the same using the sample procedures BACKTRACE calls BACKTRACE1 calls BACKTRACE2 and Procedure BACKTRACE2 raises an exception

Prior to 10g - without handling exceptions or Unhandled exceptions

create or replace
PROCEDURE BACKTRACE AS

BEGIN
  DBMS_OUTPUT.PUT_LINE('calling backtrace1');
  BACKTRACE1;
END backtrace;

CREATE OR REPLACE
PROCEDURE BACKTRACE1 AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('calling backtrace2');
  backtrace2;
END BACKTRACE1;

CREATE OR REPLACE PROCEDURE BACKTRACE2 AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('In backtrace2');
  raise no_data_found;
END BACKTRACE2;

When we execute Backtrace - the output will be

ORA-01403: no data found
ORA-06512: at "RTMASTER.BACKTRACE2", line 4
ORA-06512: at "RTMASTER.BACKTRACE1", line 4
ORA-06512: at "RTMASTER.BACKTRACE", line 5
ORA-06512: at line 2
calling backtrace1
calling backtrace2
In backtrace2
 

In this case the developer is happy since he gets the details of where the error is raised. ORA- 1403 No Data found is raised in "RTMASTER.BACKTRACE2", line 4

But remember this is happening since the error is not handled or it's an unhandled exception, but as developers we work to avoid unhandled exceptions. 

Now we will see what happens if we modify the Backtrace procedure to handle the exception

create or replace
PROCEDURE BACKTRACE AS

BEGIN
  DBMS_OUTPUT.PUT_LINE('calling backtrace1');
  BACKTRACE1;
EXCEPTION WHEN OTHERS THEN
 dbms_output.put_line('exception raised');
END backtrace;

When we execute Backtrace - the output now will be

calling backtrace1
calling backtrace2
In backtrace2
exception raised
 

In this case the developer does not have the information on where error was raised and will have difficulty in tracing the error. Now from Oracle 10g onwards DBMS_UTILITY.FORMAT_ERROR_BACKTRACE comes to the rescue the developer.

Now we modify the error handling section and include the function format_error_backtrace

create or replace
PROCEDURE BACKTRACE AS

BEGIN
  DBMS_OUTPUT.PUT_LINE('calling backtrace1');
  BACKTRACE1;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END backtrace;

Below is the output on executing this procedure:

calling backtrace1
calling backtrace2
In backtrace2
ORA-06512: at "RTMASTER.BACKTRACE2", line 4
ORA-06512: at "RTMASTER.BACKTRACE1", line 4
ORA-06512: at "RTMASTER.BACKTRACE", line 5
 

The life of the developer is made simpler now as the developer can get the error information even while handling exceptions.