Typically, when an error occurs, processing of the PL/SQL block terminates immediately. Hence, your application stops processing and the task at hand goes unfinished. Oracle enables you to be prepared for these errors and write logic in your programs to handle them gracefully and allow processing to carry on as you have designed it. This logic written to manage errors is known as exception-handling code. With Oracle exception handling, when an error is detected, control is passed to the exception-handling portion of your program, and then processing completes normally. Handling errors also provides valuable information for debugging applications and for better "bulletproofing" the application against future errors.

Without a means to handle exceptions, a program must check for execution errors after each statement, as the following example shows:

SELECT....
IF error-- check for error associated with SELECT statement
THEN....
INSERT ....
IF error-- check for error associated with INSERT statement
THEN...
UPDATE....
IF error-- check for error associated with UPDATE statement
THEN...

As you can see, this increases the processing overhead because you have to explicitly check for errors after each statement. There is always a risk that you might overlook a statement and fail to check for errors, thereby leaving open the potential for an abnormal termination to your application.

With exception handling incorporated into your application, the same statement would be transformed to look like

BEGIN
SELECT....
INSERT....
UPDATE....
exception
-- check for and process errors here
END

This way of handling errors removes all the added processing required to explicitly handle errors. Also, the readability of the program is improved.

Exception-Handling Structures

In PL/SQL, the user can anticipate and trap for certain runtime errors. Exceptions can be internally defined by Oracle or the user.

There are three types of exceptions:

• Predefined Oracle errors
• Undefined Oracle errors
• User-defined errors

Predefined Oracle Errors
To build the exception-handler portion of your program, start the block of code with the keyword exception followed by the when clause. The following is the typical syntax of an exception-handling PL/SQL block.

The Syntax for the exception Command

exception
when exception_1 THEN
statements
when exception_2 THEN
statements
...

In this syntax, exception_1 and exception_2 are the names of the predefined exceptions. statements is the PL/SQL code that will be executed if the exception name is satisfied.

The Oracle server defines several errors with standard names. Although every Oracle error has a number, the errors must be referenced by name. PL/SQL has predefined some common Oracle errors and exceptions. Some of these predefined exception names are

• no_data_found—Single row SELECT returned no data.
• too_many_rows—Single row SELECT returned more than one row.
• invalid_cursor—Illegal cursor operation was attempted.
• value_error—Arithmetic, conversion, truncation, or constraint error occurred.
• invalid_number—Conversion of a number to a character string failed.
• zero_divide—Attempted to divide by zero.
• dup_val_on_index—Attempted to insert a duplicate value into a column that has a unique index.
• cursor_already_open—Attempted to open a cursor that was previously opened.
• not_logged_on—A database call was made without being logged into Oracle.
• transaction_backed_out—Usually raised when a remote portion of a transaction is rolled back.
• login_denied—Login to Oracle failed because of invalid username and password.
• program_error—Raised if PL/SQL encounters an internal problem.
• storage_error—Raised if PL/SQL runs out of memory or if memory is corrupted.
• timeout_on_resource—Timeout occurred while Oracle was waiting for a resource.
• value_error—Arithmetic, conversion, truncation, or constraint error occurred.
• others—This is a catchall. If the error was not trapped in the previous exception traps, the error will be trapped by this statement.

Oracle declares predefined exceptions globally in the package standard. Therefore, you do not need to declare them yourself.

The following example illustrates a PL/SQL exception-handling block:

...
SELECT * from employees
WHERE name like `Bluekers%';
...
exception             -- Exception block beginning
when no_data_found    -- first exception trap
CREATE_employee (`new','Bluekers';
COMMIT;
when others THEN       -- second exception trap
ROLLBACK;
COMMIT;
END                   -- end of exception handling

Undefined Oracle Errors
As you saw in the earlier example of exception-handling blocks, the others exception was used as a catchall exception handler. others is normally used when the exact nature of the exception isn't important, when the exception is unnamed, or even when it's unpredictable.

A different way to handle an error that is unnamed is with the pragma exception_init compiler directive. This directive simply transfers information to the compiler. The pragma tells the compiler to associate an exception name with an Oracle error number. In this way, you can refer to any internal exception by name and write a specific handler for it.

The declaration of the pragma exception_init must appear in the declarative portion of the PL/SQL block, packages, or subprogram. The following is the syntax for this declaration:

pragma exception_init (exception_name, error_number);

The exception name is a previously declared execution. The pragma declaration must appear somewhere after the exception declaration. The following example shows the exception and pragma declarations and the exception-handling block for the exception:

insufficient_funds  exception;
pragma exception_init (insufficient_funds, -2019);
BEGIN
...
exception
when insufficient_funds THEN
ROLLBACK;
END;

User-Defined Errors
Users can explicitly raise an exception with the RAISE command. The raise exceptions procedure should only be used when Oracle does not raise its own exception or when processing is undesirable or impossible to complete.

Steps for trapping a user-defined error include the following:

1. Declare the name for the user exception within the declaration section of the block.
2. Raise the exception explicitly within the executable portion of the block using the RAISE command.
3. Reference the declared exception with an error-handling routine.

The following example illustrates the use of the user-defined exception:

DECLARE
invalid_pay_type      exception;            -- user-defined exception
pay_type_code varchar2(2);
BEGIN
...
IF pay_type_code not in (`H','S') THEN  -- error trap
RAISE invalid_pay_type;                  -- raise user-defined exception
END IF;
exception                               -- handle user-defined exception
when invalid_pay_type THEN
ROLLBACK;
END

Exceptions can be raised in declarations when declarations and initializations are done incorrectly. When this happens, the exception handler must reside in the enclosing block. This is called exception propagation, which is discussed in more detail later in this chapter in the section titled "Propagate Exceptions."

SQLCODE and SQLERRM

In the exception-handling part of your program, use the functions SQLCODE and SQLERRM to obtain information about the most recent error that has occurred. This is especially useful when the exception is trapped with the when others clause. The when others clause is used to trap unanticipated or unknown exceptions.

The SQLCODE function returns the error code for the exception. SQLERRM returns the corresponding error message. The following are the valid values from the SQLCODE function:

• 0—Indicates that no exception has been raised.
• 1—Indicates that a user-defined exception has been raised.
• +1403—Indicates that a no_data_found exception has been raised.
• -###—The remaining negative values correspond to the actual error codes as defined in the Oracle Server Messages and Codes manual.

The following example traps and records the error code and message of the offending exception and stores it in a table for access at a later time:

DECLARE
error_code  number;
error_msg   varchar2(250);
BEGIN
...