6.2.1 Declaring Named Exceptions

The exceptions that PL/SQL has declared in the STANDARD package (and other built-in packages) cover internal or system-generated errors. Many of the problems a user will encounter (or cause) in an application, however, are specific to that application. Your program might need to trap and handle errors such as "negative balance in account" or "call date cannot be in the past." While different in nature from "division by zero," these errors are still exceptions to normal processing and should be handled gracefully by your program.

One of the most useful aspects of the PL/SQL exception-handling model is that it does not make any structural distinction between internal errors and application-specific errors. Once an exception is raised, it can and should be handled in the exception section, regardless of the type or source of error.

Of course, to handle an exception, you must have a name for that exception. Because PL/SQL cannot name these exceptions for you (they are specific to your application), you must do so yourself by declaring an exception in the declaration section of your PL/SQL block. You declare an exception by listing the name of the exception you want to raise in your program followed by the keyword EXCEPTION:

exception_name EXCEPTION;

The following declaration section of the calc_annual_sales procedure contains two programmer-defined exception declarations:

PROCEDURE calc_annual_sales

(company_id_in IN company.company_id%TYPE)

IS

invalid_company_id EXCEPTION;

negative_balance EXCEPTION;

duplicate_company BOOLEAN;

BEGIN

... body of executable statements ...

EXCEPTION

...

WHEN NO_DATA_FOUND -- system exception

THEN

...

WHEN invalid_company_id

THEN

WHEN negative_balance

THEN

...

END;

The names for exceptions are similar in format to (and "read" just like) Boolean variable names, but 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

6.2.2 Associating Exception Names with Error Codes

Oracle has given names to just a handful of exceptions. Thousands of other error conditions within the RDBMS are defined by nothing more than an error number and a message. In addition, a developer can raise exceptions using RAISE_APPLICATION_ERROR (covered later under Section 6.3) that consist of nothing more than an error number (between -20000 and -20999) and an error message.

Exceptions without names are perfectly legitimate, but they can lead to code that is hard to read and maintain. Suppose, for example, that I write a program in which I know Oracle might raise a date-related error, such as ORA-01843: not a valid month. I could write an exception handler to trap that error with code that looks like this:

EXCEPTION

WHEN OTHERS THEN

IF SQLCODE = -1843 THEN

but that is very obscure code, begging for a comment—or some sort of clarity.

SQLCODE is a built-in function that returns the number of the last error raised; it is discussed later in this chapter under Section 6.4.