Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources] نسخه متنی

Lab 10.3 Exception Propagation

You already have seen how different types of exceptions are raised when a runtime error occurs in the executable portion of the PL/SQL block. However, a runtime error may occur in the declaration section of the block or in the exception-handling section of the block. The rules that govern how exceptions are raised in these situations are referred to as exception propagation.

Consider the first case :
A runtime error occurred in the executable section of the PL/SQL block. This case should be treated as a review because the examples that you have seen earlier in this chapter show how an exception is raised when an error occurs in the executable section of the block.

If there is an exception specified associated with a particular error, control is passed to the exception-handling section of the block. Once the statements associated with the exception are executed, control is passed to the host environment or to the enclosing block. If there is no exception handler for this error, the exception is propagated to the enclosing block (outer block). Then the steps just described are repeated again. If no exception handler is found, the execution of the program halts, and control is transferred to the host environment.

Next, take a look at a second case :
A runtime error occurred in the declaration section of the block. If there is no outer block, the execution of the program halts, and control is passed to the host environment. Consider the following script.

FOR EXAMPLE

DECLARE
v_test_var CHAR(3):= 'ABCDE';
BEGIN
DBMS_OUTPUT.PUT_LINE ('This is a test');
EXCEPTION
WHEN INVALID_NUMBER OR VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE ('An error has occurred');
END;

When executed, this example produces the output shown:

DECLARE

*

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-06512: at line 2

As you can see, the assignment statement in the declaration section of the block causes an error. Even though there is an exception handler for this error, the block is not able to execute successfully. Based on this example you may conclude that when a runtime error occurs in the declaration section of the PL/SQL block, the exception-handling section of this block is not able to catch the error.

Next, consider an example with nested PL/SQL blocks.

FOR EXAMPLE

--outer block

BEGIN
-- inner block
DECLARE
v_test_var CHAR(3):= 'ABCDE';
BEGIN
DBMS_OUTPUT.PUT_LINE ('This is a test');
EXCEPTION
WHEN INVALID_NUMBER OR VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE ('An error has occurred in '||
'the inner block');
END;

EXCEPTION

WHEN INVALID_NUMBER OR VALUE_ERROR THEN

DBMS_OUTPUT.PUT_LINE ('An error has occurred in the '||

'program');
END;

When executed, this example produces the output shown:

An error has occurred in the program

PL/SQL procedure successfully completed.

In this example, the PL/SQL block is enclosed by another block, and the program is able to complete. This is possible because the exception defined in the outer block is raised when the error occurs in the declaration section of the inner block. Therefore, you can conclude that

when a runtime error occurs in the declaration section of the inner block, the exception immediately propagates to the enclosing (outer) block .

Finally, consider a third case: A runtime error occurred in the exception-handling section of the block. Just like in the previous case, if there is no outer block, the execution of the program halts, and control is passed to the host environment. Consider the following script.

FOR EXAMPLE

DECLARE
v_test_var CHAR(3) := 'ABC';
BEGIN
v_test_var := '1234';
DBMS_OUTPUT.PUT_LINE ('v_test_var: '||v_test_var);
EXCEPTION
WHEN INVALID_NUMBER OR VALUE_ERROR THEN
v_test_var := 'ABCD';
DBMS_OUTPUT.PUT_LINE ('An error has occurred');
END;

When executed, this example produces the output shown:

DECLARE

*

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-06512: at line 8

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

As you can see, the assignment statement in the executable section of the block causes an error. Therefore, control is transferred to the exception-handling section of the block. However, the assignment statement in the exception-handling section of the block raises the same error. As a result, the output of this example contains the same error message twice. The first message is generated by the assignment statement in the executable section of the block, and the second message is generated by the assignment statement of the exception-handling section of this block. Based on this example, you may conclude that

when a runtime error occurs in the exception-handling section of the PL/SQL block, the exception-handling section of this block is not able to prevent the error.

Next, consider an example with nested PL/SQL blocks.

FOR EXAMPLE

--outer block

