Oracle raise_application_error error number best practice

Question

I have a question regarding the error codes (-20000 to -20999) for Raise Application Error.

1. Can we use same error code (eg -20000) only for different error scenarios at multiple places in PLSQL code?

2. If we can use same error code in all places, why do we have 1000 codes?

3. What is the best practice to use error codes in Raise Application Error?

Sample Code:

create table t(id number primary key);

declare
begin
  insert into t(id) values (1);
  insert into t(id) values (1);
  commit;
exception 
  when dup_val_on_index 
  then 
    raise_application_error(-20000, 'Cannot Insert duplicates');
  when others 
  then
    raise_application_error(-20000, sqlcode||'-'||sqlerrm);
end;

Answer 1

1. Can we use same error code (eg -20000) only for different error scenarios at multiple places in PLSQL code?

As Justin notes, you can certainly do that - just use one code. But it is likely to lead to confusion. I've seen it done, and usually in that case, the developers simply embed all critical information into the message, even including a code (they might, for example, already be using their own error codes that fall outside the acceptable range).

I suggest you follow Oracle's lead: assign ranges to areas of your application and then use error codes within a range when an application-specific error occurs in that part of the part.

2. If we can use same error code in all places, why do we have 1000 codes?

See above.

3. What is the best practice to use error codes in Raise Application Error?

Create a table in which you "register" error codes that are used, along with the message. Then developers can check to see "their" error is already registered and can re-use it. Or, more likely, they register a new error code and message.

Either way, you have a central point from which to organize the codes and hopefully minimize the change of two developers using the same error code.

Here's a script that does what I suggested above, along with a utility to generate a package with all of the errors defined and available for "soft-coded" reference.

CREATE TABLE msg_info (
   msgcode INTEGER,
   msgtype VARCHAR2(30),
   msgtext VARCHAR2(2000),
   msgname VARCHAR2(30),
   description VARCHAR2(2000)
   );

CREATE OR REPLACE PACKAGE msginfo
IS
   FUNCTION text (
      code_in IN INTEGER
    , type_in IN VARCHAR2
    , use_sqlerrm IN BOOLEAN := TRUE
   )
      RETURN VARCHAR2;

   FUNCTION name (code_in IN INTEGER, type_in IN VARCHAR2)
      RETURN VARCHAR2;

   PROCEDURE genpkg (
      NAME_IN IN VARCHAR2
    , oradev_use IN BOOLEAN := FALSE
    , to_file_in IN BOOLEAN := TRUE
    , dir_in IN VARCHAR2 := 'DEMO' -- UTL_FILE directory
    , ext_in IN VARCHAR2 := 'pkg'
   );
END;
/

