[Chapter 8] 8.3 Types of Exceptions

8.3 Types of Exceptions

There are four kinds of exceptions in PL/SQL:

Named system exceptions. Exceptions that have been given names by PL/SQL and raised as a result of an error in PL/SQL or RDBMS processing.
Named programmer-defined exceptions. Exceptions that are raised as a result of errors in your application code. You give these exceptions names by declaring them in the declaration section. You then raise the exceptions explicitly in the program.
Unnamed system exceptions. Exceptions that are raised as a result of an error in PL/SQL or RDBMS processing but have not been given names by PL/SQL. Only the most common errors are so named; the rest have numbers and can be assigned names with the special PRAGMA EXCEPTION_INIT syntax.
Unnamed programmer-defined exceptions. Exceptions that are defined and raised in the server by the programmer. In this case, the programmer provides both an error number (between -20000 and -20999) and an error message, and raises that exception with a call to RAISE_APPLICATION_ERROR. That error, along with its message, is propagated back to the client-side application.

The system exceptions (both named and unnamed) are raised by PL/SQL whenever a program violates a rule in the RDBMS (such as "duplicate value in index") or causes a resource limit to be exceeded (such as "maximum open cursors exceeded"). Each of these RDBMS errors has a number associated with it. In addition, PL/SQL predefines names for some of the most commonly encountered errors.

8.3.1 Named System Exceptions

The exceptions which are already given names by PL/SQL are declared in the STANDARD package in PL/SQL. You do not have to declare them in your own programs.[ 1 ] Each of the predefined exceptions is listed in Table 8.1 along with its Oracle error number, the value returned by a call to SQLCODE, and a brief description. SQLCODE is a PL/SQL built-in function that returns the status code of the last-executed statement. SQLCODE returns zero if the last statement executed without errors. In most, but not all, cases, the SQLCODE value is the same as the Oracle error code.

[1] If you do so, in fact, you will have declared your own local exception. It will not be raised when the internal error with that name occurs. Avoid declaring an exception with the same name as a predefined exception.

Here is an example of how you might use the exceptions table. Suppose that your program generates an unhandled exception for error ORA-6511. Looking up this error, you find that it is associated with the CURSOR_ALREADY_OPEN exception. Locate the PL/SQL block in which the error occurs and add an exception handler for CURSOR_ALREADY_OPEN, as shown below:

EXCEPTION
   WHEN CURSOR_ALREADY_OPEN
   THEN
      CLOSE my_cursor;
END;

Of course, you would be even better off analyzing your code to determine proactively which of the predefined exceptions might occur. Then you could decide which of those exceptions you want to handle specifically, which should be covered by the WHEN OTHERS clause, and which would best be left unhandled.

Table 8.1: Predefined Exceptions in PL/SQL
Oracle Error/SQLCODE	Description
CURSOR_ALREADY_OPEN ORA-6511 SQLCODE= -6511	You tried to OPEN a cursor that was already OPEN. You must CLOSE a cursor before you try to OPEN or re-OPEN it.
DUP_VAL_ON_INDEX ORA-00001 SQLCODE= -1	Your INSERT or UPDATE statement attempted to store duplicate values in a column or columns in a row which is restricted by a unique index.
INVALID_CURSOR ORA-01001 SQLCODE= -1001	You made reference to a cursor that did not exist. This usually happens when you try to FETCH from a cursor or CLOSE a cursor before that cursor is OPENed.
INVALID_NUMBER ORA-01722 SQLCODE = -1722	PL/SQL executes a SQL statement that cannot convert a character string successfully to a number. This exception is different from the VALUE_ERROR exception, as it is raised only from within a SQL statement.
LOGIN_DENIED ORA-01017 SQLCODE= -1017	Your program tried to log onto the Oracle RDBMS with an invalid username-password combination. This exception is usually encountered when you embed PL/SQL in a 3GL language.
NO_DATA_FOUND ORA-01403 SQLCODE= +100	This exception is raised in three different scenarios: (1) You executed a SELECT INTO statement (implicit cursor) that returned no rows. (2) You referenced an uninitialized row in a local PL/SQL table. (3) You read past end of file with UTL_FILE package.
NOT_LOGGED_ON ORA-01012 SQLCODE= -1012	Your program tried to execute a call to the database (usually with a DML statement) before it had logged into the Oracle RDBMS.
PROGRAM_ERROR ORA-06501 SQLCODE= -6501	PL/SQL encounters an internal problem. The message text usually also tells you to "Contact Oracle Support."
STORAGE_ERRORORA-06500 SQLCODE= -6500	Your program ran out of memory or memory was in some way corrupted.
TIMEOUT_ON_RESOURCEORA-00051 SQLCODE= -51	A timeout occurred in the RDBMS while waiting for a resource.
TOO_MANY_ROWSORA-01422 SQLCODE= -1422	A SELECT INTO statement returned more than one row. A SELECT INTO can return only one row; if your SQL statement returns more than one row you should place the SELECT statement in an explicit CURSOR declaration and FETCH from that cursor one row at a time.
TRANSACTION_BACKED_OUTORA-00061 SQLCODE= -61	The remote part of a transaction is rolled back, either with an explicit ROLLBACK command or as the result of some other action.
VALUE_ERRORORA-06502 SQLCODE= -6502	PL/SQL raises the VALUE_ERROR whenever it encounters an error having to do with the conversion, truncation, or invalid constraining of numeric and character data. This is a very general and common exception. If this same type of error is encountered in a SQL DML statement within a PL/SQL block, then the INVALID_NUMBER exception is raised.
ZERO_DIVIDEORA-01476 SQLCODE= -1476	Your program tried to divide by zero.

8.3.2 Named Programmer-Defined Exceptions

The exceptions that PL/SQL has declared in the STANDARD package 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, as follows:

exception_name EXCEPTION;

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

PROCEDURE calc_annual_sales
   (company_id_in IN company.company_id%TYPE)
IS
   invalid_company_id   EXCEPTION;
   no_sales_for_company EXCEPTION;
   negative_balance     EXCEPTION;

   duplicate_company    BOOLEAN;
BEGIN
   ... body of executable statements ...
EXCEPTION
   WHEN invalid_company_id
   THEN
      ...
   WHEN no_sales_for_company
   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 no_sales_for_company;
```
In the WHEN clauses of the exception section (to handle the raised exception), as in:
```
WHEN no_sales_for_company THEN
```

8.3.3 Unnamed System Exceptions

What do you do when you want to trap an internal error raised by PL/SQL or the SQL engine, but it is not one of the lucky errors that have been given a predefined name? Although this error is identified only by its internal error number, exception handlers need a name by which they can check for a match. Sure, you can always use the WHEN OTHERS clause to simply catch any exceptions not previously handled. (This is covered in detail in Section 8.6.3, "Using SQLCODE and SQLERRM in WHEN OTHERS Clause" .) In many cases, however, you will want to trap specific errors in a way that clearly documents and highlights them in your code. To do this, you assign your own name to the Oracle or PL/SQL error that might be raised in your program, and then write a specific exception handler for that custom-named exception.

8.3.3.1 The EXCEPTION_INIT pragma

You can use a compiler construct called a pragma to associate a name with an internal error code. A pragma (introduced in Chapter 2, PL/SQL Language Fundamentals ) is a special instruction to the compiler that is processed at compile time instead of at runtime. A pragma called EXCEPTION_INIT instructs the compiler to associate or initialize a programmer-defined exception with a specific Oracle error number. With a name for that error, you can then raise this exception and write a handler which will trap that error. While in most cases you will leave it to Oracle to raise these system exceptions, you could also raise them yourself.

The pragma EXCEPTION_INIT must appear in the declaration section of a block, after the declaration of the exception name used in the pragma, as shown below:

DECLARE
   exception_name EXCEPTION;
   PRAGMA EXCEPTION_INIT (exception_name, error_code_literal);
BEGIN

where exception_name is the name of an exception and error_code_literal is the number of the Oracle error (including the minus sign, if the error code is negative, as is almost always the case).

In the following program code, I declare and associate an exception for this error:

ORA-2292 violated integrity constraining (OWNER.CONSTRAINT) -
         child record found.

This error occurs if I try to delete a parent record while there are child records still in that table. A child record is a record with a foreign key reference to the parent table:

PROCEDURE delete_company (company_id_in IN NUMBER)
IS
   /* Declare the exception. */
   still_have_employees EXCEPTION;

   /* Associate the exception name with an error number. */
   PRAGMA EXCEPTION_INIT (still_have_employees, -2292);
BEGIN
   /* Try to delete the company. */
   DELETE FROM company
    WHERE company_id = company_id_in;
EXCEPTION
   /* If child records were found, this exception is raised! */
   WHEN still_have_employees
   THEN
      DBMS_OUTPUT.PUT_LINE
         (' Please delete employees for company first.');
END;

When you use EXCEPTION_INIT, you must supply a literal number for the second argument of the pragma call. By explicitly naming this system exception, the purpose of the exception handler is self-evident.

The EXCEPTION_INIT pragma improves the readability of your programs by assigning names to otherwise obscure error numbers. You can employ the EXCEPTION_INIT pragma more than once in your program. You can even assign more than one exception name to the same error number.

8.3.4 Unnamed Programmer-Defined Exceptions

The final type of exception is the unnamed, yet programmer-defined exception. This kind of exception occurs when you need to raise an application-specific error from within the server and communicate this error back to the client application process. This scenario is more common than you might at first imagine. For example, even when you run all your Oracle software in a single hardware environment (such as a character-based SQL*Forms applications running on UNIX against an Oracle server on the same UNIX platform), you still have a separation between your client (SQL*Forms screen) and your server (the RDBMS).

You will trap some, perhaps most, application-specific errors on the client side. On the other hand, the Oracle7 architecture allows you to embed many of your business rules directly into your database structure, using database triggers, constraints, and stored procedures. In many cases, you will want to let the RDBMS trap and reject invalid database actions. To do this, you need a way to identify application-specific errors and return information about those error back to the client. This kind of error communication is illustrated in Figure 8.2 .

Figure 8.2: Error communication from server to client

I have called this type of exception "unnamed" and "programmer-defined." The programmer-defined aspect should be clear: because the error is application-specific, you cannot expect PL/SQL to have already defined it for you. The reason this type of exception is also unnamed is that you cannot name or declare an exception within a server-based program or database trigger and have the client-side tool handle that named exception. This identifier simply doesn't cross the great divide between client and server.

To get around this problem, Oracle provides a special procedure to allow communication of an unnamed, yet programmer-defined, server-side exception: RAISE_APPLICATION_ERROR. (The use of this procedure and exception type is discussed in Section 8.7, "Client-Server Error Communication" later in this chapter.) The specification for this procedure is as follows:

PROCEDURE RAISE_APPLICATION_ERROR
   (error_number_in IN NUMBER, error_msg_in IN VARCHAR2);

where error_number_in is the error number you have assigned to this error. The error_msg_in argument is the message that will be sent back with the error code to the client program.