Skip to main content

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.


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

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.

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.


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:

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



Comments

  1. Your blog has given me that thing which I never expect to get from all over the websites. Nice post guys!

    ReplyDelete

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel