Dec 4

Written by: StevenFeuersteinTW
Tuesday, December 04, 2007 8:41 PM 

Winter is descending on Chicago; we had our first snow (flurries, really) on Thanksgiving Day, and the sun is weak. For many, this is a depressing time – and I mean that the lack of that and cold actually does make people feel depressed. Perhaps that is why I found myself thinking negatively – that is, about exceptions in PL/SQL.

So I thought I would share with you some of the features and functions available in PL/SQL, especially those introduced in recent versions of Oracle, which will help you trap and log error information.

For each of the topics below, I describe them briefly and then point to files in my demo.zip archive that illustrate the technique, and also the hyperlink to Oracle documentation on the topic.

You can download my demo.zip archive by clicking here.

Enjoy! And remember: never be sad about Oracle exceptions. Just make sure you have a powerful, general utility for raising, handling and logging those errors. If you don't already have such a thing, then please do download and try the freeware Quest Error Manager. I wrote it myself – just for you!

Save DML errors to a log table instead of raising an exception (Oracle10g)
As soon as the SQL engine raises an exception from processing a DML statement, your executable section terminates. You can, of course, trap the exception, log it, and then keep going, but once an exception is raised, performance degrades terribly.

Use the DBMS_ERRLOG package to define an error log table for your own table. Then when you write your DML statement, include the LOG ERRORS clause. With this approach, Oracle will save DML errors to a log table instead of raising an exception; your program will finish more quickly and then afterwards, you can query the information from the log table and either log the errors in your own system, try to recover or display error information.

My demo.zip files of relevance
dbms_errlog*.*
forall_with_dbms_errlog.sql

Oracle documentation reference http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_errlog.htm#sthref2860

Save exceptions till the end of your FORALL execution
FORALL is an absolutely, deliriously wonderful enhancement to DML execution introduced in Oracle8i. With FORALL, you can execute multiple DML statements in "bulk," which means they run in a fraction of the time it would take if they ran them on a row by row basis.

As with LOG ERRORS above, you can tell FORALL to continue past any exceptions encountered as it runs each DML operation by adding the SAVE EXCEPTIONS clause.  Then if one or more exceptions were encountered, when FORALL is done, Oracle will raise the ORA-24381 error and also populates a "pseudo-collection" called SQL%BULK_EXCEPTIONS with the error code and the index in the collection that raised the error.

My demo.zip files of relevance
cfl_to_bulk*.sql
bulkexc.sql

Oracle documentation reference http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#sthref2201

Find the line number on which the error was raised (Oracle10g)
Call the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE to retrieve a string that shows the stack of calls that trace back to the line number and program name from which the most recent error was raised.

My demo.zip files of relevance
backtrace*.sql

Oracle documentation reference http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_util.htm#sthref9387

Get the error message
What? You thought you should call the SQLERRM function? No way! Oracle recommends that you not use this function, because it might truncate your error message. Instead, call the DBMS_UTILITY.FORMAT_ERROR_STACK function and it will return the full error message (and sometimes even a stack!).

My demo.zip files of relevance
Sorry, none! It's such an easy, little thing to use...

Oracle documentation reference http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_util.htm#sthref9392