1.10.1 Declaring Exceptions

Some exceptions (see the following table) have been pre-defined by Oracle in the STANDARD package. You can also declare your own exceptions as follows:

DECLARE
   exception_name EXCEPTION;

Error	Named Exception
ORA-00001	DUP_VAL_ON_INDEX
ORA-00051	TIMEOUT_ON_RESOURCE
ORA-01001	INVALID_CURSOR
ORA-01012	NOT_LOGGED_ON
ORA-01017	LOGIN_DENIED
ORA-01403	NO_DATA_FOUND
ORA-01410	SYS_INVALID_ROWID
ORA-01422	TOO_MANY_ROWS
ORA-01476	ZERO_DIVIDE
ORA-01722	INVALID_NUMBER
ORA-06500	STORAGE_ERROR
ORA-06501	PROGRAM_ERROR
ORA-06502	VALUE_ERROR
ORA-06504	ROWTYPE_MISMATCH
ORA-06511	CURSOR_ALREADY_OPEN
ORA-06530	ACCESS_INTO_NULL
ORA-06531	COLLECTION_IS_NULL
ORA-06532	SUBSCRIPT_OUTSIDE_LIMIT
ORA-06533	SUBSCRIPT_BEYOND_COUNT

An exception can be declared only once in a block, but nested blocks can declare an exception with the same name as an outer block. If this multiple declaration occurs, scope takes precedence over name when handling the exception. The inner block's declaration takes precedence over a global declaration.

When you declare your own exception, you must RAISE it explicitly. All declared exceptions have an error code of 1 and the error message "User-defined exception," unless you use the EXCEPTION_INIT pragma.

You can associate an error number with a declared exception with the PRAGMA EXCEPTION_INIT statement:

DECLARE
   exception_name EXCEPTION;
   PRAGMA EXCEPTION_INIT (exception_name, 
      error_number);

where error_number is a literal value (variable references are not allowed). This number can be an Oracle error, such as -1855, or an error in the user-definable -20000 to -20999 range.

1.10.2 Raising Exceptions

An exception can be raised in three ways:

• By the PL/SQL runtime engine

• By an explicit RAISE statement in your code

• By a call to the built-in function RAISE_APPLICATION_ERROR

The syntax for the RAISE statement is:

RAISE exception_name;

where exception_name is the name of an exception that you have declared, or that is declared in the STANDARD package.

If you use the RAISE statement inside an exception handler, you can leave off an exception name to re-raise the current exception:

RAISE;

This syntax is not valid outside the exception section.

The RAISE_APPLICATION_ERROR built-in has the following header:

RAISE_APPLICATION_ERROR (
   num BINARY_INTEGER,
   msg VARCHAR2,
   keeperrorstack BOOLEAN DEFAULT FALSE);

where num is the error number (an integer between -20999 and -20000), msg is the associated error message, and keeperrorstack controls the contents of the error stack.

1.10.3 Scope

The scope of an exception section is that portion of the code that is "covered" by the exception section. An exception handler will only handle or attempt to handle exceptions raised in the executable section of the PL/SQL block. Exceptions raised in the declaration or exception sections are automatically passed to the outer block. Any line or set of PL/SQL code can be placed inside its own block and given its own exception section. This allows you to limit the propagation of an exception.

1.10.4 Propagation

Exceptions raised in a PL/SQL block propagate to an outer block if they are unhandled or re-raised in the exception section. When an exception occurs, PL/SQL looks for an exception handler that checks for the exception (or is the WHEN OTHERS clause) in the current block. If a match is not found, then PL/SQL propagates the exception to the enclosing block or calling program. This propagation continues until the exception is handled or propagated out of the outermost block, back to the calling program. In this case, the exception is "unhandled" and (1) stops the calling program, and (2) causes an automatic rollback of any outstanding transactions.

Once an exception is handled, it will not propagate upward. If you want to trap an exception, display a meaningful error message, and have the exception propagate upward as an error, you must re-raise the exception. The RAISE statement can re-raise the current exception or raise a new exception:

PROCEDURE delete_dept(deptno_in IN NUMBER)
DECLARE
   still_have_employees EXCEPTION
   PRAGMA EXCEPTION_INIT(still_have_employees. 
      -2292)
BEGIN
DELETE FROM dept
WHERE deptno = deptno_in;
EXCEPTION
   WHEN still_have_employees
   THEN  
      DBMS_OUTPUT.PUT_LINE
('Please delete employees in dept first');
ROLLBACK;
RAISE;  /* Re-raise the current exception. */
END;