Skip to main content

How NOT to Handle Exceptions

Oracle Database raises an exception when something goes wrong (examples: divide by zero, duplicate value on unique index, value too large to fit in variable, etc.). You can also raise exceptions when an application error occurs (examples: balance too low, person not young enough, department ID is null).

If that exception occurs in your code, you have to make a decision:
Should I handle the exception and let it propagate out unhandled?
You should let it propagate unhandled (that is, not even trap it and re-raise) if handling it in that subprogram or block will not "add value" - there are no local variables whose values you need to log, for example.

The reason I say this is that at any point up the stack of subprogram invocations, you can always call DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and it will trace back to the line number on which the error was originally raised.

If you are going to handle the exception, you have to make several decisions:
  • Should I log the error? Yes, because you want to be able to go back later (or very soon) and see if you can diagnose the problem, then fix it.
  • Should I notify anyone of the error? This depends on your support infrastructure. Maybe depositing a row in the log table is enough. 
  • What should I log along with the error information? Generally you the developer should think about the application state - local variables, packaged/global variables, table changes - for which a "snapshot" (logged values) would help you understand what caused the error.
  • Should I then re-raise the same exception or another after logging? Almost always, yes. Sometimes, it is true, you can safely "hide" an error - very common for a NO_DATA_FOUND on a SELECT-INTO, when a lack of data simply indicates need for a different action, not an error - but for the most part you should always plan to re-raise the exception back out to the enclosing block. 
So that's a high-level Q&A. Now let's dive into some anti-patterns (common patterns one finds in PL/SQL code that are "anti" - not good things to do) to drive these points home.

You can see and run all of this code on LiveSQL.

I also put together a YouTube playlist if you prefer video.

OK, here we go.

1. Worst Exception Handler Ever

CREATE OR REPLACE PROCEDURE my_procedure (value_in IN INTEGER) 
   AUTHID DEFINER 
IS 
BEGIN 
   /* Lots of code executing and then... */ 
   RAISE PROGRAM_ERROR; 
EXCEPTION 
   WHEN OTHERS 
   THEN 
      NULL; 
END;
Completely swallows up and ignores just about any error the Oracle Database engine will raise. DO NOT DO THIS.

Want to ignore the error? Make it explicit and log it anyway:
CREATE OR REPLACE PROCEDURE my_procedure (name_in IN VARCHAR2) 
   AUTHID DEFINER 
IS 
   e_table_already_exists EXCEPTION; 
   PRAGMA EXCEPTION_INIT (e_table_already_exists, -955); 
BEGIN 
   EXECUTE IMMEDIATE 'CREATE TABLE ' || name_in || ' (n number)'; 
EXCEPTION 
   /* A named handler */ 
   WHEN e_table_already_exists 
   THEN 
      /* Even better: write a message to log. */ 
      NULL; 
    
   /* CHecking SQLCODE inside WHEN OTHERS */ 
   WHEN OTHERS 
   THEN 
      IF SQLCODE = -955 
      THEN 
         /* ORA-00955: name is already used by an existing object */ 
         NULL; 
      ELSE 
         RAISE; 
      END IF; 
END;
If you can anticipate a certain error being raised and "That's OK", then handle it explicitly, either with an "IF SQLCODE = " inside WHEN OTHERS or by declaring an exception, associating it with the error code and then handling by name.

2. A Handler That Only Re-Raises: Why Bother?

CREATE OR REPLACE PROCEDURE my_procedure (value_in IN INTEGER) 
   AUTHID DEFINER 
IS 
BEGIN 
   /* Lots of code executing and then... */ 
   RAISE PROGRAM_ERROR; 
EXCEPTION 
   WHEN OTHERS 
   THEN 
      RAISE; 
END;
This handler doesn't hide the error - it immediately passes it along to the outer block. Why would you do this? Not only is there no "added value", but by re-raising, any calls to DBMS_UTILITY.FORMAT_ERROR_BACKTRACE will trace back to that later RAISE; and not to the line on which the error was *originally* raised.

Takeaway: don't handle unless you want to do something inside the handler, such as log the error information, raise a different exception or perform some corrective action.

3. "Log" Error with DBMS_OUTPUT? No Way!

CREATE OR REPLACE PROCEDURE my_procedure (value_in IN INTEGER) 
   AUTHID DEFINER 
IS 
BEGIN 
   /* Lots of code executing and then... */ 
   RAISE PROGRAM_ERROR; 
EXCEPTION 
   WHEN OTHERS 
   THEN 
      DBMS_OUTPUT.put_line ('Program failed!'); 
END;
OK, so I don't do *nothing* (NULL) in this handler, but I come awfully close. I do not re-raise, so the error is swallowed up. But I also simply write non-useful information out to the screen.

