Managing User-Defined Exceptions in PL/SQL

Database version: Oracle 11g

There are two basic ways to raise a user-defined exception in PL/SQL.

Here's one way.

DECLARE
  my_exception EXCEPTION;
BEGIN
  RAISE my_exception;
END;
/

ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 5

This method is simple, but it doesn't provide you with any information when the error is thrown. Here is a better method.

BEGIN
  raise_application_error(-20100,
                          'describe your exception here');
END;
/

ORA-20100: describe your exception here
ORA-06512: at line 3

Oracle gives us the somewhat stingy range of -20000 to -20999 (inclusive) for error codes that can be passed to raise_application_error. (Incidentally, you can also pass in decmial numbers such as -20001.142435.) But assuming I need to raise the same application error in multiple places, what's the best way for me to consistently use the same error number and the same error message?

The following is a simple framework that can help manage error codes and error messages. It has two components: a package that defines the exceptions and links them to an error number, and a table that associates error numbers with error messages.

Here's the table.

-- create table to relate error codes to error definitions
CREATE TABLE error_def (
       error_id NUMBER, 
       message VARCHAR2(1000), 
       comments VARCHAR2(1000), 
       CONSTRAINT error_def_pk PRIMARY KEY(error_id)
);

Something to consider: with some additional work you could tie a particular range of error numbers to a particular application. You would do this by creating a second table that links applications to error ranges (min/max). Then add a foreign key to the error_def table to tie the error to a particular application, and a before insert trigger to ensure that the error_id is within the valid range for its application.

Here's the package.

-- create a package to tie exceptions to numbers and messages
CREATE OR REPLACE PACKAGE errlib IS

  SUBTYPE errmsg IS error_def.message%TYPE;
  SUBTYPE errnum IS error_def.error_id%TYPE;

  FUNCTION f_errmsg(p_errnum_in IN errnum) RETURN errmsg;

  /* Write these three statements for each of your errors */
  invalid_status_errnum CONSTANT errnum := -20105;
  invalid_status EXCEPTION;
  PRAGMA EXCEPTION_INIT(invalid_status,
                        -20105);

END errlib;
/

CREATE OR REPLACE PACKAGE BODY errlib IS

  FUNCTION f_errmsg(p_errnum_in IN errnum) RETURN errmsg IS
    l_errmsg errmsg;
  BEGIN
    SELECT error_def.message
    INTO   l_errmsg
    FROM   error_def
    WHERE  error_def.error_id = p_errnum_in;
    RETURN l_errmsg;
  EXCEPTION
    WHEN no_data_found THEN
      RETURN NULL;
  END f_errmsg;

END errlib;
/

Each of your exceptions will need to have three entries in the errlib spec. One downside of this approach is that it may invalidate other packages that use the errlib package, though you may be OK (depending on your version of Oracle) if add your new entries at the bottom of the spec.

Finally, here's how you set the error message for the exception.

-- add an application error message to the table
INSERT INTO error_def
  (error_id, message, comments)
VALUES
  (-20105, 'Something bad happened!', 'If you see this error, call Bill.');
COMMIT;

Test it!

-- test!
BEGIN
  raise_application_error(errlib.invalid_status_errnum,
                          errlib.f_errmsg(errlib.invalid_status_errnum));
END;
/

ORA-20105: Something bad happened!
ORA-06512: at line 3