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

Oracle PL/SQL is one of the most mature and robust database programming languages available today. While it is a proprietary language from Oracle Corporation, both IBM DB2 and PostgresSQL offer varying levels of support for compilation and execution of PL/SQL code within their databases.

The PL/SQL offers an especially powerful error management facility, fully documented here. The following article highlights nine aspects of PL/SQL error management you should be aware of before embarking on your next app dev effort.

I offer links to a number of Oracle LiveSQL scripts you can run to validate the good-to-knows. LiveSQL offers 24x7 access to a free Oracle Database 12c Release 1 instance, so you can play around with both SQL and PL/SQL.

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.

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:

Verify on LiveSQL

• Send Application-specific Error Message To Users With RAISE_APPLICATION_ERROR