Exception handling in PL/SQL
enables you to keep a procedure
running when a minor error
occurs, which would normally stop the entire procedure. Examples of this are when you try to retrieve data
with an SQL
query, but there are no records matching the criteria. In that case, you might want to insert
a record, or assign a default value to a variable
Exceptions are defined within a subprogram. The normal format of a PL/SQL program is this:
WHEN exception name THEN
You can use the predefined exceptions, or create your own. If you create your own exceptions, you must declare them in the DECLARE
part of the program. When you want that exception to occur, you must raise it yourself, with the RAISE
IF amount = 0 THEN
WHEN my_own_fault THEN
WHEN no_data_found THEN
my_number := 0;
There are a number of predefined exceptions which you can use:
CURSOR_ALREADY_OPEN (error ORA-06511).
This error occurs when a cursor is opened that is already open. It can easily be prevented by checking the state of the cursor before opening it.
DUP_VAL_ON_INDEX (error ORA-00001).
This error occurs when you try to insert a record, but the unique key already exists in the table.
INVALID_CURSOR (error ORA-01001).
Occurs when you try to do something with a cursor that isn't possible.
INVALID_NUMBER (error ORA-01722).
This error occurs when you try to convert data to a number when it isn't a number.
LOGIN_DENIED (error ORA-01017).
When you try to log in with the wrong username or password, this error occurs.
NO_DATA_FOUND (error ORA-01403).
This error occurs when you try to retrieve data from a table, but no records match the criteria.
NOT_LOGGED_IN (error ORA-01012).
This error occurs when you try to access the database when you're not logged in.
PROGRAM_ERROR (error ORA-06501).
This error occurs when an internal error occurs.
STORAGE_ERROR (error ORA-06500).
Occurs when there isn't any memory left to execute the program.
TIMEOUT_ON_RESOURCE (error ORA-00051).
This error pops up when a time-out occurs with the Oracle database.
TOO_MANY_ROWS (error ORA-01422).
This error occurs when you retrieve more then one row from the table, when you want to insert only one row into a variable.
TRANSACTION_BACKED_OUT (error ORA-00061).
Occurs when a rollback occurs as result of a deadlock.
VALUE_ERROR (error ORA-06502).
This error occurs when a conversion, mathematical or constraint error occurs.
ZERO_DIVIDE (error ORA-01476).
This error occurs when the program tries to divide a number by zero.