Oracle Live SQL - Script: How NOT to Handle Exceptions in PL/SQL

Statement 1

CREATE TABLE employees AS SELECT * FROM hr.employees

Table created.

Statement 2

Worst exception handler EVER. Completely swallows up and ignores just about any error the Oracle Database engine will raise. DO NOT DO THIS.

WHEN OTHERS THEN NULL

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;

Procedure created.

Statement 3

There is no way to know there was a problem in my_procedure.

What, Me Failed?

BEGIN my_procedure (100); END;

Statement processed.

Statement 4

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.

Want to Ignore 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;

Procedure created.

Statement 5

We want to see this error!

Bad Table Name

BEGIN my_procedure ('NEW!!TABLE'); END;

ORA-00922: missing or invalid option ORA-06512: at "SQL_UUOSBQCXHGMVLZPIOONWLQXQR.MY_PROCEDURE", line 23
ORA-06512: at "SQL_UUOSBQCXHGMVLZPIOONWLQXQR.MY_PROCEDURE", line 7
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-00922

Statement 6

Table Already Exists? Fine.

BEGIN my_procedure ('EMPLOYEES'); END;

Statement processed.

Statement 7

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.

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;

Procedure created.

Statement 8

BEGIN my_procedure (100); END;

ORA-06501: PL/SQL: program error ORA-06512: at "SQL_UUOSBQCXHGMVLZPIOONWLQXQR.MY_PROCEDURE", line 10
ORA-06512: at "SQL_UUOSBQCXHGMVLZPIOONWLQXQR.MY_PROCEDURE", line 6
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-06501

Statement 9

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?

"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;

Procedure created.

Statement 10

BEGIN my_procedure (100); END;

Program failed!

Statement 11

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.

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); 
END;

Procedure created.

Statement 12

BEGIN my_procedure (100); END;

ORA-06501: PL/SQL: program error

Statement 13

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.

Convert Exception to Status Code?

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;

Procedure created.

Statement 14

Please don't do this with PL/SQL. With this approach, after control is returned from the subprogram, you must evaluate the status code to see if you should continue. If you forget to do this, you could end up with data issues and very hard to diagnose bugs. By relying instead on the default exception handling, the PL/SQL engine automatically determines if a block should be terminated. Much cleaner, much more likely to result in a high quality application!

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;

ORA-06501: PL/SQL: program error ORA-06512: at line 9
ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-06501

Statement 15

You should write error information to a log table. Here's a very simple example. Even better is to download and use Logger (github.com/oraopensource/logger).

Write Errors to Log Table

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 
)

ORA-06501: PL/SQL: program error

Statement 16

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!

But Never Write to Log Table This Way!

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;

Procedure created.

Statement 17

BEGIN my_procedure (100); END;

ORA-06501: PL/SQL: program error ORA-06512: at "SQL_UUOSBQCXHGMVLZPIOONWLQXQR.MY_PROCEDURE", line 22
ORA-06512: at "SQL_UUOSBQCXHGMVLZPIOONWLQXQR.MY_PROCEDURE", line 5
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-06501

Statement 18

See? No Log Info!

SELECT title, info FROM error_log

no data found

Statement 19

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.

So I Should 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;

Procedure created.

Statement 20

BEGIN  
   my_procedure (100);  
EXCEPTION 
   WHEN OTHERS 
   THEN 
      ROLLBACK to error_logged; 
      DBMS_OUTPUT.PUT_LINE ('Process terminated with error'); 
END;

Process terminated with error

Statement 21

SELECT title, info FROM error_log


TITLE INFO
Program failed value_in = 100

TITLE	INFO
Program failed	value_in = 100

Statement 22

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.

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;

Procedure created.

Statement 23

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.

Ah, This is More Like It

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;

Procedure created.

Statement 24

BEGIN my_procedure (100); END;

ORA-06501: PL/SQL: program error ORA-06512: at "SQL_UUOSBQCXHGMVLZPIOONWLQXQR.MY_PROCEDURE", line 23
ORA-06512: at "SQL_UUOSBQCXHGMVLZPIOONWLQXQR.MY_PROCEDURE", line 12
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-06501

Statement 25

SELECT TO_CHAR (created_on, 'YYYY-MM-DD HH24:MI:SS') log_ts, title, info, errorbacktrace  
  FROM error_log 
 ORDER BY id DESC


LOG_TS TITLE INFO ERRORBACKTRACE
2017-01-26 07:19:42 my_procedure failed value_in = 100 | l_local_variable = 2017-02-05 07:19:42 ORA-06512: at "SQL_UUOSBQCXHGMVLZPIOONWLQXQR.MY_PROCEDURE", line 12
2017-01-26 07:19:42 Program failed value_in = 100 ORA-06512: at "SQL_UUOSBQCXHGMVLZPIOONWLQXQR.MY_PROCEDURE", line 10


2 rows selected.

LOG_TS	TITLE	INFO	ERRORBACKTRACE
2017-01-26 07:19:42	my_procedure failed	value_in = 100 \| l_local_variable = 2017-02-05 07:19:42	ORA-06512: at "SQL_UUOSBQCXHGMVLZPIOONWLQXQR.MY_PROCEDURE", line 12
2017-01-26 07:19:42	Program failed	value_in = 100	ORA-06512: at "SQL_UUOSBQCXHGMVLZPIOONWLQXQR.MY_PROCEDURE", line 10

Statement 26

The

Use Compile-Time Warnings to Help Identify Weak Handlers

ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL'

Statement processed.

Statement 27

With warnings enabled, I am now notified by the compiler of a handler that does not re-raise an exception.

Automatic Detection of Awful Handler

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;

Warning: PROCEDURE MY_PROCEDURE
Line: 8 PLW-06009: procedure "MY_PROCEDURE" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR

More Details: https://docs.oracle.com/error-help/db/ora-20001

How NOT to Handle Exceptions in PL/SQL

Additional Information