Efficient Tracking of Exceptions in Oracle!

 Author: Adrian Billington

Page: 1 2

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. Continued...

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   The power of Oracle table index rebuilding!!
   Beware of Failed Refreshes, Troubleshoot Broken Links!!
   DBA Tips for Verifying Oracle Data Replication!!
   Effective Tracing of Oracle Sessions & Problematic SQL!!