PL/SQL User's Guide and Reference Release 8.0 A58236-01 |
Library | Product | Contents | Index

# 6
Error Handling

There is nothing more exhilarating than to be shot at without result.

Winston Churchill

Runtime errors arise from design faults, coding mistakes, hardware failures, and many other sources. Although you cannot anticipate all possible errors, you can plan to handle certain kinds of errors meaningful to your PL/SQL program.

With many programming languages, unless you disable error checking, a runtime error such as stack overflow or division by zero stops normal processing and returns control to the operating system. With PL/SQL, a mechanism called exception handling lets you "bulletproof" your program so that it can continue operating in the presence of errors.

##### Major Topics

Overview
Advantages of Exceptions
Predefined Exceptions
User-Defined Exceptions
How Exceptions Are Raised
How Exceptions Propagate
Reraising an Exception
Handling Raised Exceptions
Useful Techniques

## Overview

In PL/SQL, a warning or error condition is called an exception. Exceptions can be internally defined (by the runtime system) or user defined. Examples of internally defined exceptions include division by zero and out of memory. Some common internal exceptions have predefined names, such as `ZERO_DIVIDE` and `STORAGE_ERROR`. The other internal exceptions can be given names.

You can define exceptions of your own in the declarative part of any PL/SQL block, subprogram, or package. For example, you might define an exception named `insufficient_funds` to flag overdrawn bank accounts. Unlike internal exceptions, user-defined exceptions must be given names.

When an error occurs, an exception is raised. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram. Internal exceptions are raised implicitly (automatically) by the runtime system. User-defined exceptions must be raised explicitly by `RAISE` statements, which can also raise predefined exceptions.

To handle raised exceptions, you write separate routines called exception handlers. After an exception handler runs, the current block stops executing and the enclosing block resumes with the next statement. If there is no enclosing block, control returns to the host environment.

In the example below, you calculate and store a price-to-earnings ratio for a company with ticker symbol XYZ. If the company has zero earnings, the predefined exception `ZERO_DIVIDE` is raised. This stops normal execution of the block and transfers control to the exception handlers. The optional `OTHERS` handler catches all exceptions that the block does not name specifically.

    DECLARE
       pe_ratio NUMBER(3,1);
    BEGIN
       SELECT price / earnings INTO pe_ratio FROM stocks
          WHERE symbol = 'XYZ';  -- might cause division-by-zero error     INSERT
    INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio);
       COMMIT;
    EXCEPTION  -- exception handlers begin
       WHEN ZERO_DIVIDE THEN  -- handles 'division by zero' error
          INSERT INTO stats (symbol, ratio) VALUES ('XYZ', NULL);
          COMMIT;
       ...
       WHEN OTHERS THEN  -- handles all other errors
          ROLLBACK;
    END;   -- exception handlers and block end here

The last example illustrates exception handling, not the effective use of `INSERT` statements. For example, a better way to do the insert follows:

    INSERT INTO stats (symbol, ratio)
       SELECT symbol, DECODE(earnings, 0, NULL, price / earnings)
       FROM stocks WHERE symbol = 'XYZ';

In this example, a subquery supplies values to the `INSERT` statement. If earnings are zero, the function `DECODE` returns a null. Otherwise, `DECODE` returns the price-to-earnings ratio.

## Advantages of Exceptions

Using exceptions for error handling has several advantages. Without exception handling, every time you issue a command, you must check for execution errors, as follows:

    BEGIN
       SELECT ...
          -- check for 'no data found' error
       SELECT ...
          -- check for 'no data found' error
       SELECT ...
          -- check for 'no data found' error

Error processing is not clearly separated from normal processing; nor is it robust. If you neglect to code a check, the error goes undetected and is likely to cause other, seemingly unrelated errors.

With exceptions, you can handle errors conveniently without the need to code multiple checks, as follows:

    BEGIN
       SELECT ...
       SELECT ...
       SELECT ...
       ...
    EXCEPTION
       WHEN NO_DATA_FOUND THEN  -- catches all 'no data found' errors

Exceptions improve readability by letting you isolate error-handling routines. The primary algorithm is not obscured by error recovery algorithms. Exceptions also improve reliability. You need not worry about checking for an error at every point it might occur. Just add an exception handler to your PL/SQL block. If the exception is ever raised in that block (or any sub-block), you can be sure it will be handled.

