Exception Handling in PL/SQL
In any procedural language, it is critical to remember that the programs are a complete and separate entity from the database. Hence, whenever the program requests rows from the database, the programmer must make sure that the request completed successfully.
In relational databases, the database will always pass a special variable called SQLCODE back to any calling program. The SQLCODE returned from reach call from the program to the database is translated by Oracle into a named Boolean variable (See table below).
PL/SQL Exception Variable
|
Oracle Error
|
SQLCODE Value
|
ACCESS_INTO_NULL
|
ORA-06530
|
-6530
|
CASE_NOT_FOUND
|
ORA-06592
|
-6592
|
COLLECTION_IS_NULL
|
ORA-06531
|
-6531
|
CURSOR_ALREADY_OPEN
|
ORA-06511
|
-6511
|
DUP_VAL_ON_INDEX
|
ORA-00001
|
-1
|
INVALID_CURSOR
|
ORA-01001
|
-1001
|
INVALID_NUMBER
|
ORA-01722
|
-1722
|
LOGIN_DENIED
|
ORA-01017
|
-1017
|
NO_DATA_FOUND
|
ORA-01403
|
+100
|
NOT_LOGGED_ON
|
ORA-01012
|
-1012
|
PROGRAM_ERROR
|
ORA-06501
|
-6501
|
ROWTYPE_MISMATCH
|
ORA-06504
|
-6504
|
SELF_IS_NULL
|
ORA-30625
|
-30625
|
STORAGE_ERROR
|
ORA-06500
|
-6500
|
SUBSCRIPT_BEYOND_COUNT
|
ORA-06533
|
-6533
|
SUBSCRIPT_OUTSIDE_LIMIT
|
ORA-06532
|
-6532
|
SYS_INVALID_ROWID
|
ORA-01410
|
-1410
|
TIMEOUT_ON_RESOURCE
|
ORA-00051
|
-51
|
TOO_MANY_ROWS
|
ORA-01422
|
-1422
|
VALUE_ERROR
|
ORA-06502
|
-6502
|
ZERO_DIVIDE
|
ORA-01476
|
-1476
|
For example, if the database returns a SQLCODE=100, the PL/SQL variable NO_DATA_FOUND will be set to TRUE.
Without exception, all PL/SQL programs should be made to abort whenever an unexpected SQLCODE is returned by the Oracle database.
This can have a disastrous effect on the database, especially when the PL/SQL loads data into tables based upon false premises. To prevent this tragedy, Oracle provides a WHEN OTHERS variable, which is set to TRUE if any unexpected SQLCODE is returned from the Oracle database.
For example, consider the following code:
DECLARE
err_num NUMBER;
err_msg VARCHAR2(100);
BEGIN
...
EXCEPTION
...
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
INSERT INTO errors VALUES (err_num, err_msg);
END;
Here we see that our exception handling has an EXCEPTIONS area testing WHEN OTHERS. If the WHEN OTHERS Boolean variable is TRUE, the PL/SQL code captures the SQLCODE and the associated error message (SQLERRM), and stores these values into a special Oracle errors table.
