The More You Know

OPP - Session 3 - PL/SQL Error Management

Session three turned out to be great! The subject matter was on error handling in PL/SQL and how while Oracle 10gR2 has fixed a few issues with PL/SQL error handling, there is much more that each developer can do to increase the usefulness of errors to developers and the verbosity for users. As a bonus it was given by Steven Feuerstein who has a great presentation style. I will admit I do not agree with everything he presents, but at least he does it in a way that keeps me listening.

What he did covers was usefulness of the DBMS_UTILITY package and how SQLERRM is a thing of the past and how should remain that way by not using SQLERRM ever again. Instead , DBMS_UTILITY.FORMAT_ERROR_STACK (which is the same as SQLERRM) should be used as well as DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and DBMS_UTILITY.FORMAT_CALL_STACK. The latter provides line numbers (finally!) of where in a file the error actually occurs. It is also worth noting that the output from the last two function calls can be reset by re-raising errors via “raise;”. Also to help he noted the use of DBMS_ERRORLOG() and how it can be used to log error over an entire processes life.

However, the big bang comes from how you can tie all of that available information together to provide incredible error logging and even methodologies for storing and referring to errors by names instead of numbers. Instead of covering them here, I will point you to Steven’s error handling package (which is freeware, however not supported by Oracle) at Steve’s site here. It is defiantly worth a read through and consideration of use or at least emulation.

Steve also has the following site to help push the PL/SQL curators (one of who I had the chance to meet!) at http://www.iloveplsqland.net/. Trivia fact, the site is also run on Oracle’s APEX.