PL/SQL FORMAT_ERROR_BACKTRACE Tips

Hello All,

To find the exact position where the exception has occurred is always a tough job for the programmers to identify. To rectify this issue, a procedure FORMAT_ERROR_BACKTRACE in the DBMS_UTILITY package has been introduced in the Oracle version 10g. This procedure back traces the exception by propagating through the nested programs to bring in the exact route of the exception propagation.

In the below code snippet, the predefined exception NO_DATA_FOUND has been explicitly raised to capture the error code using both the SQLCODE and the back trace procedures.

BEGIN

  raise no_data_found;

EXCEPTION

WHEN OTHERS THEN

  dbms_output.put_line('Backtrace => '||dbms_utility.format_error_backtrace);

  dbms_output.put_line('SQLCODE => '||SQLCODE);

END;

/

Result:

Backtrace => ORA-06512: at line 2

SQLCODE => 100

The above result shows that the back trace procedure has produced the line number along with the ORA error code, whereas the SQLCODE procedure provided us with much less information.

The failure of the SQLERRM procedure to truncate the error message more than 512 characters lead us to the creation of FORMAT_ERROR_STACK procedure of the utility package which allows us to print up to 2000 characters without any truncation. This utility procedure is very much advantageous during the RAISE_APPLICATION_ERROR call with the third optional parameter is set to True which stacks up the error messages.

Thank you,

Boobal Ganesan