Section 1.6. Exception Handling

1.6. Exception Handling

In the PL/SQL language, errors of any kind are treated as exceptions situations that should not occurin your program. An exception can be one of the following:

• An error generated by the system (such as "out of memory" or "duplicate value in index")

• An error caused by a user action

• A warning issued by the application to the user

PL/SQL traps and responds to errors using an architecture of exception handlers. The exception handler mechanism allows you to cleanly separate your error processing code from your executable statements. It also provides an event-driven model, as opposed to a linear-code model, for processing errors. In other words, no matter how a particular exception is raised, it is handled by the same exception handler in the exception section.

When an error occurs in PL/SQL, whether it's a system error or an application error, an exception is raised. The processing in the current PL/SQL block's execution section halts, and control is transferred to the separate exception section of the current block, if one exists, to handle the exception. You cannot return to that block after you finish handling the exception. Instead, control is passed to the enclosing block, if any.

1.6.1. Defining Exceptions

Before an exception can be raised or handled, it must be defined. Oracle predefines thousands of exceptions, mostly by assigning numbers and messages to those exceptions. Oracle also assigns names to a relative few of these thousandsthe most commonly encountered exceptions.

These names are assigned in the STANDARD package (one of two default packages in PL/SQL), as well as in other built-in packages such as UTL_FILE and DBMS_SQL. The code Oracle uses to define exceptions like NO_DATA_FOUND is the same code that you will write to define or declare your own exceptions. You can do this in two different ways, described in the following sections.

You can also declare your own exceptions by listing the name of the exception you want to raise in your program followed by the keyword EXCEPTION:

     DECLARE        exception_name EXCEPTION;

The names for exceptions are similar in format to (and "read" just like) Boolean variable names, but they can be referenced in only two ways:

• In a RAISE statement in the execution section of the program (to raise the exception), as in:

       RAISE invalid_company_id;

  
  

• In the WHEN clauses of the exception section (to handle the raised exception), as in:

       WHEN invalid_company_id THEN

  
  

1.6.2. Raising Exceptions

There are three ways that an exception may be raised in your application:

• Oracle might raise the exception when it detects an error.

• You might raise an exception with the RAISE statement.

• You might raise an exception with the RAISE_APPLICATION_ERROR built-in procedure.

We've already looked at how Oracle raises exceptions. Now let's examine the different mechanisms you can use to raise exceptions.

1.6.2.1. RAISE statement

Oracle offers the RAISE statement so that you can, at your discretion, raise a named exception. You can raise an exception of your own or a system exception. The RAISE statement can take one of three forms:

     RAISE exception_name;     RAISE package_name.exception_name;     RAISE;

The first form (without a package name qualifier) can be used to raise an exception you have defined in the current block (or an outer block containing that block) or to raise a system exception defined in the STANDARD package.

The second form does require a package name qualifier. If an exception has been declared inside a package (other than STANDARD) and you are raising that exception outside that package, you must qualify your reference to that exception in your RAISE statement.

The third form of the RAISE statement does not require an exception name but can be used only within a WHEN clause of the exception section. Use this form when you want to re-raise (or propagate out) the same exception from within an exception handler.

1.6.2.2. Using RAISE_APPLICATION_ERROR

Oracle provides the RAISE_APPLICATION_ERROR procedure (defined in the default DBMS_STANDARD package) to raise application-specific errors in your application that need to be communicated back to the host environment.

The header for this procedure (defined in package DBMS_STANDARD) is shown here:

     PROCEDURE RAISE_APPLICATION_ERROR (        num binary_integer,        msg varchar2,        keeperrorstack boolean default FALSE);

where num is the error number and must be a value between -20,999 and -20,000 (just think: Oracle needs all the rest of those negative integers for its own exceptions!); msg is the error message and must be no more than 2K characters in length (any text beyond that limit will be ignored); and keeperrorstack indicates whether you want to add the error to any already on the stack (TRUE) or replace the existing errors (the default, FALSE).

1.6.3. Handling Exceptions

Once an exception is raised, the current PL/SQL block stops its regular execution and transfers control to the exception section. The exception is then either handled by an exception handler in the current PL/SQL block or passed to the enclosing block.

To handle or trap an exception once it is raised, you must write an exception handler for that exception. In your code, your exception handlers must appear after all the executable statements in your program but before the END statement of the block. The EXCEPTION keyword indicates the start of the exception section and the individual exception handlers. The syntax for an exception handler is as follows:

     WHEN exception_name [ OR exception_name... ]     THEN        executable statements

or:

     WHEN OTHERS     THEN        executable statements

The WHEN OTHERS clause is optional; if it is not present, then any unhandled exception is immediately propagated back to the enclosing block, if any. The WHEN OTHERS clause must be the last exception handler in the exception section.

1.6.3.1. Built-in error functions

Oracle provides several built-in functions to help you identify, analyze, and respond to errors that occur in your PL/SQL application.

SQLCODE

SQLCODE returns the error code of the most recently raised exception in your block. If there is no error, SQLCODE returns 0. SQLCODE also returns 0 when you call it outside of an exception handler.

SQLERRM

SQLERRM is a function that returns the error message for a particular error code. If you do not pass an error code to SQLERRM, it returns the error message associated with the value returned by SQLCODE. The maximum length string that SQLERRM will return is 512 bytes (in some earlier versions of Oracle, only 255 bytes).

DBMS_UTILITY.FORMAT_ERROR_STACK

This built-in function, like SQLERRM, returns the full message associated with the current error (i.e., the value returned by SQLCODE). As a rule, you should call this function inside your exception handler logic to obtain the full error message.

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

Introduced in Oracle Database 10g Release 1, this function returns a formatted string that displays a stack of programs and line numbers leading back to the line on which the error was originally raised.

1.6.3.2. Unhandled exceptions

If an exception is raised in your program and it is not handled by an exception section in either the current or enclosing PL/SQL blocks, that exception is unhandled. PL/SQL returns the error that raised the unhandled exception all the way back to the application environment from which PL/SQL was run. That environment (a tool like SQL*Plus, Oracle Forms, or a Java program) then takes an action appropriate to the situation; in the case of SQL*Plus, a ROLLBACK of any DML changes from within that top-level block's logic is automatically performed.

1.6.3.3. Propagation of an unhandled exception

The scope rules for exceptions determine the block in which an exception can be raised. The rules for exception propagation address the way in which an exception is handled after it is raised.

When an exception is raised, PL/SQL looks for an exception handler in the current block (anonymous block, procedure, or function) of the exception. If it does not find a match, then PL/SQL propagates the exception to the enclosing block of that current block. PL/SQL then attempts to handle the exception by raising it once more in the enclosing block. It continues to do this in each successive enclosing block until there are no more blocks in which to raise the exception (see Figure 1-3).

Figure 1-3. Propagation of exception handling

When all blocks are exhausted, PL/SQL returns an unhandled exception to the application environment that executed the outermost PL/SQL block. An unhandled exception halts the execution of the host program.