Allow me to repeat: TO THE SCREEN

If this code is running in production (the most important place from which to gather useful error-related diagnostics), can you see output to the screen via DBMS_OUTPUT? I bet not. And even if you could, surely you'd like to show more than a totally useful static piece of text?

4. Display Error and Re-Raise - Better But Still Pathetic

CREATE OR REPLACE PROCEDURE my_procedure (value_in IN INTEGER) 
   AUTHID DEFINER 
IS 
BEGIN 
   /* Lots of code executing and then... */ 
   RAISE PROGRAM_ERROR; 
EXCEPTION 
   WHEN OTHERS 
   THEN 
      DBMS_OUTPUT.put_line (SQLERRM); 
      RAISE;
END;
OK, now I display the current error message, but still stuck with output to the screen, and anyway (a) we recommend you call DBMS_UTILITY.format_error_stack instead, since it avoids some possible truncation issues with SQLERRM (for very long error stacks) and (b) you really do need to see more than that! At least, though, I do re-raise the error.

5. Do Not Convert Exceptions to Status Codes

CREATE OR REPLACE PROCEDURE my_procedure (value_in     IN     INTEGER, 
                                          status_out      OUT INTEGER) 
   AUTHID DEFINER 
IS 
BEGIN 
   IF value_in > 100 
   THEN 
      /* All is fine */ 
      /* Execute some code */ 
      /* Set status to "ok" */ 
      status_out := 0; 
   ELSE 
      RAISE PROGRAM_ERROR; 
   END IF; 
EXCEPTION 
   WHEN OTHERS 
   THEN 
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack); 
      status_out := SQLCODE; 
END; 
This is a common technique in some other programming languages. For example, in C, many people only write functions and the function's return value is the status. If the status is not 0 or some other magic value indicating success, then you must abort.

But, oh, the resulting code!
DECLARE 
   l_status INTEGER; 
BEGIN  
   my_procedure (100, l_status);  
 
   IF l_status <> 0 
   THEN 
      /* That didn't go well. Need to stop or do *something*! */ 
      RAISE program_error; 
   END IF; 
    
   my_procedure (110, l_status); 
    
   IF l_status <> 0 
   THEN 
      /* That didn't go well. Need to stop or do *something*! */ 
      RAISE program_error; 
   END IF; 
END; 

6. Write to a Log Table, But Not This Way

CREATE TABLE error_log 
( 
   id               NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
   title            VARCHAR2 (200), 
   info             CLOB, 
   created_on       DATE DEFAULT SYSDATE, 
   created_by       VARCHAR2 (100), 
   callstack        CLOB, 
   errorstack       CLOB, 
   errorbacktrace   CLOB 
)
/

CREATE OR REPLACE PROCEDURE my_procedure (value_in IN INTEGER) 
   AUTHID DEFINER 
IS 
BEGIN 
   RAISE PROGRAM_ERROR; 
EXCEPTION 
   WHEN OTHERS 
   THEN 
      INSERT INTO error_log (title, 
                             info, 
                             created_by, 
                             callstack, 
                             errorstack, 
                             errorbacktrace) 
           VALUES ('Program failed', 
                   'value_in = ' || value_in, 
                   USER, 
                   DBMS_UTILITY.format_call_stack, 
                   DBMS_UTILITY.format_error_stack, 
                   DBMS_UTILITY.format_error_backtrace); 
 
      RAISE; 
END;
/

You should write error information to a log table. Here's a very simple example. Even better is to download and use the open source and popular Logger.

It's good that I write my errors to a table, but terrible that I do it this way. You should never "hard code" the inserts right inside the handler.

First, that's bad because if you ever need to change the table structure you (might) have to go back to each handler and change it.

Second, in this way, the error log entry becomes a part of your session's transaction.

If the process ends with an unhandled exception, your log entry is rolled back along with the "bad" transaction. Error information lost!

7. Get Fancy with Savepoints?

CREATE OR REPLACE PROCEDURE my_procedure (value_in IN INTEGER) 
   AUTHID DEFINER 
IS 
BEGIN 
   SAVEPOINT my_transaction; 
 
   UPDATE employees 
      SET salary = value_in; 
 
   RAISE PROGRAM_ERROR; 
EXCEPTION 
   WHEN OTHERS 
   THEN 
      ROLLBACK TO my_transaction; 
 
      INSERT INTO error_log (title, 
                             info, 
                             created_by, 
                             callstack, 
                             errorstack, 
                             errorbacktrace) 
           VALUES ('Program failed', 
                   'value_in = ' || value_in, 
                   USER, 
                   DBMS_UTILITY.format_call_stack, 
                   DBMS_UTILITY.format_error_stack, 
                   DBMS_UTILITY.format_error_backtrace); 
 
      SAVEPOINT error_logged; 
 
      RAISE;  
