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 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.
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
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.
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;
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!