Nine Good-to-Knows about PL/SQL Error Management

1. Exceptions raised in the declaration section are not handled in the exception section.

This sometimes surprises a developer new to PL/SQL. The exception section of a PL/SQL block can only possibly handle an exception raised in the executable section. An exception raised in the declaration section (in an attempt to assign a default value to a variable or constant) always propagates out unhandled to the enclosing block.

Verify on LiveSQL
Exceptions Raised in Declaration Section Not Handled Locally

2. An exception raised does not automatically roll back uncommitted changes to tables.

Any non-query DML statements that complete successfully in your session are not rolled back when an exception occurs - either directly in PL/SQL or propagated out from the SQL engine. You still have the option of either committing or rolling back yourself.

If, however, the exception goes unhandled out to the host environment, a rollback almost always occurs (this is performed by the host environment).

Verify on LiveSQL
Exceptions Do Not Rollback Uncommitted Changes

3. You can (and should!) name those unnamed ORA errors (never hard-code an error number).

Oracle Database pre-defines a number of exceptions for common ORA errors, such as NO_DATA_FOUND and VALUE_ERROR. But there a whole lot more errors for which there is no pre-defined name. And some of these can be encountered quite often in code. The key thing for developers is to avoid hard-coding these error numbers in your code. Instead, use the EXCEPTION_INIT pragma to assign a name for that error code, and then handle it by name.

Verify on LiveSQL
Generate Named Exceptions
Use EXCEPTION_INIT to Give Names to Un-named Oracle Errors

4. If you do not re-raise an exception in your exception handler, the outer block doesn't know an error has occurred.

Just sayin'. You have a subprogram that invokes another subprogram (or nested block). That "inner" subprogram fails with an exception. It contains an exception handler. It logs the error, but then neglects to re-raise that exception (or another). Control passes out to the invoking subprogram, and it continues executing statements, completely unaware that an error occurred in that inner block. Which means, by the way, that a call to SQLCODE will return 0. This may be just what you want, but make sure you do this deliberately.

Verify on LiveSQL
If Exception Not Re-raised, No More Exception!

5. Whenever you log an error, capture the call stack, error code, error stack and error backtrace.

Ideally, this is a total non-issue for you, because you simply invoke a generic logger procedure in your exception handlers (example and recommendation: download and use Logger, an open source utility that does almost anything and everything you can think of).

But if you are about to write your own (or are using a home-grown logging utility), make sure that you cal and store in your log (likely a relational table), the values returned by:

• SQLCODE
• DBMS_UTILITY.FORMAT_CALL_STACK (or corresponding subprograms in 12.1's UTL_CALL_STACK package) - answers question "How did I get here?"
• DBMS_UTILITY.FORMAT_ERROR_STACK (or corresponding subprograms in 12.1's UTL_CALL_STACK package) - answers question "What is my error message/stack?" We recommend using this instead of SQLERRM.
• DBMS_UTILITY.FORMAT_ERROR_BACKTRACE (or corresponding subprograms in 12.1's UTL_CALL_STACK package) - answers question "On what line was the error raised?"

Verify on LiveSQL
Basic Error Logging Package
"How did I get here?" DBMS_UTILITY.FORMAT_CALL_STACK

UTL_CALL_STACK: Fine-grained execution call stack package (12.1)
Error Message Functions: SQLERRM and DBMS_UTILITY.FORMAT_ERROR_STACK
Back Trace Exception to Line That Raised It

6. Always log your error (and backtrace) before re-raising the exception.

When you re-raise an exception, you will reset the backtrace (the track back to the line on which the error was raised) and might change the error code (if you raise a different exception to propagate the exception "upwards").

So it is extremely important to call you error logging subprogram (see previous Good to Know) before you re-raise an exception.

Verify on LiveSQL
Back Trace Exception to Line That Raised It

7. Compile-time warnings will help you avoid "WHEN OTHERS THEN NULL".

One of Tom Kyte's favorite pet peeves, the following exception sections "swallow up" errors.

EXCEPTION
   WHEN OTHERS
   THEN
      NULL;

EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE (SQLERRM);

In fact, any exception handler that does not re-raise the same exception or another, runs the risk of hiding errors from the calling subprogram, your users, and yourself as you debug your code.

Generally, you should log the error, then re-raise it. 

There are certainly some cases in which this advice does not hold (for example: a function that fetches a single row for a primary key. If there is no row for the key, it's not an application error, so just return NULL). In those cases, include a comment so that the person maintaining your code in the distant future knows that you weren't simply ignoring the Wisdom of the Kyte. Example:

EXCEPTION
   WHEN OTHERS
   THEN
      /* No company or this ID, let calling subprogram decide what to do */
      RETURN NULL;

One way to avoid this problem is to turn on compile-time warnings. Then when your program unit is compiled, you will be warned if the compiler has identified an exception handler that does not contain a RAISE statement or a call to RAISE_APPLICATION_ERROR.

Verify on LiveSQL
Automatically Detect Exception Handlers That "Swallow Up" Errors

8. Use LOG ERRORS to suppress SQL errors at the row level.

The impact of a non-query DML statement is usually "all or nothing". If my update statement identifies 100 rows to change, then either all 100 rows are changed or none are. And none might be the outcome if, say, an error occurs on just one of the rows (value too large to fit in column, NULL value for non-NULL column, etc.).

But if you have a situation in which you would really like to "preserve" as many of those row-level changes as possible, you can add the LOG ERRORS clause to your DML statement. Then, if any row changes raise an error, that information is written to your error log table, and processing continues.

IMPORTANT: if you use LOG ERRORS, you must must must check that error log table immediately after the DML statement completes. You should also enhance the default error log table.

Verify on LiveSQL
Suppress DML Errors at Row Level with LOG ERRORS

Helper Package for LOG ERRORS

9. Send an application-specific error message to your users with RAISE_APPLICATION_ERROR.

If you execute a SELECT-INTO that does not identify any rows, the PL/SQL runtime engine raises: ORA-01403 and the error message (retrieved via SQLERRM or DBMS_UTILITY.FORMAT_ERROR_STACK) is simply "No data found".

That may be exactly what you want your users to see. But there is a very good chance you'd like to offer something more informative, such as "An employee with that ID is not in the system."

In this case, you can use RAISE_APPLICATION_ERROR, as in:

CREATE OR REPLACE PACKAGE BODY employees_mgr
IS
   FUNCTION onerow (employee_id_in IN hr.employees.employee_id%TYPE)
      RETURN hr.employees%ROWTYPE
      RESULT_CACHE
   IS
      l_employee hr.employees%ROWTYPE;
   BEGIN
      SELECT *
        INTO l_employee
        FROM hr.employees
       WHERE employee_id = employee_id_in;

      RETURN l_employee;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         raise_application_error (
            -20000,
            'An employee with that ID is not in the system.');
   END;
END;

Verify on LiveSQL
Send Application-specific Error Message To Users With RAISE_APPLICATION_ERROR