RSS

7.5 Exception Propagation

17 Apr

Lab completing this lab, you will be able to

  • Understand how exceptions propagate
  • Reraise exception

DECLARE

    v_test_var CHAR(3) := ‘ABCDE’;

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

BEGIN

    DBMS_OUTPUT.PUT_LINE(‘This is a test’);

EXCEPTION

    WHEN INVALID_NUMBER OR VALUE_ERROR THEN

        DBMS_OUTPUT.PUT_LINE(‘An error has occured’);

END;

DECLARE

*

ERROR at line 1:

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

too small

ORA-06512: at line 2

 

 

–outer block

BEGIN

    –inner block

    DECLARE

        v_test_var CHAR(3) := ‘ABCDE’;

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

    BEGIN

        DBMS_OUTPUT.PUT_LINE(‘This is a test’);

    EXCEPTION

        WHEN INVALID_NUMBER OR VALUE_ERROR THEN

            DBMS_OUTPUT.PUT_LINE(‘An error has occured in ‘ || ‘ the inner block’);

    END;

EXCEPTION

    WHEN INVALID_NUMBER OR VALUE_ERROR THEN

        DBMS_OUTPUT.PUT_LINE(‘An error has occured in the ‘ || ‘program’);

END;

An error has occurred in the program

PL/SQL procedure successfully completed.

 

 

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

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

        v_test_var := ‘ABCD’;

        DBMS_OUTPUT.PUT_LINE (‘An error has occurred’);

END;

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

 

 

–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

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

            v_test_var := ‘ABCD’;

            DBMS_OUTPUT.PUT_LINE(‘An error has occured in ‘ || ‘ the inner block’);

    END;

EXCEPTION

    WHEN INVALID_NUMBER OR VALUE_ERROR THEN

        DBMS_OUTPUT.PUT_LINE(‘An error has occured in the ‘ || ‘program’);

END;

An error has occurred in the program

PL/SQL procedure successfully completed.

 

 

–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 occured in ‘ || ‘ the inner block’);

    END;

EXCEPTION

    WHEN e_exception2 THEN

        DBMS_OUTPUT.PUT_LINE(‘An error has occured in ‘ || ‘ the program’);

END;

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.

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 e_exception

        WHEN e_exception THEN

            RAISE;

    END;

EXCEPTION

    WHEN e_exception THEN

        DBMS_OUTPUT.PUT_LINE(‘An error has occured’);

END;

It is important to note that when an exception is reraised in the block that is not enclosed by any other block, the program is unable to complete successfully.

Advertisements
 
Leave a comment

Posted by on 04/17/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: