What happens if exception is raised in the program and that exception is not handled by an exception section?

You learned to write PL/SQL blocks with a declarative section and an executable section. All the SQL and PL/SQL code that must be executed is written in the executable block.

So far it has been assumed that the code works satisfactorily if you take care of compile-time errors. However, the code may cause some unanticipated errors at run time. In this post, you learn how to deal with such errors in the PL/SQL block.

What Is an Exception?

Consider the example shown below.

DECLARE v_lname VARCHAR2(15); BEGIN SELECT last_name INTO v_lname FROM employees WHERE first_name='John'; DBMS_OUTPUT.PUT_LINE ('John''s last name is :' ||v_lname); END;

There are no syntax errors in the code, which means that you must be able to successfully execute the anonymous block. The SELECT statement in the block retrieves the last name of John.

However, you see the following error report when you execute the code:

The code does not work as expected. You expected the SELECT statement to retrieve only one row; however, it retrieves multiple rows. Such errors that occur at run time are called exceptions. When an exception occurs, the PL/SQL block is terminated. You can handle such exceptions in your PL/SQL block.

Handling the Exception: An Example

Conside the example shown below:

DECLARE v_lname VARCHAR2(15); BEGIN SELECT last_name INTO v_lname FROM employees WHERE first_name='John'; DBMS_OUTPUT.PUT_LINE ('John''s last name is :' ||v_lname); EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE (' Your select statement retrieved multiple rows. Consider using a cursor.'); END; /

Output:

anonymous block completed Your select statement retrieved multiple rows. Consider using a cusrsor

You have previously learned how to write PL/SQL blocks with a declarative section (beginning with the DECLARE keyword) and an executable section (beginning and ending with the BEGIN and END keywords, respectively).

For exception handling, you include another optional section called the exception section.

• This section begins with the EXCEPTION keyword.
• If present, this must be the last section in a PL/SQL block.

In the example above, the code from the previous section is rewritten to handle the exception that occurred. The output of the code is shown as well. By adding the EXCEPTION section of the code, the PL/SQL program does not terminate abruptly. When the exception is raised, the control shifts to the exception section and all the statements in the exception section are executed. The PL/SQL block terminates with normal, successful completion

Understanding Exceptions with PL/SQL

An exception is an error in PL/SQL that is raised during the execution of a block. A block always terminates when PL/SQL raises an exception, but you can specify an exception handler to perform final actions before the block ends.

Two Methods for Raising an Exception

1. An Oracle error occurs and the associated exception is raised automatically. For example, if the ORA-01403 error occurs when no rows are retrieved from the database in a SELECT statement, PL/SQL raises the NO_DATA_FOUND exception. These errors are converted into predefined exceptions.

2. Depending on the business functionality your program implements, you may have to explicitly raise an exception. You raise an exception explicitly by issuing the RAISE statement in the block. The raised exception may be either user-defined or predefined. There are also some nonpredefined Oracle errors. These errors are any standard Oracle errors that are not predefined. You can explicitly declare exceptions and associate them with the nonpredefined Oracle errors.

Trapping an Exception

Include an EXCEPTION section in your PL/SQL program to trap exceptions. If the exception is raised in the executable section of the block, processing branches to the corresponding exception handler in the exception section of the block. If PL/SQL successfully handles the exception, the exception does not propagate to the enclosing block or to the calling environment. The PL/SQL block terminates successfully.

Propagating an Exception

If the exception is raised in the executable section of the block and there is no corresponding exception handler, the PL/SQL block terminates with failure and the exception is propagated to an enclosing block or to the calling environment. The calling environment can be any application (such as SQL*Plus that invokes the PL/SQL program).

Exception Types

There are three types of exceptions.

Note: Some application tools with client-side PL/SQL (such as Oracle Developer Forms) have their own exceptions.

Syntax to Trap Exceptions

You can trap any error by including a corresponding handler within the exception-handling section of the PL/SQL block. Each handler consists of a WHEN clause, which specifies an exception name, followed by a sequence of statements to be executed when that exception is raised.

You can include any number of handlers within an EXCEPTION section to handle specific exceptions. However, you cannot have multiple handlers for a single exception.

EXCEPTION WHEN exception1 [OR exception2 . . .] THEN statement1; statement2; ... [WHEN exception3 [OR exception4 . . .] THEN statement1; statement2; ...] [WHEN OTHERS THEN statement1; statement2; ...]

Exception-trapping syntax includes the following elements:

• exception – Is the standard name of a predefined exception or the name of a user-defined exception declared within the declarative section.
• statement – Is one or more PL/SQL or SQL statements.
• OTHERS – Is an optional exception-handling clause that traps any exceptions that have not been explicitly handled.

WHEN OTHERS Exception Handler

As stated previously, the exception-handling section traps only those exceptions that are specified. To trap any exceptions that are not specified, you use the OTHERS exception handler. This option traps any exception not yet handled. For this reason, if the OTHERS handler is used, it must be the last exception handler that is defined.

For example:

