Oracle PL/SQL for DBAs

Defining Exceptions

Before an exception can be raised or handled, it must be defined. Oracle predefines thousands of exceptions, mostly by assigning numbers and messages to those exceptions. Oracle also assigns names to a relative few of these thousands—the most commonly encountered exceptions.

These names are assigned in the STANDARD package (one of two default packages in PL/SQL), as well as in other built-in packages such as UTL_FILE and DBMS_SQL. The code Oracle uses to define exceptions like NO_DATA_FOUND is the same code that you will write to define or declare your own exceptions. You can do this in two different ways, described in the following sections.

You can also declare your own exceptions by listing the name of the exception you want to raise in your program followed by the keyword EXCEPTION:

    DECLARE
       exception_name EXCEPTION;

The names for exceptions are similar in format to (and “read” just like) Boolean variable names, but they can be referenced in only two ways:

• In a RAISE statement in the execution section of the program (to raise the exception), as in:

      RAISE invalid_company_id;

• In the WHEN clauses of the exception section (to handle the raised exception), as in:

      WHEN invalid_company_id THEN