END;

Um, no. Yes, you could use savepoints to make sure that the log entry is not rolled back - but then any rollbacks that occur "higher up" in the callstack have to know to rollback only to this savepoint. It's messy and hard to ensure consistency. The autonomous transaction feature is much to be preferred.

Best Approach: Reusable Error Logger

CREATE OR REPLACE PROCEDURE log_error (title_in   IN error_log.title%TYPE, 
                                       info_in    IN error_log.info%TYPE) 
   AUTHID DEFINER 
IS 
   PRAGMA AUTONOMOUS_TRANSACTION; 
BEGIN 
   INSERT INTO error_log (title, 
                          info, 
                          created_by, 
                          callstack, 
                          errorstack, 
                          errorbacktrace) 
        VALUES (title_in, 
                info_in, 
                USER, 
                DBMS_UTILITY.format_call_stack, 
                DBMS_UTILITY.format_error_stack, 
                DBMS_UTILITY.format_error_backtrace); 
 
   COMMIT; 
END;
This is a very simple example; again, the Logger project is a MUCH better example - and code you can use "out of the box". The key points are: (a) move the insert into the procedure so it appears just once and can be modified here as needed; (b) The autonomous transaction pragma to ensure that I can commit this insert without affecting the unsaved changes in my "outer" transaction/session.
CREATE OR REPLACE PROCEDURE my_procedure (value_in IN INTEGER)  
   AUTHID DEFINER  
IS  
   l_local_variable   DATE;  
BEGIN  
   l_local_variable :=  
      CASE WHEN value_in > 100 THEN SYSDATE - 10 ELSE SYSDATE + 10 END;  
  
   UPDATE employees  
      SET salary = value_in;  
  
   RAISE PROGRAM_ERROR;  
EXCEPTION  
   WHEN OTHERS  
   THEN  
      log_error (  
         'my_procedure failed',  
            'value_in = '  
         || value_in  
         || ' | l_local_variable = '  
         || TO_CHAR (l_local_variable, 'YYYY-MM-DD HH24:MI:SS'));  
  
      RAISE;  
END;
In my exception handler, I call the generic logging routine. In that call, I include the values of both my parameter and local variable, so that I can use this information later to debug my program. Note that the local variable's value is lost unless I handle and log here.

Finally, I re-raise the exception to ensure that the enclosing block is aware of the "inner" failure.

Lots of Ways To Go Wrong....

But it's also not that hard to do things right. Just remember:
  • Do not swallow up errors. Make sure you at least log the error, if you truly do not want or need to stop further processing.
  • Log all local application state before propagating the exception to the outer block.
  • Write all error log information to a table through a reusable logging procedure, which is defined as an autonomous transaction.
And why write one of these yourself? Logger does it all for you already!

