Exceptions
Exceptions are used to handle errors that occur in your PL/SQL code. Earlier you saw an example PL/SQL block that contained an EXCEPTION block to handle attempts to divide a number by zero; that block handled the ZERO_DIVIDE exception. This exception and some of the other exceptions are shown in Table 11-1.
| Exception | Error | Description |
|---|---|---|
| ACCESS_INTO_NULL | ORA-06530 | Attempt was made to assign values to the attributes of an uninitialized object. You'll learn about objects in Chapter 12. |
| CASE_NOT_FOUND | ORA-06592 | None of the WHEN clauses of a CASE statement was selected, and there is no default ELSE clause. |
| COLLECTION_IS_NULL | ORA-06531 | You'll learn about collections in Chapter 13. Attempt was made to apply collection methods other than EXISTS to an uninitialized nested table or varray, or an attempt was made to assign values to the elements of an uninitialized nested table or varray. |
| CURSOR_ALREADY_OPEN | ORA-06511 | Attempt was made to open an already open cursor. Cursor must be closed before it can be reopened. |
| DUP_VAL_ON_INDEX | ORA-00001 | Attempt was made to store duplicate values in a column that is constrained by a unique index. |
| INVALID_CURSOR | ORA-01001 | Your program attempts an illegal cursor operation such as closing an unopened cursor. |
| INVALID_NUMBER | ORA-01722 | Attempt to convert a character string into a number failed because the string does not represent a valid number. Note: In procedural statements VALUE_ERROR is raised instead of INVALID_NUMBER . |
| LOGIN_DENIED | ORA-01017 | Attempt was made to connect to a database with an invalid user name or password. |
| NO_DATA_FOUND | ORA-01403 | SELECT INTO statement returns no rows, or an attempt was made to access a deleted element in a nested table or an uninitialized element in an index by table. |
| NOT_LOGGED_ON | ORA-01012 | Attempt was made to access a database item without first being connected to a database. |
| PROGRAM_ERROR | ORA-06501 | PL/SQL had an internal problem. |
| ROWTYPE_MISMATCH | ORA-06504 | Host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types. For example, when an open host cursor variable is passed to a stored procedure or function, the return types of the actual and formal parameters must be compatible. |
| SELF_IS_NULL | ORA-30625 | Attempt was made to call a MEMBER method on a null instance. That is, the built-in parameter SELF (which is always the first parameter passed to a MEMBER method) is null. |
| STORAGE_ERROR | ORA-06500 | PL/SQL ran out of memory or the memory has been corrupted. |
| SUBSCRIPT_BEYOND_COUNT | ORA-06533 | Attempt was made to reference a nested table or varray element using an index number larger than the number of elements in the collection. |
| SUBSCRIPT_OUTSIDE_LIMIT | ORA-06532 | Attempt was made to reference a nested table or varray element using an index number ( “1, for example) that is outside the legal range. |
| SYS_INVALID_ROWID | ORA-01410 | Conversion of a character string to a universal rowid fails because the character string does not represent a valid rowid. |
| TIMEOUT_ON_RESOURCE | ORA-00051 | A timeout occurred while the database was waiting for a resource. |
| TOO_MANY_ROWS | ORA-01422 | SELECT INTO statement returned more than one row. |
| VALUE_ERROR | ORA-06502 | An arithmetic, conversion, truncation , or size -constraint error occurred. For example, when selecting a column value into a character variable, if the value is longer than the declared length of the variable, PL/SQL aborts the assignment and raises VALUE_ERROR . In procedural statements VALUE_ERROR is raised if the conversion of a character string into a number fails. Note: In SQL statements INVALID_NUMBER is raised instead of VALUE_ERROR . |
| ZERO_DIVIDE | ORA-01476 | Attempt was made to divide a number by zero. |
The following sections show examples that raise some of the exceptions shown in Table 11-1.
ZERO_DIVIDE Exception
The ZERO_DIVIDE exception is raised when an attempt is made to divide a number by zero. The following example attempts to divide 1 by 0 and therefore causes the ZERO_DIVIDE exception to be raised:
BEGIN DBMS_OUTPUT.PUT_LINE(1 / 0); EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('Division by zero'); END; / Division by zero When the exception occurs, program control passes to the EXCEPTION block where the WHEN clause is examined for a matching exception. If no matching exception is found, the exception is propagated to the enclosing block. For example, if the EXCEPTION block was omitted from the previous example, the exception is propagated up and is sent to SQL*Plus:
BEGIN DBMS_OUTPUT.PUT_LINE(1 / 0); END; BEGIN * ERROR at line 1: ORA-01476: divisor is equal to zero ORA-06512: at line 2
DUP_VAL_ON_INDEX Exception
The DUP_VAL_ON_INDEX exception is raised when an attempt is made to store duplicate values in a column that is constrained by a unique index. The following example attempts to insert a row in the customers table with a customer_id of 1. This causes DUP_VAL_ON_INDEX to be raised because the customers table already contains a row with a customer_id of 1:
BEGIN INSERT INTO customers ( customer_id, first_name, last_name ) VALUES ( 1, 'Greg', 'Green' ); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('Duplicate value on an index'); END; / Duplicate value on an index INVALID_NUMBER Exception
The INVALID_NUMBER exception is raised when an attempt is made to convert an invalid character string into a number. The following example attempts to convert the string 123X to a number that is used in an insert, which causes INVALID_NUMBER to be raised because 123X is not a valid number:
BEGIN INSERT INTO customers ( customer_id, first_name, last_name ) VALUES ( '123X', 'Greg', 'Green' ); EXCEPTION WHEN INVALID_NUMBER THEN DBMS_OUTPUT.PUT_LINE('Conversion of string to number failed'); END; / Conversion of string to number failed OTHERS Exception
You can use the OTHERS exception to handle all exceptions. For example:
BEGIN DBMS_OUTPUT.PUT_LINE(1 / 0); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An exception occurred'); END; / An exception occurred Because OTHERS handles all exceptions, you must list it after any specific exceptions in your EXCEPTION block. If you attempt to list OTHERS elsewhere, the database returns the error PLS-00370. For example:
SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE(1 / 0); 3 EXCEPTION 4 WHEN OTHERS THEN 5 DBMS_OUTPUT.PUT_LINE('An exception occurred'); 6 WHEN ZERO_DIVIDE THEN 7 DBMS_OUTPUT.PUT_LINE('Division by zero'); 8 END; 9 / WHEN OTHERS THEN * ERROR at line 4: ORA-06550: line 4, column 3: PLS-00370: OTHERS handler must be last among the exception handlers of a block ORA-06550: line 0, column 0: PL/SQL: Compilation unit analysis terminated 
EAN: 2147483647
Pages: 217