## Predefined Exceptions

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 error-reporting functions `SQLCODE` and `SQLERRM` are especially useful in the `OTHERS` handler because they return the Oracle error code and message text. Alternatively, you can use the pragma `EXCEPTION_INIT` to associate exception names with Oracle error numbers. (See "Using EXCEPTION_INIT".)

PL/SQL declares predefined exceptions globally in package `STANDARD`, which defines the PL/SQL environment. So, you need not declare them yourself. You can write handlers for predefined exceptions using the names shown in the list below. Also shown are the corresponding Oracle error codes and `SQLCODE` return values.

Exception | Oracle Error | SQLCODE Value
--- | --- | ---
    ACCESS_INTO_NULL
| ORA-06530 | -6530
    COLLECTION_IS_NULL
| ORA-06531 | -6531
    CURSOR_ALREADY_OPEN
| ORA-06511 | -6511
    DUP_VAL_ON_INDEX
| ORA-00001 | -1
    INVALID_CURSOR
| ORA-01001 | -1001
    INVALID_NUMBER
| ORA-01722 | -1722
    LOGIN_DENIED
| ORA-01017 | -1017
    NO_DATA-FOUND
| ORA-01403 | +100
    NOT_LOGGED_ON
| ORA-01012 | -1012
    PROGRAM_ERROR
| ORA-06501 | -6501
    ROWTYPE_MISMATCH
| ORA-06504 | -6504
    STORAGE_ERROR
| ORA-06500 | -6500
    SUBSCRIPT_BEYOND_COUNT
| ORA-06533 | -6533
    SUBSCRIPT_OUTSIDE_LIMIT
| ORA-06532 | -6532
    TIMEOUT_ON_RESOURCE
| ORA-00051 | -51
    TOO_MANY_ROWS
| ORA-01422 | -1422
    VALUE_ERROR
| ORA-06502 | -6502
    ZERO_DIVIDE
| ORA-01476 | -1476

Brief descriptions of the predefined exceptions follow:

Exception | Raised when ...
--- | ---
    ACCESS_INTO_NULL
| you try to assign values to the attributes of an uninitialized (atomically null) object.
    COLLECTION_IS_NULL
| you try to apply collection methods other than `EXISTS` to an uninitialized (atomically null) nested table or varray, or you try to assign values to the elements of an uninitialized nested table or varray.
    CURSOR_ALREADY_OPEN
| you try to open an already open cursor. You must close a cursor before you can reopen it. A cursor `FOR` loop automatically opens the cursor to which it refers. So, you cannot open that cursor inside the loop.
    DUP_VAL_ON_INDEX
| you try to store duplicate values in a database column that is constrained by a unique index.
    INVALID_CURSOR
| you try an illegal cursor operation such as closing an unopened cursor.
    INVALID_NUMBER
| in a SQL statement, the conversion of character string to a number fails because the character string does not represent a valid number. In procedural statements, `VALUE_ERROR` is raised.
    LOGIN_DENIED
| you try logging on to Oracle with an invalid username and/or password.
    NO_DATA_FOUND
| a `SELECT` `INTO` statement returns no rows, or you reference a deleted element in a nested table, or you reference an uninitialized element in an index-by table. The `FETCH` statement is expected to return no rows eventually, so when that happens, no exception is raised. SQL group functions such as `AVG` and `SUM` always return a value or a null. So, a `SELECT` `INTO` statement that calls a group function will never raise `NO_DATA_FOUND`.
    NOT_LOGGED_ON
| your PL/SQL program issues a database call without being connected to Oracle.
    PROGRAM_ERROR
| PL/SQL has an internal problem.
    ROWTYPE_MISMATCH
| the host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types. For example, when you pass an open host cursor variable to a stored subprogram, the return types of the actual and formal parameters must be compatible.
    STORAGE_ERROR
| PL/SQL runs out of memory or memory is corrupted.
    SUBSCRIPT_BEYOND_COUNT
| you reference a nested table or varray element using an index number larger than the number of elements in the collection.
    SUBSCRIPT_OUTSIDE_LIMIT
| you reference a nested table or varray element using an index number that is outside the legal range (-1 for example).
    TIMEOUT_ON_RESOURCE
| a time-out occurs while Oracle is waiting for a resource.
    TOO_MANY_ROWS
