How to log line number alongwith SQLCODE and SQLERRM?
How to find where an error was raised in PL/SQL
how to get the stack trace, package name and procedure name?
It has always been possible in PL/SQL to identify the source of an exception in a block of code; i.e. to know the precise point at which a block of code failed. However, until Oracle 10g, it has not been possible for developers to intervene in the exception in any way whilst retaining this information (for example to record the exception and its source in application logs). This has been the cause of many a frustration for developers.
Finally, with the release of 10g, Oracle has added provision for PL/SQL developers to trap AND log exceptions accurately for the first time. The DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function gives us the flexibility we have been demanding for years and the information that the DBMS_UTILITY.FORMAT_ERROR_STACK function simply didn't provide.
The following simple PL/SQL block demonstrates that Oracle will happily tell us where a procedure, function or anonymous block hit an exception.
SQL> BEGIN 2 EXECUTE IMMEDIATE 'garbage'; 3 END; 4 /
BEGIN * ERROR at line 1: ORA-00900: invalid SQL statement ORA-06512: at line 2
There are many PL/SQL developers who consider this to be adequate. They might, for example, take screen scrapes of their scheduling systems' output as application logs and be satisfied with the level of information demonstrated above. Or perhaps their front-end applications display the error stack as seen above.
Many systems, however, have a requirement to write application logs to files or tables. Therefore, to ensure that the exception is logged, the following "pseudo-approach" is taken by many developers (note that in the simple examples that follow I've substituted DBMS_OUTPUT.PUT_LINE for an application logging package).
SQL> BEGIN 2 EXECUTE IMMEDIATE 'garbage'; 3 EXCEPTION 4 WHEN OTHERS THEN 5 DBMS_OUTPUT.PUT_LINE( SQLERRM ); 6 RAISE; 7 END; 8 /
ORA-00900: invalid SQL statement BEGIN * ERROR at line 1: ORA-00900: invalid SQL statement ORA-06512: at line 6
The result is that the point in the code where the exception was raised moves to the explicit RAISE call, as can be seen at the end of the error stack above (highlighted in bold). 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. For example, I recently had to debug another developer's procedure, which contained 98 separate UPDATE statements and one of them "in the middle somewhere" failed with an invalid number exception. The only way to identify the actual statement was by removing the WHEN OTHERS so Oracle could tell me the correct line number.
As stated earlier, Oracle has supplied the DBMS_UTILITY.FORMAT_ERROR_STACK function for years, but this is of no use in solving this problem, as the following example demonstrates.
SQL> BEGIN 2 EXECUTE IMMEDIATE 'garbage'; 3 EXCEPTION 4 WHEN OTHERS THEN 5 DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_STACK ); 6 RAISE; 7 END; 8 /
ORA-00900: invalid SQL statement BEGIN * ERROR at line 1: ORA-00900: invalid SQL statement ORA-06512: at line 6
The DBMS_UTILITY.FORMAT_ERROR_STACK function in this instance provides no information over and above the SQLERRM function used in the previous example. The only difference is that the DBMS_UTILITY.FORMAT_ERROR_STACK function appends a line feed!
The long awaited Oracle enhancement to solve this problem was introduced in the first release of 10g. The DBMS_UTILITY.FORMAT_ERROR_BACKTRACE provides the error stack all the way back to source. In a simple example such as the following, the output is very simple and provides the accurate information we require.
SQL> BEGIN 2 EXECUTE IMMEDIATE 'garbage'; 3 EXCEPTION 4 WHEN OTHERS THEN 5 DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ); 6 RAISE; 7 END; 8 /
ORA-06512: at line 2 BEGIN * ERROR at line 1: ORA-00900: invalid SQL statement ORA-06512: at line 6
Note, however, that the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function does not supply the error message, just the error's propagation path. We therefore need to include a call to SQLERRM.
SQL> BEGIN 2 EXECUTE IMMEDIATE 'garbage'; 3 EXCEPTION 4 WHEN OTHERS THEN 5 DBMS_OUTPUT.PUT_LINE( SQLERRM ); 6 DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ); 7 RAISE; 8 END; 9 /
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
This example demonstrates that we now have sufficient information for our application logs, while the error stack generated from the RAISE call in line 7 can be discarded (it is included to send the necessary failure signal to the calling program / scheduler / shell).
As the nesting of exception blocks increases, so does the amount of information the new function provides, as the following example demonstrates (note that this time I have not re-raised the exception to keep the output clear).
SQL> CREATE PROCEDURE will_error AS 2 BEGIN 3 RAISE PROGRAM_ERROR; 4 END; 5 /
Procedure created.
SQL> BEGIN 2 will_error(); 3 EXCEPTION 4 WHEN OTHERS THEN 5 DBMS_OUTPUT.PUT_LINE( SQLERRM ); 6 DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ); 7 END; 8 /
ORA-06501: PL/SQL: program error ORA-06512: at "SCOTT.WILL_ERROR", line 3 ORA-06512: at line 2
At last, we now have a full propagation record of our exception, from its origin through to the outermost caller. Reading the stack from top to bottom, note that the exact points at which the exceptions were encountered are preserved. A major caveat to this is, of course, that if we go back to procedure WILL_ERROR and re-raise the exception in a WHEN OTHERS or such-like, we will once again lose the correct line.
Therefore, an important distinction needs to be made between application code that needs to be logged and that which doesn't. In my mind it is fairly clear that the various utility packages I include in my 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. At last!
