Nine Good to Knows for PL/SQL Error Management

Version 3

    I first published this content on my blog, but I figure it would also be helpful to make it available directly on this space. Apologies for some poor formatting. I find this wysiwyg editor less than optimal.

    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.

    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).
    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.

    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.

    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:
    • 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?"


    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.

    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.

      WHEN OTHERS   

    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:

    /* No company or this ID, let calling subprogram decide what to do */     
    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.

    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.

    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:


      FUNCTION onerow (employee_id_in IN hr.employees.employee_id%TYPE)
      RETURN hr.employees%ROWTYPE RESULT_CACHE   
         l_employee hr.employees%ROWTYPE;
          SELECT * INTO l_employee
             FROM hr.employees
            WHERE employee_id = employee_id_in;
           RETURN l_employee;
              raise_application_error (
                 -20000, 'An employee with ID "' || employee_id_in || '" is not in the system.');


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