| a `SELECT` `INTO` statement returns more than one row.
    VALUE_ERROR
| an arithmetic, conversion, truncation, or size-constraint error occurs. For example, when you select a column value into a character variable, if the value is longer than the declared length of the variable, PL/SQL aborts the assignment and raises `VALUE_ERROR`. In procedural statements, `VALUE_ERROR` is raised if the conversion of a character string to a number fails. In SQL statements, `INVALID_NUMBER` is raised.
    ZERO_DIVIDE
| you try to divide a number by zero.

## User-Defined Exceptions

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.

### Declaring Exceptions

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;

Exception and variable declarations are similar. But remember, an exception is an error condition, not a data item. Unlike variables, exceptions cannot appear in assignment statements or SQL statements. However, the same scope rules apply to variables and exceptions.

### Scope Rules

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 next 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.

### Using EXCEPTION_INIT

To handle unnamed internal exceptions, you must use the `OTHERS` handler or the pragma `EXCEPTION_INIT`. A pragma is a compiler directive, which can be thought of as a parenthetical remark to the compiler. Pragmas (also called pseudoinstructions) are processed at compile time, not at run time. For example, in the language Ada, the following pragma tells the compiler to optimize the use of storage space:

    pragma OPTIMIZE(SPACE);

In PL/SQL, the pragma `EXCEPTION_INIT` tells the compiler to associate an exception name with an Oracle error number. That allows you to refer to any internal exception by name and to write a specific handler for it.

You code the pragma `EXCEPTION_INIT` in the declarative part of a PL/SQL block, subprogram, or package using the syntax

    PRAGMA EXCEPTION_INIT(exception_name, Oracle_error_number);

where `exception_name` is the name of a previously declared exception. The pragma must appear somewhere after the exception declaration in the same declarative part, as shown in the following example:

    DECLARE
       deadlock_detected EXCEPTION;
       PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
    BEGIN
       ...
    EXCEPTION
       WHEN deadlock_detected THEN
          -- handle the error
       ...
    END;

### Using raise_application_error

Package `DBMS_STANDARD`, which is supplied with Oracle, provides language facilities that help your application interact with Oracle. For example, the procedure `raise_application_error` lets you issue user-defined error messages from stored subprograms. That way, you can report errors to your application and avoid returning unhandled exceptions.

To call `raise_application_error`, you use the syntax

    raise_application_error(error_number, message[, {TRUE | FALSE}]);

where `error_number` is a negative integer in the range -20000 .. -20999 and `message` is a character string up to 2048 bytes long. If the optional third parameter is TRUE, the error is placed on the stack of previous errors. If the parameter is `FALSE` (the default), the error replaces all previous errors. Package `DBMS_STANDARD` is an extension of package `STANDARD`, so you need not qualify references to it.

An application can call `raise_application_error` only from an executing stored subprogram. When called, `raise_application_error` ends the subprogram and returns a user-defined error number and message to the application. The error number and message can be trapped like any Oracle error.

In the following example, you call `raise_application_error` if an employee's salary is missing:

    CREATE PROCEDURE raise_salary (emp_id NUMBER, increase NUMBER) AS
       current_salary NUMBER;
    BEGIN
       SELECT sal INTO current_salary FROM emp
          WHERE empno = emp_id;
       IF current_salary IS NULL THEN
          /* Issue user-defined error message. */
          raise_application_error(-20101, 'Salary is missing');
       ELSE
          UPDATE emp SET sal = current_salary + increase
             WHERE empno = emp_id;
       END IF;
    END raise_salary;

The calling application gets a PL/SQL exception, which it can process using the error-reporting functions `SQLCODE` and `SQLERRM` in an `OTHERS` handler. Also, it can use the pragma `EXCEPTION_INIT` to map specific error numbers returned by `raise_application_error` to exceptions of its own, as follows:

    EXEC SQL EXECUTE
       DECLARE
          ...
          null_salary EXCEPTION;
          /* Map error number returned by raise_application_error
             to user-defined exception. */
          PRAGMA EXCEPTION_INIT(null_salary, -20101);
       BEGIN
          ...
          raise_salary(:emp_number, :amount);
       EXCEPTION
          WHEN null_salary THEN
             INSERT INTO emp_audit VALUES (:emp_number, ...);
          ...
       END;
    END-EXEC;