CREATE OR REPLACE PACKAGE BODY msginfo
IS
   FUNCTION msgrow (code_in IN INTEGER, type_in IN VARCHAR2)
      RETURN msg_info%ROWTYPE
   IS
      CURSOR msg_cur
      IS
         SELECT *
           FROM msg_info
          WHERE msgtype = type_in AND msgcode = code_in;

      msg_rec   msg_info%ROWTYPE;
   BEGIN
      OPEN msg_cur;
      FETCH msg_cur INTO msg_rec;
      CLOSE msg_cur;
      RETURN msg_rec;
   END;

   FUNCTION text (
      code_in IN INTEGER
    , type_in IN VARCHAR2
    , use_sqlerrm IN BOOLEAN := TRUE
   )
      RETURN VARCHAR2
   IS
      msg_rec   msg_info%ROWTYPE   := msgrow (code_in, type_in);
   BEGIN
      IF msg_rec.msgtext IS NULL AND use_sqlerrm
      THEN
         msg_rec.msgtext := SQLERRM (code_in);
      END IF;

      RETURN msg_rec.msgtext;
   END;

   FUNCTION NAME (code_in IN INTEGER, type_in IN VARCHAR2)
      RETURN VARCHAR2
   IS
      msg_rec   msg_info%ROWTYPE   := msgrow (code_in, type_in);
   BEGIN
      RETURN msg_rec.msgname;
   END;

   PROCEDURE genpkg (
      NAME_IN IN VARCHAR2
    , oradev_use IN BOOLEAN := FALSE
    , to_file_in IN BOOLEAN := TRUE
    , dir_in IN VARCHAR2 := 'DEMO'
    , ext_in IN VARCHAR2 := 'pkg'
   )
   IS
      CURSOR exc_20000
      IS
         SELECT *
           FROM msg_info
          WHERE msgcode BETWEEN -20999 AND -20000 AND msgtype = 'EXCEPTION';

      -- Send output to file or screen?
      v_to_screen   BOOLEAN         := NVL (NOT to_file_in, TRUE);
      v_file        VARCHAR2 (1000) := name_in || '.' || ext_in;

      -- Array of output for package
      TYPE lines_t IS TABLE OF VARCHAR2 (1000)
         INDEX BY BINARY_INTEGER;

      output        lines_t;

      -- Now pl simply writes to the array.
      PROCEDURE pl (str IN VARCHAR2)
      IS
      BEGIN
         output (NVL (output.LAST, 0) + 1) := str;
      END;

      -- Dump to screen or file.
      PROCEDURE dump_output
      IS
      BEGIN
         IF v_to_screen
         THEN
            FOR indx IN output.FIRST .. output.LAST
            LOOP
               DBMS_OUTPUT.put_line (output (indx));
            END LOOP;
         ELSE
            -- Send output to the specified file.
            DECLARE
               fid   UTL_FILE.file_type;
            BEGIN
               fid := UTL_FILE.fopen (dir_in, v_file, 'W');

               FOR indx IN output.FIRST .. output.LAST
               LOOP
                  UTL_FILE.put_line (fid, output (indx));
               END LOOP;

               UTL_FILE.fclose (fid);
            EXCEPTION
               WHEN OTHERS
               THEN
                  DBMS_OUTPUT.put_line (   'Failure to write output to '
                                        || dir_in
                                        || '/'
                                        || v_file
                                       );
                  UTL_FILE.fclose (fid);
            END;
         END IF;
      END dump_output;
   BEGIN
      /* Simple generator, based on DBMS_OUTPUT. */
      pl ('CREATE OR REPLACE PACKAGE ' || NAME_IN);
      pl ('IS ');

      FOR msg_rec IN exc_20000
      LOOP
         IF exc_20000%ROWCOUNT > 1
         THEN
            pl (' ');
         END IF;

         pl ('   exc_' || msg_rec.msgname || ' EXCEPTION;');
         pl (   '   en_'
             || msg_rec.msgname
             || ' CONSTANT INTEGER := '
             || msg_rec.msgcode
             || ';'
            );
         pl (   '   PRAGMA EXCEPTION_INIT (exc_'
             || msg_rec.msgname
             || ', '
             || msg_rec.msgcode
             || ');'
            );

         IF oradev_use
         THEN
            pl ('   FUNCTION ' || msg_rec.msgname || ' RETURN INTEGER;');
         END IF;
      END LOOP;

      pl ('END ' || NAME_IN || ';');
      pl ('/');

      IF oradev_use
      THEN
         pl ('CREATE OR REPLACE PACKAGE BODY ' || NAME_IN);
         pl ('IS ');

         FOR msg_rec IN exc_20000
         LOOP
            pl ('   FUNCTION ' || msg_rec.msgname || ' RETURN INTEGER');
            pl ('   IS BEGIN RETURN en_' || msg_rec.msgname || '; END;');
            pl ('   ');
         END LOOP;

         pl ('END ' || NAME_IN || ';');
         pl ('/');
      END IF;

      dump_output;
   END;
END;
/

/* Sample data to be used in package generation. */

BEGIN
   INSERT INTO msg_info
     VALUES (-20100, 'EXCEPTION', 'Balance too low', 'bal_too_low'
           , 'Description');
   INSERT INTO msg_info
     VALUES (-20200, 'EXCEPTION', 'Employee too young', 'emp_too_young'
           , 'Description');

   COMMIT;
END;
/

Answer 2

Can you use the same error code every time? Sure.

Should you? Almost certainly not. It would be rather annoying if Oracle raised an ORA-00001 error for every possible thing that went wrong-- a primary key violation, a foreign key violation, an unexpected internal error, a tablespace running out of space, a permission error, etc.-- because that makes it much more difficult for developers to handle the errors they can and propagate those they cannot. You'd have to do things like parse the text of the error string to figure out what went wrong and to figure out whether it's an error that you can handle or not. And heaven forbid that Oracle ever change the text of an error message or your parser causes you to misinterpret an error message. Similarly, it would generally be annoying if your code threw the same error code for every possible problem that was encountered.

If you are going to use a custom error code, it should communicate something above and beyond what the Oracle error code provides. It makes no sense, for example, to have a when others that converts the nice, usable Oracle error message and error stack to a pointless user-defined error. It may make perfect sense, though, for an ORA-20001 error to indicate that a foo already exists and an ORA-20002 error to indicate that a bar already exists when you have an application that deals with lots of foo's and bar's and those errors would make more sense to a user than a generic duplicate key error.