An internal exception is raised implicitly whenever your PL/SQL
program violates an Oracle rule or exceeds a system-dependent limit. Every
Oracle error has a number, but exceptions must be handled by name. So, PL/SQL
predefines some common Oracle errors as exceptions. For example, PL/SQL raises
the predefined exception NO_DATA_FOUND if a SELECT INTO
statement returns no rows. To handle other Oracle errors, you can use the OTHERS
handler. The functions SQLCODE and SQLERRM are
especially useful in the OTHERS handler because they return the
Oracle error code and message text.
When an exception is raised, normal execution of your PL/SQL block or subprogram stops and control transfers to its exception-handling part, which is formatted as follows:
EXCEPTION
WHEN exception_name1 THEN -- handler
sequence_of_statements1
WHEN exception_name2 THEN -- another handler
sequence_of_statements2
...
WHEN OTHERS THEN -- optional handler
sequence_of_statements3
END;
To catch raised exceptions, you write exception handlers. Each
handler consists of a WHEN clause, which specifies an exception,
followed by a sequence of statements to be executed when that exception is
raised. These statements complete execution of the block or subprogram; control
does not return to where the exception was raised. In other words, you cannot
resume processing where you left off. The optional OTHERS exception
handler, which is always the last handler in a block or subprogram, acts as the
handler for all exceptions not named specifically. Thus, a block or subprogram
can have only one OTHERS handler. In an exception handler, you can
use the built-in functions SQLCODE and SQLERRM to find
out which error occurred and to get the associated error message. For internal
exceptions, SQLCODE returns the number of the Oracle error. The
number that SQLCODE returns is negative unless the Oracle error is no
data found, in which case SQLCODE returns +100. SQLERRM
returns the corresponding error message. The message begins with the Oracle
error code. For user-defined exceptions, SQLCODE returns +1 and SQLERRM
returns the message User-Defined Exception. If no exception has been
raised, SQLCODE returns zero and SQLERRM returns the
message: ORA-0000: normal, successful completion
You can pass an error number to SQLERRM, in which
case SQLERRM returns the message associated with that error number.
Make sure you pass negative error numbers to SQLERRM. In the
following example, you pass positive numbers and so get unwanted results:
DECLARE
...
err_msg VARCHAR2(100);
BEGIN
/* Get all Oracle error messages. */
FOR err_num IN 1..9999 LOOP
err_msg := SQLERRM(err_num); -- wrong; should be -err_num
INSERT INTO errors VALUES (err_msg);
END LOOP;
END;
Passing a positive number to SQLERRM always returns
the message user-defined exception unless you pass +100,
in which case SQLERRM returns the message no
data found. Passing a zero to SQLERRM always returns the
message normal, successful completion. You cannot use SQLCODE
or SQLERRM directly in a SQL statement. Instead, you must assign
their values to local variables, then use the variables in the SQL statement, as
shown in the following example:
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;
The string function SUBSTR ensures that a VALUE_ERROR
exception (for truncation) is not raised when you assign the value of SQLERRM
to err_msg. The functions SQLCODE and SQLERRM
are especially useful in the OTHERS exception handler because they
tell you which internal exception was raised.
The following lists predefined exceptions:
Predefined exceptions can be raised either implicitly or explicitly. Brief descriptions of the predefined exceptions follow:
PL/SQL lets you define exceptions of your own. Unlike predefined
exceptions, user-defined exceptions must be declared and must be raised
explicitly by RAISE statements. Exceptions can be declared only in
the declarative part of a PL/SQL block, subprogram, or package. You declare an
exception by introducing its name, followed by the keyword EXCEPTION.
In the following example, you declare an exception named past_due:
DECLARE past_due EXCEPTION;
You cannot declare an exception twice in the same block. You can, however, declare the same exception in two different blocks. Exceptions declared in a block are considered local to that block and global to all its sub-blocks. Because a block can reference only local or global exceptions, enclosing blocks cannot reference exceptions declared in a sub-block. If you redeclare a global exception in a sub-block, the local declaration prevails. So, the sub-block cannot reference the global exception unless it was declared in a labeled block, in which case the following syntax is valid:
block_label.exception_name
The following example illustrates the scope rules:
DECLARE
past_due EXCEPTION;
acct_num NUMBER;
BEGIN
DECLARE ---------- sub-block begins
past_due EXCEPTION; -- this declaration prevails
acct_num NUMBER;
BEGIN
...
IF ... THEN
RAISE past_due; -- this is not handled
END IF;
END; ------------- sub-block ends
EXCEPTION
WHEN past_due THEN -- does not handle RAISEd exception
...
END;
The enclosing block does not handle the raised exception because
the declaration of past_due in the sub-block prevails. Though they
share the same name, the two past_due exceptions are different,
just as the two acct_num variables share the same name but are
different variables. Therefore, the RAISE statement and the WHEN
clause refer to different exceptions. To have the enclosing block handle the
raised exception, you must remove its declaration from the sub-block or define
an OTHERS handler.
Back to CS643 schedule