About Named System Exceptions

Oracle gives names to a relatively small number of system exceptions by including EXCEPTION_INIT pragma statements in built-in package specifications.

The most important and commonly used set of named exceptions may be found in the STANDARD package in PL/SQL. This package is one of the two default packages of PL/SQL (the other is DBMS_STANDARD). This means that you can reference these exceptions without including the package name as a prefix. So, for instance, if I want to raise the NO_DATA_FOUND exception in my code, I can do so with either of these statements:

WHEN NO_DATA_FOUND THENWHEN STANDARD.NO_DATA_FOUND THEN

You can find predefined exceptions in other built-in packages such as DBMS_LOB, the package used to manipulate large objects. Here is an example of one such definition in that package's specification:

invalid_argval EXCEPTION; PRAGMA EXCEPTION_INIT(invalid_argval, -21560);

Because DBMS_LOB is not a default package, when I reference this exception, I need to include the package name:

WHEN DBMS_LOB.invalid_argval THEN...

Many of the STANDARD-based predefined exceptions are listed in Chapter 6, each with its Oracle error number, the value returned by a call to SQLCODE, and a brief description. SQLCODE is a PL/SQL built-in function that returns the status code of the last-executed SQL or DML statement. SQLCODE returns zero if the last statement executed without errors. In all but one case (100, the ANSI standard error number for NO_DATA_FOUND), the SQLCODE value is the same as the Oracle error code.

Table 6-1. Predefined exceptions in PL/SQL
Name of exception Oracle error/SQLCODE	Description
CURSOR_ALREADY_OPENORA-6511 SQLCODE=-6511	You tried to OPEN a cursor that was already OPEN. You must CLOSE a cursor before you try to OPEN or re-OPEN it.
DUP_VAL_ON_INDEXORA-00001 SQLCODE= -1	Your INSERT or UPDATE statement attempted to store duplicate values in a column or columns in a row that is restricted by a unique index.
INVALID_CURSORORA-01001 SQLCODE=-1001	You made reference to a cursor that did not exist. This usually happens when you try to FETCH from a cursor or CLOSE a cursor before that cursor is OPENed.
INVALID_NUMBERORA-01722 SQLCODE =-1722	PL/SQL executes a SQL statement that cannot convert a character string successfully to a number. This exception is different from the VALUE_ERROR exception, as it is raised only from within a SQL statement.
LOGIN_DENIEDORA-01017 SQLCODE= -1017	Your program tried to log into the Oracle RDBMS with an invalid username-password combination. This exception is usually encountered when you embed PL/SQL in a 3GL language.
NO_DATA_FOUNDORA-01403 SQLCODE= +100	This exception is raised in three different scenarios: (1) You executed a SELECT INTO statement (implicit cursor) that returned no rows. (2) You referenced an uninitialized row in a local PL/SQL table. (3) You read past end-of-file with the UTL_FILE package.
NOT_LOGGED_ONORA-01012 SQLCODE= -1012	Your program tried to execute a call to the database (usually with a DML statement) before it had logged into the Oracle RDBMS.
PROGRAM_ERRORORA-06501 SQLCODE= -6501	PL/SQL encounters an internal problem. The message text usually also tells you to "Contact Oracle Support."
STORAGE_ERRORORA-06500 SQLCODE= -6500	Your program ran out of memory, or memory was in some way corrupted.
TIMEOUT_ON_RESOURCEORA-00051 SQLCODE=-51	A timeout occurred in the RDBMS while waiting for a resource.
TOO_MANY_ROWSORA-01422 SQLCODE= -1422	A SELECT INTO statement returned more than one row. A SELECT INTO can return only one row; if your SQL statement returns more than one row, you should place the SELECT statement in an explicit CURSOR declaration and FETCH from that cursor one row at a time.
TRANSACTION_BACKED_OUTORA-00061 SQLCODE= -61	The remote part of a transaction is rolled back, either with an explicit ROLLBACK command or as the result of some other action (such as a failed SQL/DML on the remote database).
VALUE_ERRORORA-06502 SQLCODE= -6502	PL/SQL encountered an error having to do with the conversion, truncation, or invalid constraining of numeric and character data. This is a very general and common exception. If this type of error is encountered in a SQL DML statement within a PL/SQL block, then the INVALID_NUMBER exception is raised.
ZERO_DIVIDEORA-01476 SQLCODE= -1476	Your program tried to divide by zero.

Here is an example of how you might use the exceptions table. Suppose that your program generates an unhandled exception for error ORA-6511. Looking up this error, you find that it is associated with the CURSOR_ALREADY_OPEN exception. Locate the PL/SQL block in which the error occurs and add an exception handler for CURSOR_ALREADY_OPEN, as shown here:

EXCEPTION WHEN CURSOR_ALREADY_OPEN THEN CLOSE my_cursor;END;

Of course, you would be even better off analyzing your code to determine proactively which of the predefined exceptions might occur. Then you could decide which of those exceptions you want to handle specifically, which should be covered by the WHEN OTHERS clause, and which would best be left unhandled.