WHEN NO_DATA_FOUND THEN statement1; ... WHEN TOO_MANY_ROWS THEN statement1; ... WHEN OTHERS THEN statement1;

Example
Consider the preceding example. If the NO_DATA_FOUND exception is raised by the program,the statements in the corresponding handler are executed. If the TOO_MANY_ROWS exception is raised, the statements in the corresponding handler are executed. However, if some other exception is raised, the statements in the OTHERS exception handler are executed.

The OTHERS handler traps all the exceptions that are not already trapped. Some Oracle tools have their own predefined exceptions that you can raise to cause events in the application. The OTHERS handler also traps these exceptions.

Guidelines for Trapping Exceptions

• Begin the exception-handling section of the block with the EXCEPTION keyword.
• Define several exception handlers, each with its own set of actions, for the block.
• When an exception occurs, PL/SQL processes only one handler before leaving the block.
• Place the OTHERS clause after all other exception-handling clauses.
• You can have only one OTHERS clause.
• Exceptions cannot appear in assignment statements or SQL statements.

Trapping Predefined Oracle Server Errors

Trap a predefined Oracle Server error by referencing its predefined name within the corresponding exception-handling routine. Sample predefined exceptions:

• NO_DATA_FOUND
• TOO_MANY_ROWS
• INVALID_CURSOR
• ZERO_DIVIDE
• DUP_VAL_ON_INDEX

Note: PL/SQL declares predefined exceptions in the STANDARD package.

Trapping Nonpredefined Oracle Server Errors

Nonpredefined exceptions are similar to predefined exceptions; however, they are not defined as PL/SQL exceptions in the Oracle Server. They are standard Oracle errors. You create exceptions with standard Oracle errors by using the PRAGMA EXCEPTION_INIT function. Such exceptions are called nonpredefined exceptions.

You can trap a nonpredefined Oracle Server error by declaring it first. The declared exception is raised implicitly. In PL/SQL, PRAGMA EXCEPTION_INIT tells the compiler to associate an exception name with an Oracle error number. This enables you to refer to any internal exception by name and to write a specific handler for it.

Note: PRAGMA (also called pseudoinstructions) is the keyword that signifies that the statement is a compiler directive, which is not processed when the PL/SQL block is executed. Rather, it directs the PL/SQL compiler to interpret all occurrences of the exception name within the block as the associated Oracle Server error number.

Nonpredefined Error Trapping: Example

Condider the example shown below to trap Oracle Server error 01400 (“cannot insert NULL”):

DECLARE e_insert_excep EXCEPTION; PRAGMA EXCEPTION_INIT(e_insert_excep, -01400); BEGIN INSERT INTO departments (department_id, department_name) VALUES (280, NULL); EXCEPTION WHEN e_insert_excep THEN DBMS_OUTPUT.PUT_LINE('INSERT OPERATION FAILED'); DBMS_OUTPUT.PUT_LINE(SQLERRM); END; /

Output:

anonymous block completed INSERT OPERATION FAILED ORA-1400: cannot insert NULL into ("ORA41","DEPARTMENTS","DEPARTMENT_NAME")

The example illustrates the three steps associated with trapping a nonpredefined error:
1. Declare the name of the exception in the declarative section, using the syntax:

In the syntax, exception is the name of the exception.

2. Associate the declared exception with the standard Oracle Server error number by using the PRAGMA EXCEPTION_INIT function. Use the following syntax:

PRAGMA EXCEPTION_INIT(exception, error_number);

In the syntax, exception is the previously declared exception and error_number is a standard Oracle Server error number.

3. Reference the declared exception within the corresponding exception-handling routine.

Example
The example shown above tries to insert the NULL value for the department_name column of the departments table. However, the operation is not successful because department_name is a NOT NULL column. Note the following line in the example:

DBMS_OUTPUT.PUT_LINE(SQLERRM);

The SQLERRM function is used to retrieve the error message. You learn more about SQLERRM in the next few sections.

Functions for Trapping Exceptions

When an exception occurs, you can identify the associated error code or error message by using two functions. Based on the values of the code or the message, you can decide which subsequent actions to take.

SQLCODE returns the Oracle error number for internal exceptions. SQLERRM returns the message associated with the error number.

SQLCODE Values: Examples

Functions for Trapping Exceptions

When an exception is trapped in the WHEN OTHERS exception handler, you can use a set of generic functions to identify those errors. The example shown below illustrates the values of SQLCODE and SQLERRM assigned to variables, and then those variables being used in a SQL statement.

DECLARE error_code NUMBER; error_message VARCHAR2(255); BEGIN ... EXCEPTION ... WHEN OTHERS THEN ROLLBACK; error_code := SQLCODE ; error_message := SQLERRM ; INSERT INTO errors (e_user, e_date, error_code, error_message) VALUES(USER,SYSDATE,error_code, error_message); END; /

You cannot use SQLCODE or SQLERRM directly in a SQL statement. Instead, you must assign their values to local variables, and then use the variables in the SQL statement, as shown in the following example:

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

