 |
|
Oracle Concepts by Burleson
Consulting |
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. There have been many cases where an improperly
configured PL/SQL program failed to abort when the Oracle database
returns a non-zero SQL code. This mistake causes the program to
think that it has successfully accessed the database, when in reality,
the operation never completed.
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 EXCEPTIONS area tests
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.
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
