Recommendations for Error Handling

Question:
What is the best way to write robust exception and error codes in PL/SQL procedures and functions? I've seen this done in various ways, but there must be a better one!
Answer:
This is, of course, a very big topic, and one I cannot fully address here. I suggest you spend some time with the PL/SQL User's Guide and Reference, Chapter 10, "Handling PL/SQL Errors." You may also want to read the coverage of exception handling in two of my books, Oracle PL/SQL Programming and Oracle PL/SQL Best Practices.
I will, however, offer some high-level guidelines below that you may find helpful. Please keep in mind as you read them that there are very few absolutes when it comes to error handling; the architecture of your application may dictate a different approach. You should make sure that whatever error-handling code you use is easy to maintain and records all the information you need to debug and fix the problem.
Some Guidelines for Error Management in PL/SQL
1. Exceptions can be classified as follows: deliberate, unfortunate, and unexpected. Consider how you read from a text file with UTL_FILE -- you just call UTL_FILE.GET_LINE in an "endless" loop until the NO_DATA_FOUND exception is raised. The package was designed to raise that exception deliberately, and you will include a handler that traps the error and then allows program execution to continue.
A SELECT INTO cursor that raises NO_DATA_FOUND is an example from the "unfortunate" category. Your program should trap the error and then take whatever action is required by the application. That same SELECT INTO could also raise TOO_MANY_ROWS, an unexpected error that indicates a data integrity violation.
Handle deliberate exceptions as an aspect of normal program flow; handle unfortunate exceptions by recovering cleanly and communicating a status (which might be a human-readable message) to the caller; let other exceptions bubble to the top (but never let them go unhandled to the client). Two corollaries: (1) Avoid using the when others clause except in the exception handler of the outermost block of the top-level PL/SQL call, unless you log the error information at that point and then re-raise the exception. (2) Code you create to be reused by other PL/SQL programs should never use when others, unless the caught exception is re-raised.
2. Rely on the default error-handling mechanism of PL/SQL as much as possible. Do not write programs to return status codes rather than raise exceptions. The propagation and handling of exceptions is generally more flexible and easier to maintain than such an approach. If, however, your PL/SQL program is being called from a non-PL/SQL environment that does not handle Oracle exceptions well, you might then consider returning status codes.
3. Trap the exception as close as possible to the block in which it was raised. By doing so, you will be able to extract from the program and record in your error log as much information as possible about the context in which the error occurred. Much of this data can be lost as the exception propagates unhandled through enclosing blocks.
4. Use the Oracle Database 10g new function, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, to determine the precise line number on which the error was raised. This is a wonderful enhancement and will make analysis of errors much easier. Use DBMS_UTILITY.FORMAT_ERROR_STACK rather than SQLERRM to obtain the current error message.
5. Rather than have each developer write their own error handling code in each exception section, you should share the use of a single, reusable error management package. By doing so, the application's error handling and logging will be much more consistent, and developers will spend far less time writing this tedious, low-level code. For examples of such a package, check out PLVexc of the freeware library, PL/Vision, available at www.quest-pipelines.com, and the qd_runtime package of Qnxo (www.qnxo.com).
6. Use RAISE_APPLICATION_ERROR only when you need to communicate an application-specific error to the user. An application-specific error that will be trapped and dealt with entirely within your PL/SQL code can be implemented through a user-defined exception. Avoid hard-coding error messages and codes in the -20999 to -20000 range when you use this built-in. Instead, you might consider defining all your application-specific errors in a table and then generate a package that contains the definitions of all your errors. Reference these errors by named constants and exceptions. For an example of such an approach, check out the msginfo.sql file that is part of the demonstration files download that accompanies my training resources: www.oracleplsqlprogramming.com/resources.