10.10 How do I…Handle predefined exceptions and system errors?Problem
Any time an exception occurs in my PL/SQL block, it terminates the block. I need to handle exceptions in all my PL/SQL code to ensure that unexpected errors don’t terminate the program. When exceptions occur, I want to be sure that runtime errors are handled gracefully. How do I handle exceptions in PL/SQL?
Technique
The exception section of each PL/SQL block can handle one or more exceptions. The EXCEPTION keyword identifies the beginning of the exception section. If an exception occurs and an exception handler does not exist in the exception section to handle it, an unhandled exception error occurs and execution of the module is terminated. Handlers are listed in the exception section following the WHEN clause, as shown here:
EXCEPTION
WHEN first_exception THEN
<code to handle first exception>
WHEN second_exception THEN
<code to handle second exception>
...
WHEN OTHERS THEN
<code to handle any other system exceptions>
END;
Predefined exceptions are system exceptions that correspond to the most common Oracle runtime errors. Identifiers for predefined exceptions are defined in the STANDARD package, and these predefined exceptions are listed in Table 10.9. Exception handlers can appear in any order in the EXCEPTION section, except for the OTHERS exception handler, which appears as the last one. The exception handling code for OTHERS handles all other exceptions for which no exception handler is explicitly specified in the EXCEPTION section.
Table 10.9 Predefined exceptions.
Exception Name Oracle Error Description ACCESS_INTO_NULL1 ORA-6530 Assign values to attributes of an uninitialized object. COLLECTION_IS_NULL1 ORA-6531 With an uninitialized collection, assign values to elements, or apply collection methods other than EXISTS. CURSOR_ALREADY_OPEN ORA-6511 Open a cursor that is already open. DUP_VAL_ON_INDEX ORA-0001 Violate a unique constraint. INVALID_CURSOR ORA-1001 Use an invalid cursor. INVALID_NUMBER ORA-1722 Conversion of a string to a number failed. LOGIN_DENIED ORA-1017 Invalid username/password. NO_DATA_FOUND ORA-1403 No row returned by a SELECT INTO statement. Reference a deleted element in a nested table. Reference an initialized element in an Index- By table. NOT_LOGGED_ON ORA-1010 Attempt to perform a database operation when not connected. PROGRAM_ERROR ORA-6501 Internal error. ROWTYPE_MISMATCH2 ORA-6504 Host variable and cursor variable have incompatible types. STORAGE_ERROR ORA-6500 PL/SQL runs out of memory. SUBSCRIPT_BEYOND_COUNT1 ORA-6533 Reference a collection using an index number larger than the number of elements in the collection. SUBSCRIPT_OUTSIDE_LIMIT1 ORA-6532 Reference a collection using an illegal index number (a negative index number for example). TIMEOUT_ON_RESOURCE ORA-0051 Timeout occurred. TOO_MANY_ROWS ORA-1422 More than one row returned by a SELECT INTO statement. TRANSACTION_BACKED_OUT3 ORA-0061 Transaction rolled back because of deadlock. VALUE_ERROR ORA-6502 Arithmetic, conversion, truncation, or size-constraint error. ZERO_DIVIDE ORA-1476 Attempt to divide by zero. 1 Exception predefined in PL/SQL 3.0 and later. 2 Exception predefined in PL/SQL 2.2 and later. 3 Exception predefined only in PL/SQL 2.0 and 2.1. Steps
1. Run SQL*Plus and connect as the WAITE user account. The PL/SQL block in the CHP10_34.SQL file generates an exception intentionally by assigning a character value to a numeric variable.
If the PL/SQL block does not contain an exception section, the block terminates abnormally. An Oracle error and its description are displayed whenever an unhandled exception occurs, but here the exception is handled in the exception section of the block by catching it as a predefined exception. Figure 10.34 displays the code and results of the operation in SQL*Plus.
Lines 1 and 2 contain the declarative section of the block. Line 4 attempts to set the numeric variable defined in line 2 to a character value, which is an illegal operation. If there is no exception handler defined to handle the error, the program terminates abnormally. The exception section starting on line 6 is executed whenever an exception occurs. The VALUE_ERROR exception is handled in lines 7 and 8.
The Oracle error generated by line 4 is handled by the exception handler in lines 7 and 8. The only way for this code to execute is for the exception to occur. If a different exception occured, it would not be handled, and the execution of the block would still be terminated abnormally. This can be avoided by including a WHEN OTHERS exception handler as a catchall for all other Oracle errors.
2. Run the CHP10_35.SQL file in SQL*Plus. The PL/SQL block contained in the file creates an exception for an Oracle error not that is not predefined. The code and output are shown in Figure 10.35.
Lines 1 through 3 contain the declarative section for the block. Line 2 declares an exception to identify an Oracle error for which a predefined exception does not exist. The statement contained in line 4 is a compiler directive to associate the newly defined exception with an Oracle error. When the Oracle error defined in the EXCEPTION_INIT procedure is encountered, the exception associated with it is raised. Lines 6 through 9 attempt an illegal query of a ROWID from a view. The error encounter is the one defined by the BAD_ROWID exception. Lines 11 and 12 handle the exception by displaying a message within SQL*Plus. Lines 13 and 14 handle any other system error that might occur during execution. When the Oracle error ORA-01445 occurs in line 6 of the PL/SQL block, the user-defined exception handler processes the error and terminates the program normally.
How It Works
Errors are handled in PL/SQL blocks by exception handlers in the exception section. If a PL/SQL block does not handle exceptions when they occur, an error is generated and the program is terminated abnormally. Step 1 presents a PL/SQL block with an exception handler for the VALUE_ERROR exception. When this exception occurs, the exception handler causes the program to display a message before it terminates normally. Step 2 presents the technique for handling system errors that are not predefined. An exception is declared, and the PRAGMA EXCEPTION_INIT directive is used to associate an Oracle error with the exception. The WHEN OTHERS exception handler handles exceptions not dealt with by a specific error handler.
Comments
It is important to handle exceptions within your PL/SQL code. Exceptions can occur for a variety of reasons and can cause your code to terminate abnormally. You should attempt to handle exceptions specifically and not rely on the WHEN OTHERS exception to handle all your errors. The WHEN OTHERS exception handler must be placed last in the exception section. An exception raised in the declarative section because of an illegal assignment cannot be handled by a handler in the exception section and is propagated to the calling environment.