Section 6.2.  Defining Exceptions

6.2. 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; DBMS_STANDARD is the other), 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 that you will write to define or declare your own exceptions. You can do this in two different ways, described in the following sections. 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 "Raising Exceptions) 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 commentor 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 "Handling Exceptions." 6.2.2.1 Using EXCEPTION_INIT With the EXCEPTION_INIT pragma, I can replace the WHEN clause shown in the previous example with something like this: EXCEPTION WHEN invalid_month THEN No more hardcoded literal error numbers that are difficult to remember. Now I have a self-documenting name. Let's see how we can accomplish this. EXCEPTION_INIT is a compile-time command or pragma used to associate a name with an internal error code. EXCEPTION_INIT instructs the compiler to associate an identifier, declared as an EXCEPTION, with a specific error number. Once you have made that association, you can then raise that exception by name and write an explicit WHEN handler that traps the error. The pragma EXCEPTION_INIT must appear in the declaration section of a block; the exception named must have already been defined in that same block, an enclosing block, or a package specification. Here is the syntax in an anonymous block: DECLARE exception_name EXCEPTION; PRAGMA EXCEPTION_INIT (exception_name, integer); where exception_name is the name of an exception and integer is a literal integer value, the number of the Oracle error with which you want to associate the named exception. The error number can be any integer value with these constraints: It cannot be -1403 (one of the two error codes for NO_DATA_FOUND). If for some reason you want to associate your own named exception with this error, you need to pass 100 to the EXCEPTION_INIT pragma. It cannot be 0 or any positive number besides 100. It cannot be a negative number less than -1000000. Let's look at an example. 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 row while there are child rows still in that table. (A child row is a row 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; 6.2.2.2 Recommended uses of EXCEPTION_INIT You will find this pragma most useful in two circumstances: Giving names to otherwise anonymous system exceptions that you commonly reference in your code. In other words, Oracle did not predefine a name for the error; you have only the number with which to work. Assigning names to the application-specific errors you raise using RAISE_APPLICATION_ERROR (see the section"Raising Exceptions). This allows you to handle such errors by name, rather than simply by number. In both cases, I recommend that you centralize your usage of EXCEPTION_INIT into packages so that the definitions of exceptions are not scattered throughout your code. Suppose, for example, that I am doing lots of work with dynamic SQL (described in Chapter 16). I might then encounter "invalid column name" errors as I construct my dynamic queries. I don't want to have to remember what the code is for this error, and it's silly to define my pragmas in 20 different programs. So instead I predefine my own "system exceptions" in my own dynamic SQL package: CREATE OR REPLACE PACKAGE dynsql IS invalid_table_name EXCEPTION; PRAGMA EXCEPTION_INIT (invalid_table_name, -903); invalid_column_name EXCEPTION; PRAGMA EXCEPTION_INIT (invalid_column_name, -904); and now I can trap for these errors in any program as follows: WHEN dynsql.invalid_column_name THEN ... I also suggest that you take this same approach when working with the -20,NNN error codes passed to RAISE_APPLICATION_ERROR. Avoid hardcoding these literals directly into your application; instead, build (or generate) a package that assigns names to those error numbers. Here is an example of such a package: PACKAGE errnums IS en_too_young CONSTANT NUMBER := -20001; exc_too_young EXCEPTION; PRAGMA EXCEPTION_INIT (exc_too_young, -20001); en_sal_too_low CONSTANT NUMBER := -20002; exc_sal_too_low EXCEPTION; PRAGMA EXCEPTION_INIT (exc_sal_too_low , -20002); END errnums; By relying on such a package, I can write code like the following, without embedding the actual error number in the logic: PROCEDURE validate_emp (birthdate_in IN DATE) IS min_years CONSTANT PLS_INTEGER := 18; BEGIN IF ADD_MONTHS (SYSDATE, min_years * 12 * -1) < birthdate_in THEN RAISE_APPLICATION_ERROR (errnums.en_too_young, 'Employee must be at least ' || min_years || ' old.'); END IF; END; 6.2.3. About Named System Exceptions Oracle gives names to a relatively small number of system exceptions by including EXCEPTION_INIT pragma statements in built-in package specifications. The most important and commonly used set of named exceptions may be found in the STANDARD package in PL/SQL. Because this package is one of the two default packages of PL/SQL, you can reference these exceptions without including the package name as a prefix. So, for instance, if I want to handle the NO_DATA_FOUND exception in my code, I can do so with either of these statements: WHEN NO_DATA_FOUND THEN WHEN STANDARD.NO_DATA_FOUND THEN You can find predefined exceptions in other built-in packages such as DBMS_LOB, the package used to manipulate large objects. Here is an example of one such definition in that package's specification: invalid_argval EXCEPTION; PRAGMA EXCEPTION_INIT(invalid_argval, -21560); Because DBMS_LOB is not a default package, when I reference this exception, I need to include the package name: WHEN DBMS_LOB.invalid_argval THEN... Many of the STANDARD-based predefined exceptions are listed in Table 6-1, each with its Oracle error number, the value returned by a call to SQLCODE (a built-in function that returns the current error code, described later in the section "Built-in Error Functions"), and a brief description. In all but one case (100, the ANSI standard error number for NO_DATA_FOUND), the SQLCODE value is the same as the Oracle error code. Table 6-1. Some of the predefined exceptions in PL/SQL Name of exception 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 that 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 because it is raised only from within a SQL statement. LOGIN_DENIED ORA-01017 SQLCODE= -1017 Your program tried to log into 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 the 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_ERROR ORA-06500 SQLCODE= -6500 Your program ran out of memory, or memory was in some way corrupted. TIMEOUT_ON_RESOURCE ORA-00051 SQLCODE=-51 A timeout occurred in the RDBMS while waiting for a resource. TOO_MANY_ROWS ORA-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_OUT ORA-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 (such as a failed SQL/DML on the remote database). VALUE_ERROR ORA-06502 SQLCODE= -6502 PL/SQL encountered 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 type of error is encountered in a SQL DML statement within a PL/SQL block, then the INVALID_NUMBER exception is raised. ZERO_DIVIDE ORA-01476 SQLCODE= -1476 Your program tried to divide by zero. 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 here: 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. You could then 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. 6.2.4. Scope of an Exception The scope of an exception is that portion of the code that is "covered" by that exception. An exception covers a block of code if it can be raised in that block. The following table shows the scope for each of the different kinds of exceptions: Exception type Description of scope Named system exceptions These exceptions are globally available because they are not declared in or confined to any particular block of code. You can raise and handle a named system exception in any block. Named programmer defined exceptions These exceptions can be raised and handled only in the execution and exception sections of the block in which they are declared (and all nested blocks). If the exception is defined in a package specification, its scope is every program whose owner has EXECUTE authority on that package. Anonymous system exceptions These exceptions can be handled in any PL/SQL exception section via the WHEN OTHERS section. If they are assigned a name, then the scope of that name is the same as that of the named programmer-defined exception. Anonymous programmer-defined exceptions These exceptions are defined only in the call to RAISE_APPLICATION_ERROR, and then are passed back to the calling program. Consider the following example of the exception overdue_balance declared in the procedure check_account. The scope of that exception is the check_account procedure, and nothing else: PROCEDURE check_account (company_id_in IN NUMBER) IS overdue_balance EXCEPTION; BEGIN ... executable statements ... LOOP ... IF ... THEN RAISE overdue_balance; END IF; END LOOP; EXCEPTION WHEN overdue_balance THEN ... END; I can RAISE the overdue_balance inside the check_account procedure, but I cannot raise that exception from a program that calls check_account. The following anonymous block will generate a compile error, as shown below: DECLARE company_id NUMBER := 100; BEGIN check_account (100); EXCEPTION WHEN overdue_balance /* PL/SQL cannot resolve this reference. */ THEN ... END; PLS-00201: identifier "OVERDUE_BALANCE" must be declared The check_account procedure is a "black box" as far as the anonymous block is concerned. Any identifiersincluding exceptionsdeclared inside check_account are invisible outside of that program.