Oracle PL/SQL Best Practices

Name

EXC-12: Standardize named application exceptions in package specifications.

Synopsis

It’s likely that a developer will raise a certain error or errors in the process of using your code, you should declare exceptions in the package specification. Users of your code can then trap and handle those errors by name.

This approach is used most often for application-specific exceptions, but if your program might also raise an Oracle exception that has not been given a name in the STANDARD or other built-in package, you can give it a name and associate it with that number. See [EXC-14: Use the EXCEPTION_INIT pragma to name system exceptions that might be raised by your program.] for more details.

Example

Suppose that my overdue.analyze_status procedure might raise one of the following two errors:

“Overdue more than one month”

I have defined this as a serious error in my database. I must immediately stop processing and raise an exception.

“Fetch out of sequence”

This is an Oracle error that occurs when something goes wrong in my cursor FOR loop.

I then add these lines to my overdue package:

CREATE OR REPLACE PACKAGE overdue
IS
   excessive_lateness EXCEPTION;
   PRAGMA EXCEPTION_INIT (
      excessive_lateness, -20700);

   fetch_out_of_sequence EXCEPTION;
   PRAGMA EXCEPTION_INIT (
      fetch_out_of_sequence, -1003);

Benefits

Programmers have a better sense of what to expect—and what kind of exception handlers to write—when using your code.

Resources

sqlerr.pks: Package of predefined exceptions that commonly occur ...