EnterpriseDB: Named Exceptions
Oracle Tips by
exception is one of the pre-defined exceptions that come supplied in
EnterpriseDB. When referring to a named exception, you use a textual
name instead of an error code.
WHERE 1 = 2;
DBMS_OUTPUT.PUT_LINE( 'No Data Found');
No Data Found
8 contains a list of all named, Oracle compatible exceptions and the
associated code. Exceptions that you will use frequently, and that
are fairly self-explanatory are:
no_data_found – A select into statement returned no rows
too_many_rows – A select into statement returned more than one row
dup_val_on_index – An insert or update tried to create a non-unique
row on a column or key identified as unique (can be multi-column)
zero_divide – A divide by zero exception
cursor_already_open – An open cursor command was issued against an
already open cursor
invalid_cursor – A cursor operation was attempted against a cursor
that is not open
SQLCODE and SQLERRM
and SQLERRM are variables populated by the database. Upon an
exception condition, the database instantiates the two variables.
SQLCODE stores the database error code and SQLERRM stores a text
description of the error.
cannot manually assign values to either SQLCODE or SQLERRM. You can
use RAISE_APPLICATION_ERROR to assign values to these variables.
User Defined Exceptions
defined exceptions are defined though the built-in procedure,
RAISE_APPLICATION_ERROR. This procedure has the syntax:
RAISE_APPLICATION_ERROR(<numeric code>, <text
call raise_application_error, program control is passed to the nearest
exception handler. The numeric code can be any number but best
practice recommends that you use a negative number to distinguish
application codes from built-in codes.
Raise_application_error populates the SQLCODE with the numeric code
and the SQLERRM with the text message. You cannot test for user
defined exceptions by name. You test for them in the OTHERS branch
via the SQLCODE:
IF 1 != 2 THEN
RAISE_APPLICATION_ERROR(-20001, 'Numbers do not match');
IF SQLCODE = -20001
EDB--20001: Numbers do not match
Oracle's PL/SQL, if you want to re-raise the same exception, you can
use the RAISE keyword. When written by itself in an exception
handler, RAISE will re-raise the current exception. You would use a
RAISE if you need to process rollbacks, commits or other executable
but you want to propagate the exception to the calling routine.
RAISE keyword, to re-raise an exception, is not currently supported in
SPL. You may use raise to raise a named exception such as
no_data_found. The syntax is the same as PL/SQL, raise <exception
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.