This technique allows the calling application to handle error conditions in specific exception handlers.

### Redeclaring Predefined Exceptions

Remember, PL/SQL declares predefined exceptions globally in package `STANDARD`, so you need not declare them yourself. Redeclaring predefined exceptions is error prone because your local declaration overrides the global declaration.

    EXCEPTION
       WHEN invalid_number OR STANDARD.INVALID_NUMBER THEN
          -- handle the error
       ...
       WHEN OTHERS THEN ...
    END;

## How Exceptions Are Raised

Internal exceptions are raised implicitly by the runtime system, as are user-defined exceptions that you have associated with an Oracle error number using `EXCEPTION_INIT`. However, other user-defined exceptions must be raised explicitly by `RAISE` statements.

### Using the RAISE Statement

PL/SQL blocks and subprograms should raise an exception only when an error makes it undesirable or impossible to finish processing. You can place `RAISE` statements for a given exception anywhere within the scope of that exception. In the following example, you alert your PL/SQL block to a user-defined exception named `out_of_stock`:

    DECLARE
       out_of_stock   EXCEPTION;
       number_on_hand NUMBER(4);
    BEGIN
       ...
       IF number_on_hand < 1 THEN
          RAISE out_of_stock;
       END IF;
       ...
    EXCEPTION
       WHEN out_of_stock THEN
          -- handle the error
    END;

You can also raise a predefined exception explicitly. That way, an exception handler written for the predefined exception can process other errors, as the following example shows:

    DECLARE
       acct_type INTEGER;
       ...
    BEGIN
       ...
       IF acct_type NOT IN (1, 2, 3) THEN
          RAISE INVALID_NUMBER;  -- raise predefined exception
       END IF;
       ...
    EXCEPTION
       WHEN INVALID_NUMBER THEN
          ROLLBACK;
       ...
    END;

## How Exceptions Propagate

When an exception is raised, if PL/SQL cannot find a handler for it in the current block or subprogram, the exception propagates. That is, the exception reproduces itself in successive enclosing blocks until a handler is found or there are no more blocks to search. In the latter case, PL/SQL returns an unhandled exception error to the host environment.

However, exceptions cannot propagate across remote procedure calls (RPCs). Therefore, a PL/SQL block cannot catch an exception raised by a remote subprogram. For a workaround, see "Using raise_application_error".

Figure 6-1, Figure 6-2, and Figure 6-3 illustrate the basic propagation rules.

#### Figure 6-1 Propagation Rules: Example 1

#### Figure 6-2 Propagation Rules: Example 2

#### Figure 6-3 Propagation Rules: Example 3

An exception can propagate beyond its scope, that is, beyond the block in which it was declared. Consider the following example:

    BEGIN
       ...
       DECLARE  ---------- sub-block begins
          past_due EXCEPTION;
       BEGIN
          ...
          IF ... THEN
             RAISE past_due;
          END IF;
       END;  ------------- sub-block ends
    EXCEPTION
       ...
       WHEN OTHERS THEN
          ROLLBACK;
    END;

Because the block in which it was declared has no handler for the exception named `past_due`, it propagates to the enclosing block. But, according to the scope rules, enclosing blocks cannot reference exceptions declared in a sub-block. So, only an `OTHERS` handler can catch the exception.

## Reraising an Exception

Sometimes, you want to reraise an exception, that is, handle it locally, then pass it to an enclosing block. For example, you might want to roll back a transaction in the current block, then log the error in an enclosing block.

To reraise an exception, simply place a `RAISE` statement in the local handler, as shown in the following example:

    DECLARE
       out_of_balance  EXCEPTION;
    BEGIN
       ...
       BEGIN  ---------- sub-block begins
          ...
          IF ... THEN
             RAISE out_of_balance;  -- raise the exception
          END IF;
       EXCEPTION
          WHEN out_of_balance THEN
             -- handle the error
             RAISE;  -- reraise the current exception
          ...
       END;  ------------ sub-block ends
    EXCEPTION
       WHEN out_of_balance THEN
          -- handle the error differently
       ...
    END;

Omitting the exception name in a `RAISE` statement-allowed only in an exception handler-reraises the current exception.

## Handling Raised Exceptions

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

To catch raised exceptions, you must 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.

