 |
|
Oracle Unexpected Errors
Oracle Tips by
Burleson
|
If an error occurs during the execution of an object, Oracle raises
an exception and generates the most appropriate error text.
If the error message corresponds to one of the predefined
exceptions that PL/SQL uses, the error can be handled using an
exception handler for the predefined exception. Table 4.2 lists these
predefined exceptions and their associated Oracle error message
numbers.
|
Table 4.2 Predefined exceptions in PL/SQL. |
|
Exception |
Associated Oracle Error |
|
CURSOR_ALREADY_OPEN |
ORA-06511 |
|
DUP_VAL_ON_INDEX |
ORA-00001 |
|
INVALID_CURSOR |
ORA-01001 |
|
INVALID_NUMBER |
ORA-01722 |
|
LOGIN_DENIED |
ORA-01017 |
|
NO_DATA_FOUND |
ORA-01403 |
|
NOT_LOGGED_ON |
ORA-01012 |
|
PROGRAM_ERROR |
ORA-06501 |
|
STORAGE_ERROR |
ORA-06500 |
|
TIMEOUT_ON_RESOURCE |
ORA-00051 |
|
TOO_MANY_ROWS |
ORA-01422 |
|
TRANSACTION_BACKED_OUT |
ORA-00061 |
|
VALUE_ERROR |
ORA-06502 |
|
ZERO_DIVIDE |
ORA-01476 |
You can re-declare these predefined exceptions and create custom
handlers for your new exceptions, but Oracle will not recognize your
new exception when it attempts to raise one of the predefined
exceptions. Your best bet is to leave the predefined exceptions alone
and create your own user-defined exceptions.
While the predefined exceptions are quite useful and deal with the
most commonly encountered error conditions quite well, it’s not
uncommon for other errors to be encountered as well. To allow you to
handle specified errors that don’t have a predefined exception, Oracle
has provided the OTHERS exception handler.
Listing 4.21 illustrates the use of an OTHERS exception
handler.
Listing 4.21 Using the OTHERS exception handler.
BEGIN
<statements>
EXCEPTION
WHEN OTHERS THEN
<error handling code>
END;
The code that follows the OTHERS exception handler is
written to handle errors that you don’t expect. Often, this code is a
call to another procedure that logs a message to a table containing
specific information about the error (the text of the Oracle error,
the parameters of the procedure or function in which the error
occurred, and any other relevant information).
You can also use the OTHERS exception handler in conjunction
with the SQLERRM() and SQLCODE() functions that PL/SQL
provides, as shown in Listing 4.22.
Listing 4.22 Using SQLCODE() and SQLERRM() in
an OTHERS exception handler.
BEGIN
<statements>
EXCEPTION
WHEN OTHERS THEN
IF (SQLCODE = -942) THEN
DBMS_Output.Put_Line (SQLERRM);
ELSE
RAISE;
END IF;
END;
This example calls the SQLCODE() function to determine what
error is occurring and SQLERRM() to record the text of the
error message.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
