Oracle PL/SQL Best Practices

Name

EXC-09: Use named constants to soft-code application-specific error numbers and messages.

Synopsis

Oracle allocates 1000 error numbers, between -20,000 and -20,999, to use for our own application-specific errors (such as “Employee must be 18 years old” or “Reservation date must be in the future”).

Define all error numbers and their associated messages in a database table or operating-system file. Build a package that gives names to these errors, and then raise the errors using those names and not any hard-coded values.

Example

Here’s a fairly typical tangle of hard-coded, error-prone programming with RAISE_APPLICATION_ERROR. Sam Developer is told to write a procedure to stop updates and inserts when an employee is younger than 18. Sam thinks to himself “Surely no one has used error 20734 yet, so I will use it” and produces this code:

CREATE OR REPLACE PROCEDURE check_hiredate (
   date_in IN DATE)
IS
BEGIN
   IF date_in < ADD_MONTHS (SYSDATE, -1 * 12 * 18)
   THEN
      RAISE_APPLICATION_ERROR (
         -20734,
         'Employee must be 18 years old.');
   END IF;
END;

Check out all that hard-coding! And while Sam is writing his code, of course, Natasha Programmer also decides that 20734 is a fine error number. What a mess! Here’s a much cleaner approach:

CREATE OR REPLACE PROCEDURE check_hiredate (
   date_in IN DATE)
IS
BEGIN
   IF emp_rules.emp_too_young (date_in)
   THEN
      err.raise (errnums.emp_too_young);
   END IF;
END; 

First, I have moved the logic defining a “too young” employee to a function, as recommended in [MOD-01: ...