Comments

  1. In number 4, your code doesn't re-raise the error, though the accompanying text says it does. Also you have two headings numbered 6.

    ReplyDelete
  2. Great article, Steven.

    So, is the Logger project alive?

    ReplyDelete
    Replies
    1. Definitely, though I think the main developers have been pre-occupied with other projects of late.

      Delete
  3. Undetected error: "If you can anticipate a certain being raised"
    should be:
    "If you can anticipate a certain error being raised"

    ReplyDelete
  4. Thank you for sharing this nice post.
    Foued

    ReplyDelete
  5. GREAT ARTICLE! "When others then null" is the king :D
    But, if I may, another very important thing which is often ommitted -
    one should log into the log table the key data for the process e.q. -
    the client id, the phone number or whatever you will later looking for...
    What's more - logs tables usually are big or very big. If you direclty choose the key data and key fields for searching -it would be easer to create an index.
    Too many times I saw very perfectly built logs but without easy to retrive information about the key data -
    or no information at all or hidden in the text field!

    Queries on these tables look like that:
    1. find max id from the log table
    2. search for rows where id > max - 10000 (id as the only filed with index!!)
    3. search by your eyes for errros or try to use MESSAGE_FILED LIKE '%my_client_nr%'

    exhaustig...

    ReplyDelete
  6. Steve: you do not mention an exception package as an additional exception handling improvement. My faithful reading of all your books seems to suggest that you recommend this as a great way to centralize exception handling for a complex of modules and ensure consistency of handling and logging, while reducing overall code complexity. Perhaps you could extend this discussion to indicate when it is worth implementing an exception package.

    ReplyDelete
    Replies
    1. Allen, that's a very good point, but I thought that's what I *was* mentioning in this section: Best Approach: Reusable Error Logger

      Delete
    2. I misspoke a bit. I meant a package of named exceptions with rational exception values, which may or may not include (all) the error handling itself. Perhaps Logger does this - I have not looked at it yet.

      Delete
    3. Gotcha. No, I doubt Logger does this. And then you run into the perennial question: one central package for all these or do you put user-defined exceptions and their pragmas->error codes into functional-area-specific packages? I tend toward the latter of late. Avoid those central dependency units.

      Delete
  7. Excellent post; I've been saying similar things for a while now:

    https://dfitzjarrell.wordpress.com/2008/03/04/what-was-that-masked-message/
    https://dfitzjarrell.wordpress.com/2008/05/02/ive-never-seen-that-error-before/
    https://dfitzjarrell.wordpress.com/2012/06/26/nullified-remains/

    ReplyDelete
  8. Hello

    I would like to ask you about one case.
    I coded two procedures whitch i execute in third procedure :

    CREATE OR REPLACE PROCEDURE Proc3 as
    Begin
    Proc1;
    Proc2;
    End;

    What about puting "Raise" command after "When others Then " statement.
    I want to execute Proc2 despite the error in Proc1.
    The solution to problem is placing "Raise" only in Proc3 exception clasule ?

    ReplyDelete
    Replies
    1. If you want to continue past an exception from proc1, simply put that call inside its own nested block, as in:

      begin
      begin
      proc1;
      exception when others then log_error;
      end;
      proc2;
      end;

      Delete
  9. Hi Steven sir,
    I think we could also log errors in table using pragmatic autonomous transaction to only commit that part.
    Please correct me if I am wrong.

    ReplyDelete
    Replies
    1. Somnath, good thinking! In fact, LOG ERRORS itself relies on an autonomous transaction unit to write to the error logging table. So, yes, you *sort of* do the same thing yourself. Here's the difference: anything YOU do will suppress the error at the statement level, at best. Only with LOG ERRORS can you get down to suppression of errors at the row level, allowing the statement execution to continue. Make sense?

      Delete
  10. This comment has been removed by a blog administrator.

    ReplyDelete
  11. I think my project predates Logger. I borrowed your idea Steve and developed it over a 15 year career, shaping it with each new thing I learned in PL/SQL. I rarely make changes now, but I bring it along to each new shop as a familiar tool that many (especially DBAs) have come to appreciate. I only "recently" set it loose on the Internet through GitHub. Look up "ORA-EXCEPTION-HANDLER" to see the handiwork. Feel free to copy and tinker with it, folks. I think it makes the use of custom error checkpoints look more elegant, and logging outputs cool as well with some of Oracle's built in packages. I agree with you, Mr. Feuerstein, PL/SQL is pretty cool, and what better to be paid writing it?

    ReplyDelete
    Replies
    1. Thanks for sharing, Rich!

      Folks, here's the link to Rich's repo:

      https://github.com/richardpascual/ora-exception-handler

      Delete
  12. Create a test-script which shows that exceptions raised differ between parallel and sequential execution (tested in 12.2) and also SQLERRM is inconsistent between dynamic SQL execution and hardcoded SQL. The last test shows that SQLERRM contains both exceptions while in all other cases, only the last exception is contained:

    dynamic SQL - rebuild unique index sequential
    catch uniq
    -1452
    ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
    ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

    dynamic SQL - rebuild unique index parallel
    catch others
    -12801
    ORA-12801: error signaled in parallel query server P00T, instance vpdm4525db01.dyndb.itoper.local:PCDB0020_45251 (1)
    ORA-12801: error signaled in parallel query server P00T, instance vpdm4525db01.dyndb.itoper.local:PCDB0020_45251 (1)
    ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

    dynamic SQL - insert with div 0 sequential
    catch div0
    -1476
    ORA-01476: divisor is equal to zero
    ORA-01476: divisor is equal to zero

    dynamic SQL - insert with div 0 parallel
    catch others
    -12801
    ORA-12801: error signaled in parallel query server P02H, instance vpdm4525db01.dyndb.itoper.local:PCDB0020_45251 (1)
    ORA-12801: error signaled in parallel query server P02H, instance vpdm4525db01.dyndb.itoper.local:PCDB0020_45251 (1)
    ORA-01476: divisor is equal to zero

    none dynamic - insert with div 0 sequential
    catch others
    -12801
    ORA-12801: error signaled in parallel query server P02H, instance vpdm4525db01.dyndb.itoper.local:PCDB0020_45251 (1)
    ORA-01476: divisor is equal to zero
    ORA-12801: error signaled in parallel query server P02H, instance vpdm4525db01.dyndb.itoper.local:PCDB0020_45251 (1)
    ORA-01476: divisor is equal to zero

    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