[ Team LiB ] Previous Section Next Section

1.10 Exception Handling

PL/SQL allows developers to raise and handle errors (exceptions) in a very flexible and powerful way. Each PL/SQL block can have its own exception section in which exceptions can be trapped and handled (resolved or passed on to the enclosing block).

When an exception occurs (is raised) in a PL/SQL block, its execution section immediately terminates. Control is passed to the exception section.

Every exception in PL/SQL has an error number and error message; some exceptions also have names.

1.10.1 Declaring Exceptions

Some exceptions (see the following table) have been pre-defined by Oracle in the STANDARD package or other built-in packages, such as UTL_FILE. 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-00061

TRANSACTION_BACKED_OUT

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-01725

USERENV_COMMMITSCN_ERROR

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

ORA-09592

CASE_NOT_FOUND

ORA-30625

SELF_IS_NULL

ORA-29280

INVALID_PATH

ORA-29281

INVALID_MODE

ORA-29282

INVALID_FILEHANDLE

ORA-29283

INVALID_OPERATION

ORA-29284

READ_ERROR

ORA-29285

WRITE_ERROR

ORA-29286

INTERNAL_ERROR

ORA-29287

INVALID_MAXLINESIZE

ORA-29288

INVALID_FILENAME

ORA-29289

ACCESS_DENIED

ORA-29290

INVALID_OFFSET

ORA-29291

DELETE_FAILED

ORA-29292

RENAME_FAILED

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 using the following syntax: 

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 an exception that is declared in the STANDARD package. If you use the RAISE statement inside an exception handler, you can omit the exception name to re-raise the current exception: 

RAISE;

This syntax is not valid outside the exception section.

The RAISE_APPLICATION_ERROR built-in function 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, as shown here: 

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;
1.10.4.1 WHEN OTHERS clause

Use the WHEN OTHERS clause in the exception handler as a catch-all to trap any exceptions that are not handled by specific WHEN clauses in the exception section. If present, this clause must be the last exception handler in the exception section. You specify this clause as follows: 

EXCEPTION
   WHEN OTHERS
   THEN
      ...
1.10.4.2 SQLCODE and SQLERRM

SQLCODE and SQLERRM are built-in functions that provide the SQL error code and message for the current exception. Use these functions inside the exception section's WHEN OTHERS clause to handle specific errors by number. The EXCEPTION_INIT pragma allows you to handle errors by name. For example, the following code: 

CREATE TABLE err_test
   (widget_name   VARCHAR2(100)
   ,widget_count  NUMBER
   ,CONSTRAINT no_small_numbers CHECK 
      (widget_count > 1000));
BEGIN
   INSERT INTO err_test (widget_name, widget_count)
   VALUES ('Athena',2);
EXCEPTION
   WHEN OTHERS THEN
   IF SQLCODE = -2290
      AND SQLERRM LIKE '%NO_SMALL_NUMBERS%'
   THEN
      DBMS_OUTPUT.PUT_LINE('widget_count is too
         small');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Exception not handled,'
         ||'SQLcode='||SQLCODE);
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
   END IF;
END;

produces this output:

widget_count is too small

The built-in package DBMS_UTILITY's FORMAT_ERROR_STACK and FORMAT_CALL_STACK procedures can be used to capture the full error stack and call stack. See the book Oracle Built-in Packages for more information on DBMS_UTILITY.

1.10.4.3 Exceptions and DML

When an exception is raised in a PL/SQL block, it does not roll back your current transaction, even if the block itself issued an INSERT, UPDATE, or DELETE. You must issue your own ROLLBACK statement if you want to clean up your transaction as a result of the exception.

If your exception goes unhandled (propagates out of the outermost block), however, most host environments will then force an automatic, unqualified rollback of any outstanding changes in your session.

    [ Team LiB ] Previous Section Next Section