BEGIN
-- inner block
DECLARE
v_test_var CHAR(3) := 'ABC';
BEGIN
v_test_var := '1234';
DBMS_OUTPUT.PUT_LINE ('v_test_var: '||v_test_var);
EXCEPTION
WHEN INVALID_NUMBER OR VALUE_ERROR THEN
v_test_var := 'ABCD';
DBMS_OUTPUT.PUT_LINE ('An error has occurred in '||
'the inner block');
END;

EXCEPTION

WHEN INVALID_NUMBER OR VALUE_ERROR THEN

DBMS_OUTPUT.PUT_LINE ('An error has occurred in the '||

'program');

END;

When executed, this example produces the output shown:

An error has occurred in the program

PL/SQL procedure successfully completed.

In this example, the PL/SQL block is enclosed by another block, and the program is able to complete. This is possible because the exception defined in the outer block is raised when the error occurs in the exception-handling section of the inner block. Therefore, you can conclude that

when a runtime error occurs in the exception-handling section of the inner block, the exception immediately propagates to the enclosing block.

In the previous two examples, an exception is raised implicitly by a runtime error in the exception-handling section of the block. However, an exception can be raised in the exception-handling section of the block explicitly by the RAISE statement. Consider the following example.

FOR EXAMPLE

--outer block
DECLARE
e_exception1 EXCEPTION;
e_exception2 EXCEPTION;
BEGIN
-- inner block
BEGIN
RAISE e_exception1;
EXCEPTION
WHEN e_exception1 THEN
RAISE e_exception2;
WHEN e_exception2 THEN
DBMS_OUTPUT.PUT_LINE ('An error has occurred in '||
'the inner block');
END;
EXCEPTION
WHEN e_exception2 THEN
DBMS_OUTPUT.PUT_LINE ('An error has occurred in '||
'the program');
END;

This example produces the output shown:

An error has occurred in the program

PL/SQL procedure successfully completed.

Here two exceptions are declared :
e_exception1 and e_exception2. Exception e_exception1 is raised in the inner block via the statement RAISE. In the exception-handling section of the block, exception e_exception1 tries to raise e_exception2. Even though there is an exception handler for the exception e_exception2 in the inner block, control is transferred to the outer block. This happens because only one exception can be raised in the exception-handling section of the block. Only after one exception has been handled can another be raised, but two or more exceptions cannot be raised simultaneously.

When a PL/SQL block is not enclosed by another block, control is transferred to the host environment, and the program is not able to complete successfully. Then the following error message is displayed.

DECLARE

*

ERROR at line 1:

ORA-06510: PL/SQL: unhandled user-defined exception

ORA-06512: at line 10

ORA-06510: PL/SQL: unhandled user-defined exception

Re-Raising an Exception

On some occasions you may want to be able to stop your program if a certain type of error occurs. In other words, you may want to handle an exception in the inner block and then pass it to the outer block. This process is called re-raising an exception. The following example helps to illustrate this point.

FOR EXAMPLE

-- outer block
DECLARE
e_exception EXCEPTION;
BEGIN
-- inner block
BEGIN
RAISE e_exception;
EXCEPTION
WHEN e_exception THEN
RAISE;
END;
EXCEPTION
WHEN e_exception THEN
DBMS_OUTPUT.PUT_LINE ('An error has occurred');
END;

In this example, the exception e_exception is declared in the outer block. Then it is raised in the inner block. As a result, control is transferred to the exception-handling section of the inner block. The statement RAISE in the exception-handling section of the block causes the exception to propagate to the exception-handling section of the outer block. Notice that when the RAISE statement is used in the exception-handling section of the inner block, it is not followed by the exception name.

When run, this example produces the output shown:

The error has occurred

PL/SQL procedure successfully completed.

It is important to note that when an exception is re-raised in the block that is not enclosed by any other block, the program is unable to complete successfully. Consider the following example: DECLARE e_exception EXCEPTION; BEGIN RAISE e_exception; EXCEPTION WHEN e_exception THEN RAISE; END; When run, this example produces the following output: DECLARE * ERROR at line 1: ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at line 7