As the following example shows, use of the `OTHERS` handler guarantees that no exception will go unhandled:

    EXCEPTION
       WHEN ... THEN
          -- handle the error
       WHEN ... THEN
          -- handle the error
       WHEN OTHERS THEN
          -- handle all other errors
    END;

If you want two or more exceptions to execute the same sequence of statements, list the exception names in the `WHEN` clause, separating them by the keyword `OR`, as follows:

    EXCEPTION
       WHEN over_limit OR under_limit OR VALUE_ERROR THEN
          -- handle the error

If any of the exceptions in the list is raised, the associated sequence of statements is executed. The keyword `OTHERS` cannot appear in the list of exception names; it must appear by itself. You can have any number of exception handlers, and each handler can associate a list of exceptions with a sequence of statements. However, an exception name can appear only once in the exception-handling part of a PL/SQL block or subprogram.

The usual scoping rules for PL/SQL variables apply, so you can reference local and global variables in an exception handler. However, when an exception is raised inside a cursor `FOR` loop, the cursor is closed implicitly before the handler is invoked. Therefore, the values of explicit cursor attributes are not available in the handler.

### Exceptions Raised in Declarations

Exceptions can be raised in declarations by faulty initialization expressions. For example, the following declaration raises an exception because the constant `limit` cannot store numbers larger than 999:

    DECLARE
       limit CONSTANT NUMBER(3) := 5000;  -- raises an exception
    BEGIN
       ...
    EXCEPTION
       WHEN OTHERS THEN ...  -- cannot catch the exception

Handlers in the current block cannot catch the raised exception because an exception raised in a declaration propagates immediately to the enclosing block.

### Exceptions Raised in Handlers

Only one exception at a time can be active in the exception-handling part of a block or subprogram. So, an exception raised inside a handler propagates immediately to the enclosing block, which is searched to find a handler for the newly raised exception. From there on, the exception propagates normally. Consider the following example:

    EXCEPTION
       WHEN INVALID_NUMBER THEN
          INSERT INTO ...  -- might raise DUP_VAL_ON_INDEX
       WHEN DUP_VAL_ON_INDEX THEN ...  -- cannot catch the exception

### Branching to or from an Exception Handler

A `GOTO` statement cannot branch to an exception handler; nor can it branch from an exception handler into the current block. For example, the following `GOTO` statement is illegal:

    DECLARE
       pe_ratio NUMBER(3,1);
    BEGIN
       DELETE FROM stats WHERE symbol = 'XYZ';
       SELECT price / NVL(earnings, 0) INTO pe_ratio FROM stocks
          WHERE symbol = 'XYZ';
       <<my_label>>
       INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio);
    EXCEPTION
       WHEN ZERO_DIVIDE THEN
          pe_ratio := 0;
          GOTO my_label;  -- illegal branch into current block

However, a `GOTO` statement can branch from an exception handler into an enclosing block.

### Using SQLCODE and SQLERRM

In an exception handler, you can use the 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

unless you used the pragma `EXCEPTION_INIT` to associate the exception name with an Oracle error number, in which case `SQLCODE` returns that error number and `SQLERRM` returns the corresponding error message. The maximum length of an Oracle error message is 512 characters including the error code, nested messages, and message inserts such as table and column names.

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 this message:

    ORA-01403: no data found

