RSS

8.3 Exception Propagation

30 Apr

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 also may occur in the declaration section or exception-handling section of the block. The rules that govern how exceptions are raised in these situations are called exception propagation. Consider the first case: A runtime error occurs in the executable section of the PL/SQL block. This case should be treated as a review, because the examples you have seen so far in this chapter show how an exception is raised when an error occurs in the executable section of the block. If an exception is associated with a particular error, control is passed to the exception-handling section of the block. After 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, execution of the program halts, and control is transferred to the host environment. Next, take a look at a second case: A runtime error occurs in the declaration section of the block. If there is no outer block, execution of the program halts, and control is passed to the host environment. Consider the following script:

 

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 following output:

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 cannot 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 cannot catch the error.

 

 

–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 following output:

An error has occurred in the program

PL/SQL procedure successfully completed.

In this example, the PL/SQL block is enclosed in another block, and the program can 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.

 

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 following output:

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 cannot prevent the error.

 

–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 following output:

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

 

 

–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 following output:

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 RAISE statement. 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 cannot complete successfully. This is illustrated by the following example:

DECLARE

e_exception1 EXCEPTION;

BEGIN

RAISE e_exception1;

END;

The following error message is displayed:

DECLARE

*

ERROR at line 1:

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

ORA-06512: at line 4

 

RERAISING 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 reraising an exception. The following example helps illustrate this point:

 

— 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 RAISE statement 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 following output:

An error has occurred

PL/SQL procedure successfully completed.

Advertisements
 
Leave a comment

Posted by on 04/30/2010 in ORACLE

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: