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.
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.
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.
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:
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:
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.
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.
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.
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 .
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);
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.