Passing a zero to `SQLERRM` always returns the following message:

    ORA-0000: 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 the following example shows:

    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);

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`. `SQLCODE` and `SQLERRM` are especially useful in the `OTHERS` exception handler because they tell you which internal exception was raised.

### Unhandled Exceptions

Remember, if it cannot find a handler for a raised exception, PL/SQL returns an unhandled exception error to the host environment, which determines the outcome. For example, in the Oracle Precompilers environment, any database changes made by a failed SQL statement or PL/SQL block are rolled back.

Unhandled exceptions can also affect subprograms. If you exit a subprogram successfully, PL/SQL assigns values to `OUT` parameters. However, if you exit with an unhandled exception, PL/SQL does not assign values to `OUT` parameters. Also, if a stored subprogram fails with an unhandled exception, PL/SQL does not roll back database work done by the subprogram.

You can avoid unhandled exceptions by coding an `OTHERS` handler at the topmost level of every PL/SQL block and subprogram.

## Useful Techniques

In this section, you learn three techniques that increase flexibility.

### Continuing after an Exception Is Raised

An exception handler lets you recover from an otherwise "fatal" error before exiting a block. But, when the handler completes, the block terminates. You cannot return to the current block from an exception handler. In the following example, if the `SELECT` `INTO` statement raises `ZERO_DIVIDE`, you cannot resume with the `INSERT` statement:

    DECLARE
       pe_ratio NUMBER(3,1);
    BEGIN
       DELETE FROM stats WHERE symbol = 'XYZ';
       SELECT price / NVL(earnings, 0) INTO pe_ratio FROM stocks
          WHERE symbol = 'XYZ';
       INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio);
    EXCEPTION
       WHEN ZERO_DIVIDE THEN ...

Though PL/SQL does not support continuable exceptions, you can still handle an exception for a statement, then continue with the next statement. Simply place the statement in its own sub-block with its own exception handlers. If an error occurs in the sub-block, a local handler can catch the exception. When the sub-block terminates, the enclosing block continues to execute at the point where the sub-block ends. Consider the following example:

    DECLARE
       pe_ratio NUMBER(3,1);
    BEGIN
       DELETE FROM stats WHERE symbol = 'XYZ';
       BEGIN  ---------- sub-block begins
          SELECT price / NVL(earnings, 0) INTO pe_ratio FROM stocks
             WHERE symbol = 'XYZ';
       EXCEPTION
          WHEN ZERO_DIVIDE THEN
             pe_ratio := 0;
       END;  ---------- sub-block ends
       INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio);
    EXCEPTION
       ...

In this example, if the `SELECT` `INTO` statement raises a `ZERO_DIVIDE` exception, the local handler catches it and sets `pe_ratio` to zero. Execution of the handler is complete, so the sub-block terminates, and execution continues with the `INSERT` statement.

### Retrying a Transaction

After an exception is raised, rather than abandon your transaction, you might want to retry it. The technique you use is simple. First, encase the transaction in a sub-block. Then, place the sub-block inside a loop that repeats the transaction.

Before starting the transaction, you mark a savepoint. If the transaction succeeds, you commit, then exit from the loop. If the transaction fails, control transfers to the exception handler, where you roll back to the savepoint undoing any changes, then try to fix the problem.

Consider the example below. When the exception handler completes, the sub-block terminates, control transfers to the `LOOP` statement in the enclosing block, the sub-block starts executing again, and the transaction is retried. You might want to use a `FOR` or `WHILE` loop to limit the number of tries.

    DECLARE
       name   CHAR(20);
       ans1   CHAR(3);
       ans2   CHAR(3);
       ans3   CHAR(3);
       suffix NUMBER := 1;
    BEGIN
       ...
       LOOP  -- could be FOR i IN 1..10 LOOP to allow ten tries
          BEGIN  -- sub-block begins
             SAVEPOINT start_transaction;  -- mark a savepoint
             /* Remove rows from a table of survey results. */
             DELETE FROM results WHERE answer1 = 'NO';
             /* Add a survey respondent's name and answers. */
             INSERT INTO results VALUES (name, ans1, ans2, ans3);
                -- raises DUP_VAL_ON_INDEX if two respondents
                -- have the same name (because there is a unique
                -- index on the name column)
             COMMIT;
             EXIT;
          EXCEPTION
             WHEN DUP_VAL_ON_INDEX THEN
                ROLLBACK TO start_transaction;  -- undo changes
                suffix := suffix + 1;             -- try to fix
                name := name || TO_CHAR(suffix);  -- problem
             ...
          END;  -- sub-block ends
       END LOOP;
    END;

### Using Locator Variables

Exceptions can mask the statement that caused an error, as the following example shows:

    BEGIN
       SELECT ...
       SELECT ...
       SELECT ...
       ...
    EXCEPTION
       WHEN NO_DATA_FOUND THEN ...
          -- Which SELECT statement caused the error?
    END;

Normally, this is not a problem. But, if the need arises, you can use a locator variable to track statement execution, as follows:

    DECLARE
       stmt INTEGER := 1;  -- designates 1st SELECT statement
    BEGIN
       SELECT ...
       stmt := 2;  -- designates 2nd SELECT statement
       SELECT ...
       stmt := 3;  -- designates 3rd SELECT statement
       SELECT ...
       ...
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
          INSERT INTO errors VALUES ('Error in statement ' || stmt);
          ...
    END;

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index