Trapping User-Defined Exceptions

PL/SQL enables you to define your own exceptions depending on the requirements of your application. For example, you may prompt the user to enter a department number.Define an exception to deal with error conditions in the input data. Check whether the department number exists. If it does not, you may have to raise the user-defined exception.

PL/SQL exceptions must be:

• Declared in the declarative section of a PL/SQL block
• Raised explicitly with RAISE statements
• Handled in the EXCEPTION section

You trap a user-defined exception by declaring it and raising it explicitly.

1. Declare the name of the user-defined exception within the declarative section.

Syntax:

In the syntax, exception is the name of the exception.

2. Use the RAISE statement to raise the exception explicitly within the executable section.

Syntax:

In the syntax, exception is the previously declared exception.

3. Reference the declared exception within the corresponding exception-handling routine.

Consider the example shown below:

DECLARE v_deptno NUMBER := 500; v_name VARCHAR2(20) := 'Testing'; e_invalid_department EXCEPTION; BEGIN UPDATE departments SET department_name = v_name WHERE department_id = v_deptno; IF SQL%NOTFOUND THEN RAISE e_invalid_department; END IF; COMMIT; EXCEPTION WHEN e_invalid_department THEN DBMS_OUTPUT.PUT_LINE('No such department id.'); END; /

Output:

anonymous block completed No such department id.

Example:
The block shown in the example above updates the department_name of a department. The user supplies the department number and the new name. If the supplied department number does not exist, no rows are updated in the departments table. An exception is raised and a message is printed for the user that an invalid department number was entered.

Note: Use the RAISE statement by itself within an exception handler to raise the same exception again and propagate it back to the calling environment.

Propagating Exceptions in a Subblock

When a subblock handles an exception, it terminates normally. Control resumes in the enclosing block immediately after the subblock’s END statement. However, if a PL/SQL raises an exception and the current block does not have a handler for that exception, the exception propagates to successive enclosing blocks until it finds a handler. If none of these blocks handles the exception, an unhandled exception in the host environment results.

When the exception propagates to an enclosing block, the remaining executable actions in that block are bypassed. One advantage of this behavior is that you can enclose statements that require their own exclusive error handling in their own block, while leaving more general exception handling to the enclosing block.

Consider the example shown below:

DECLARE ... e_no_rows exception; e_integrity exception; PRAGMA EXCEPTION_INIT (e_integrity, -2292); BEGIN FOR c_record IN emp_cursor LOOP BEGIN SELECT ... UPDATE ... IF SQL%NOTFOUND THEN RAISE e_no_rows; END IF; END; END LOOP; EXCEPTION WHEN e_integrity THEN ... WHEN e_no_rows THEN ... END; /

Note in the example that the exceptions (no_rows and integrity) are declared in the outer block. In the inner block, when the no_rows exception is raised, PL/SQL looks for the exception to be handled in the subblock. Because the exception is not handled in the subblock, the exception propagates to the outer block, where PL/SQL finds the handler.

The RAISE Statement

The RAISE statement stops normal execution of a PL/SQL block or subprogram and transfers control to an exception handler. It explicitly raises predefined exceptions or user-defined exceptions

Syntax:

In the syntax, the exception_name is the name of a predefined or a user-defined exception.The exception_name is optional only in an exception handler, where the default is the current exception. Outside an exception handler, you must specify the name of the exception.

RAISE_APPLICATION_ERROR Procedure

Use the RAISE_APPLICATION_ERROR procedure to communicate a predefined exception interactively by returning a nonstandard error code and error message. With RAISE_APPLICATION_ERROR, you can report errors to your application and avoid returning unhandled exceptions.

Syntax:

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

In the syntax:

• error_number – Is a user-specified number for the exception between –20,000 and –20,999.
• message – Is the user-specified message for the exception; is a character string up to 2,048-bytes long.
• TRUE | FALSE – Is an optional Boolean parameter (If TRUE, the error is placed on the stack of previous errors. If FALSE, which is the default, the error replaces all previous errors.)

The RAISE_APPLICATION_ERROR procedure can be used in either the executable section or the exception section of a PL/SQL program, or both. The returned error is consistent with how the Oracle Server produces a predefined, nonpredefined, or user-defined error. The error number and message are displayed to the user.

The example below shows that the RAISE_APPLICATION_ERROR procedure can be used in both the executable and the exception sections of a PL/SQL program.

Executable section:

BEGIN ... DELETE FROM employees WHERE manager_id = v_mgr; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR(-20202, 'This is not a valid manager'); END IF; ...

Exception section:

... EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20201, 'Manager is not a valid employee.'); END; /

Here is another example of using the RAISE_APPLICATION_ERROR procedure:

DECLARE e_name EXCEPTION; BEGIN ... DELETE FROM employees WHERE last_name = 'Higgins'; IF SQL%NOTFOUND THEN RAISE e_name; END IF; EXCEPTION WHEN e_name THEN RAISE_APPLICATION_ERROR (-20999, 'This is not a valid last name'); ... END; /