 |
|
EnterpriseDB: Named Exceptions
Oracle Tips by
Burleson
|
A named
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.
DECLARE
v_number NUMBER;
BEGIN
SELECT 1
INTO v_number
FROM dual
WHERE 1 = 2;
EXCEPTION
WHEN no_data_found
THEN
DBMS_OUTPUT.PUT_LINE( 'No Data Found');
END;
INFO:
No Data Found
Chapter
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
SQLCODE
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.
You
cannot manually assign values to either SQLCODE or SQLERRM. You can
use RAISE_APPLICATION_ERROR to assign values to these variables.
User Defined Exceptions
User
defined exceptions are defined though the built-in procedure,
RAISE_APPLICATION_ERROR. This procedure has the syntax:
RAISE_APPLICATION_ERROR(<numeric code>, <text
message>);
When you
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:
DECLARE
v_number NUMBER;
BEGIN
IF 1 != 2 THEN
RAISE_APPLICATION_ERROR(-20001, 'Numbers do not match');
END IF;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -20001
THEN
DBMS_OUTPUT.PUT_LINE( SQLERRM);
END IF;
END;
INFO:
EDB--20001: Numbers do not match
RAISE
In
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.
The
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
name>.
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress. |
