Logging Errors using Error back trace and Error Stack functions

   From 10g version, Oracle database provides a mechanism to log exceptions in the form of two functions in DBMS_UTILITY  package viz., FORMAT_ERROR_STACK and FORMAT_ERROR_BACKTRACE.

FORMAT_ERROR_BACKTRACE function return the error stack along the place where  exception occurred where as  FORMAT_ERROR_STACK just returns the error stack.

Syntax of these two functions is :

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
RETURN VARCHAR2;
DBMS_UTILITY.FORMAT_ERROR_STACK
RETURN VARCHAR2;

Example:

Create or replace procedure proc0 is
Begin
RAISE NO_DATA_FOUND;
END;

CREATE OR REPLACE PROCEDURE PROC1 IS
BEGIN
PROC0();
END;
CREATE OR REPLACE PROCEDURE PROC2 IS
BEGIN
PROC1();
END;
CREATE OR REPLACE PROCEDURE PROC3 IS
BEGIN
PROC2();
END;

CREATE OR REPLACE PROCEDURE PROC4 IS
BEGIN
PROC3();
END;
/*CALL WITHOUT ERROR BACK TRACE*/
BEGIN
PROC4();
END;

Output of this when I ran in Oracle 11gR2 HR schema:

Error report:
ORA-01403: no data found
ORA-06512: at "HR.PROC0", line 3
ORA-06512: at "HR.PROC1", line 3
ORA-06512: at "HR.PROC2", line 3
ORA-06512: at "HR.PROC3", line 3
ORA-06512: at "HR.PROC4", line 3
ORA-06512: at line 2
01403. 00000 -  "no data found"
*Cause:   
*Action:

Using Error back trace function, one can redirect these same error message to the logging mechanism they use for their application. In the below example I redirect the same into dbms_output.

/*CALL WITH ERROR BACK TRACE*/
SET SERVEROUTPUT ON;
BEGIN
PROC4();
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘-------------------------------------------’);
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
DBMS_OUTPUT.PUT_LINE(‘-------------------------------------------’);
END;

anonymous block completed
-------------------------------------------
ORA-01403: no data found

ORA-06512: at "HR.PROC0", line 3
ORA-06512: at "HR.PROC1", line 3
ORA-06512: at "HR.PROC2", line 3
ORA-06512: at "HR.PROC3", line 3
ORA-06512: at "HR.PROC4", line 3
ORA-06512: at line 2
-------------------------------------------

These functions especially FORMAT_ERROR_BACKTRACE comes handy when your program unit calls a lot of functions and procedures and you capture any unhandled exception in when other section – to ensure where this unhandled exception occurred.


Cheers,
Vijay