Author: Adrian Billington
Oracle 10g enables PL/SQL developers to trap AND log exceptions accurately. The DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function provides the flexibility and information that DBMS_UTILITY.FORMAT_ERROR_STACK function simply didn’t provide.
Low Level of Information:
Many PL/SQL developers become satisfied with the level of information described below. They take screen scrapes of their scheduling systems’ output as application logs or perhaps their front-end applications display the error stack. Below PL/SQL block demonstrates where a procedure, function or anonymous block hit an exception.
SQL> BEGIN
EXECUTE IMMEDIATE ‘garbage’;
END;
/
BEGIN
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 2
Logging:
Many systems have requirement to write application logs to files or tables. Many developers follow the pseudo-approach as below in order to ensure that the exception is logged. In the below example DBMS_OUTPUT.PUT_LINE refers to an application logging package.
SQL> BEGIN
EXECUTE IMMEDIATE ‘garbage’;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( SQLERRM );
RAISE;
END;
/
ORA-00900: invalid SQL statement
BEGIN
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 6
The point in the code where the exception is raised moves to the explicit RAISE call. The application logs would now record the fact that an ORA-00900 was raised but in a scaled-up application it wouldn’t know which statement hit the exception. This can be a major problem. Consider a scenario where out of 98 separate UPDATE statements one in the middle somewhere fail with an invalid number exception. The only way to identify the actual statement will be by removing the others so Oracle could tell the correct line number.
The DBMS_UTILITY.FORMAT_ERROR_STACK function provides no information over and above the SQLERRM function used in the previous example. It only appends a line feed.
SQL> BEGIN
EXECUTE IMMEDIATE ‘garbage’;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_STACK );
RAISE;
END;
/
ORA-00900: invalid SQL statement
BEGIN
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 6
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE:
Oracle 10g DBMS_UTILITY.FORMAT_ERROR_BACKTRACE provides the error stack all the way back to source. Below example output is very simple and provides the accurate information.
SQL> BEGIN
EXECUTE IMMEDIATE ‘garbage’;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
RAISE;
END;
/
ORA-06512: at line 2
BEGIN
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 6
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function supplies the error propagation path not the error message. Therefore we need to include a call to SQLERRM
SQL> BEGIN
EXECUTE IMMEDIATE ‘garbage’;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( SQLERRM );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
RAISE;
END;
/
ORA-00900: invalid SQL statement
ORA-06512: at line 2
BEGIN
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 7
We now have sufficient information for our application logs, while the error stack generated from the RAISE call can be discarded as it is included to send the necessary failure signal to the calling program / scheduler / shell.
Nesting of Application Blocks:
As the nesting of exception blocks increases, so does the amount of information the new function provides.
SQL> CREATE PROCEDURE will_error AS
BEGIN
RAISE PROGRAM_ERROR;
END;
/
Procedure created.
SQL> BEGIN
will_error();
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( SQLERRM );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
END;
/
ORA-06501: PL/SQL: program error
ORA-06512: at “SCOTT.WILL_ERROR”, line 3
ORA-06512: at line 2
Now we have a full propagation record of our exception from its origin through to the outermost caller. Reading the stack from top to bottom, the exact points at which the exceptions were encountered are preserved. Be cautious if we go back to procedure WILL_ERROR and re-raise the exception in a WHEN OTHERS or such-like then we will once again lose the correct line.
To conclude I would say that an important distinction needs to be made between application code that needs to be logged and that which doesn’t. Various utility packages in an overall application will not handle unexpected exceptions in any way. These will be captured and logged by the business-rule packages that process data and need to write to application log files. These processing packages will each contain a call to the new DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function to enable them to log the precise origins and propagation path of an exception.
