Lab 10.3 Exception Propagation

Team-Fly    

Oracle® PL/SQL® Interactive Workbook, Second Edition
By Benjamin Rosenzweig, Elena Silvestrova
Table of Contents
Chapter 10.  Exceptions


Lab Objectives

After this Lab, you will be able to:

  • Understand How Exceptions Propagate

  • Re-raise Exceptions

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.

graphics/intfig03.gif 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.

graphics/intfig03.gif 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.

graphics/intfig03.gif 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.

graphics/intfig03.gif 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.

graphics/intfig03.gif 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.

graphics/intfig03.gif 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. 

graphics/intfig07.gif

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 


Lab 10.3 Exercises

10.3.1 Understand How Exceptions Propagate

In this exercise, you will use nested PL/SQL blocks to practice exception propagation. You will be asked to experiment with the script via exceptions. Try to answer the questions before you run the script. Once you have answered the questions, run the script and check your answers.

Create the following PL/SQL script:

 -- ch10_3a.sql, version 1.0  SET SERVEROUTPUT ON  DECLARE     v_my_name VARCHAR2(15) := 'ELENA SILVESTROVA';  BEGIN     DBMS_OUTPUT.PUT_LINE ('My name is '||v_my_name);     DECLARE        v_your_name VARCHAR2(15);     BEGIN        v_your_name := '&sv_your_name';        DBMS_OUTPUT.PUT_LINE ('Your name is '||v_your_name);     EXCEPTION        WHEN VALUE_ERROR THEN           DBMS_OUTPUT.PUT_LINE ('Error in the inner block');           DBMS_OUTPUT.PUT_LINE ('This name is too long');     END;  EXCEPTION     WHEN VALUE_ERROR THEN        DBMS_OUTPUT.PUT_LINE ('Error in the outer block');        DBMS_OUTPUT.PUT_LINE ('This name is too long');  END; 

Answer the following questions first, and then execute the script:

a)

What exception is raised by the assignment statement in the declaration section of the outer block?

b)

Once this exception (based on the previous question) is raised, will the program terminate successfully? You should explain your answer.

c)

How would you change this script so that the exception is able to handle an error caused by the assignment statement in the declaration section of the outer block?

d)

Change the value of the variable from "Elena Silvestrova" to "Elena." Then change the script so that if there is an error caused by the assignment statement of the inner block, it is handled by the exception-handling section of the outer block.

10.3.2 Re-Raise Exceptions

In this exercise, you will check the number of sections for each course. If a course does not have a section associated with it, you will raise an exception, e_no_sections. Again, try to answer the questions before you run the script. Once you have answered the questions, run the script and check your answers.

Create the following PL/SQL script:

 -- ch10_4a.sql, version 1.0  SET SERVEROUTPUT ON  DECLARE     CURSOR course_cur IS        SELECT course_no           FROM course;     v_total NUMBER;     e_no_sections EXCEPTION;  BEGIN     FOR course_rec in course_cur LOOP        BEGIN           SELECT COUNT(*)             INTO v_total             FROM section            WHERE course_no = course_rec.course_no;           IF v_total = 0 THEN              RAISE e_no_sections;           ELSE              DBMS_OUTPUT.PUT_LINE ('Course, '||                 course_rec.course_no||' has '||                 v_total||' sections');           END IF;        EXCEPTION           WHEN e_no_sections THEN              DBMS_OUTPUT.PUT_LINE ('There are no sections '||                 'for course '||course_rec.course_no);        END;     END LOOP;  END; 

Answer the following questions first, and then execute the script:

a)

What exception will be raised if there are no sections for a given course number?

b)

If the exception e_no_sections is raised, will the cursor FOR loop terminate? Explain your answer.

c)

Change this script so that the exception e_no_sections is reraised in the outer block.

Lab 10.3 Exercise Answers

This section gives you some suggested answers to the questions in Lab 10.3, with discussion related to how those answers resulted. The most important thing to realize is whether your answer works. You should figure out the implications of the answers here and what the effects are from any different answers you may come up with.

10.3.1 Answers

a)

What exception is raised by the assignment statement in the declaration section of the outer block?

A1:

Answer: The exception VALUE_ERROR is raised by the assignment statement of the outer block.

The variable v_my_name is declared as VARCHAR2(15). However, the value that is assigned to this variable contains seventeen letters. As a result, the assignment statement causes a runtime error.

b)

Once this exception (based on the previous question) is raised, will the program terminate successfully? You should explain your answer.

A2:

Answer: When that exception VALUE_ERROR is raised, the script is not able to complete successfully because the error occurred in the declaration section of the outer block. Since the outer block is not enclosed by any other block, control is transferred to the host environment. As a result, an error message will be generated when this example is run.

c)

How would you change this script so that the exception is able to handle an error caused by the assignment statement in the declaration section of the outer block?

A3:

Answer: In order for the exception to handle the error generated by the assignment statement in the declaration section of the outer block, the assignment statement must be moved to the executable section of this block. All changes are shown in bold letters.

 -- ch10_3b.sql, version 2.0  SET SERVEROUTPUT ON  DECLARE     v_my_name VARCHAR2(15);  BEGIN     v_my_name := 'ELENA SILVESTROVA';     DBMS_OUTPUT.PUT_LINE ('My name is '||v_my_name);     DECLARE        v_your_name VARCHAR2(15);     BEGIN        v_your_name := '&sv_your_name';        DBMS_OUTPUT.PUT_LINE ('Your name is '||v_your_name);     EXCEPTION        WHEN VALUE_ERROR THEN           DBMS_OUTPUT.PUT_LINE ('Error in the inner block');           DBMS_OUTPUT.PUT_LINE ('This name is too long');     END;     EXCEPTION        WHEN VALUE_ERROR THEN           DBMS_OUTPUT.PUT_LINE ('Error in the outer block');           DBMS_OUTPUT.PUT_LINE ('This name is too long');     END; 

The new version of this script produces the following output:

 Enter value for sv_your_name: TEST A NAME  old   9:       v_your_name := '&sv_your_name';  new   9:       v_your_name := 'TEST A NAME';  Error in the outer block  This name is too long  PL/SQL procedure successfully completed. 
d)

Change the value of the variable from "Elena Silvestrova" to "Elena." Then change the script so that if there is an error caused by the assignment statement of the inner block, it is handled by the exception-handling section of the outer block.

A4:

Answer: Note that when the value of the variable used in the outer block is changed from "Elena Silvestrova" to "Elena", it allows the script to pass control of the execution to the inner block. In the previous versions of this example, the inner block was never executed because the VALUE_ERROR exception was always encountered in the outer block.

Your script should look similar to the script below. All changes are shown in bold letters.

 -- ch10_3c.sql, version 3.0  SET SERVEROUTPUT ON  DECLARE     v_my_name VARCHAR2(15) := 'ELENA';  BEGIN     DBMS_OUTPUT.PUT_LINE ('My name is '||v_my_name);     DECLARE        v_your_name VARCHAR2(15) := '&sv_your_name';     BEGIN        DBMS_OUTPUT.PUT_LINE ('Your name is '||v_your_name);     EXCEPTION        WHEN VALUE_ERROR THEN           DBMS_OUTPUT.PUT_LINE ('Error in the inner block');           DBMS_OUTPUT.PUT_LINE ('This name is too long');     END;  EXCEPTION     WHEN VALUE_ERROR THEN        DBMS_OUTPUT.PUT_LINE ('Error in the outer block');        DBMS_OUTPUT.PUT_LINE ('This name is too long');  END; 

In this version of the example, the assignment statement was moved from the executable section of the inner block to the declaration section of this block. As a result, if an exception is raised by the assignment statement of the inner block, control is transferred to the exception section of the outer block.

You can modify this example in a different manner that allows you to achieve the same result.

 -- ch10_3d.sql, version 4.0  SET SERVEROUTPUT ON  DECLARE     v_my_name VARCHAR2(15) := 'ELENA';  BEGIN     DBMS_OUTPUT.PUT_LINE ('My name is '||v_my_name);     DECLARE        v_your_name VARCHAR2(15);     BEGIN        v_your_name := '&sv_your_name';        DBMS_OUTPUT.PUT_LINE ('Your name is '||v_your_name);     EXCEPTION        WHEN VALUE_ERROR THEN           RAISE;     END;  EXCEPTION     WHEN VALUE_ERROR THEN        DBMS_OUTPUT.PUT_LINE ('Error in the outer block');        DBMS_OUTPUT.PUT_LINE ('This name is too long');  END; 

In this version of the example, the RAISE statement was used in the exception-handling section of the inner block. As a result, the exception is re-raised in the outer block.

Both versions of this example produce very similar output. The first output is generated by the third version of the example, and the second output is generated by the fourth version of the example.

 Enter value for sv_your_name: THIS NAME MUST BE REALLY  LONG  old   6:       v_your_name VARCHAR2(15) := '&sv_your_name';  new   6:       v_your_name VARCHAR2(15) := 'THIS NAME MUST  BE REALLY LONG';  My name is ELENA  Error in the outer block  This name is too long  PL/SQL procedure successfully completed.  Enter value for sv_your_name: THIS NAME MUST BE REALLY  LONG  old   8:    v_your_name := '&sv_your_name';  new   8:      v_your_name := 'THIS NAME MUST BE REALLY  LONG';  My name is ELENA  Error in the outer block  This name is too long  PL/SQL procedure successfully completed. 

Notice that the only difference between the two versions of the output is the line number of the bind variable. In the first version of the output, the assignment statement takes place in the declaration section of the inner block. In the second version of the output, the assignment statement occurs in the executable section of the inner block. However, all messages displayed on the screen are identical in both versions of the output.

10.3.2 Answers

a)

What exception will be raised if there are no sections for a given course number?

A1:

Answer: If there are no sections for a given course number, the exception e_no_sections is raised.

b)

If the exception e_no_sections is raised, will the cursor FOR loop terminate? Explain your answer.

A2:

Answer: If the exception e_no_sections is raised, the cursor FOR loop will continue its normal execution. This is possible because the inner block, in which this exception is raised and handled, is located inside the body of the loop. As a result, the example produces the following output:

 Course, 10 has 1 sections  Course, 20 has 4 sections  Course, 25 has 9 sections  There are no sections for course 80  Course, 100 has 5 sections  Course, 120 has 6 sections  Course, 122 has 5 sections  Course, 124 has 4 sections  Course, 125 has 5 sections  Course, 130 has 4 sections  Course, 132 has 2 sections  Course, 134 has 3 sections  Course, 135 has 4 sections  Course, 140 has 3 sections  Course, 142 has 3 sections  Course, 144 has 1 sections  Course, 145 has 2 sections  Course, 146 has 2 sections  Course, 147 has 1 sections  Course, 204 has 1 sections  Course, 210 has 1 sections  Course, 220 has 1 sections  Course, 230 has 2 sections  Course, 240 has 2 sections  Course, 310 has 1 sections  Course, 330 has 1 sections  Course, 350 has 3 sections  Course, 420 has 1 sections  Course, 430 has 2 sections  Course, 450 has 1 sections  PL/SQL procedure successfully completed. 
c)

Change this script so that the exception e_no_sections is reraised in the outer block.

A3:

Answer: Your script should look similar to the script shown. All changes are shown in bold letters.

 -- ch10_4b.sql, version 2.0  SET SERVEROUTPUT ON  DECLARE     CURSOR course_cur IS        SELECT course_no          FROM course;     v_total NUMBER;     e_no_sections EXCEPTION;  BEGIN     FOR course_rec in course_cur LOOP        BEGIN           SELECT COUNT(*)             INTO v_total             FROM section            WHERE course_no = course_rec.course_no;           IF v_total = 0 THEN              RAISE e_no_sections;           ELSE              DBMS_OUTPUT.PUT_LINE ('Course, '||                 course_rec.course_no||' has '||                 v_total||' sections');           END IF;        EXCEPTION           WHEN e_no_sections THEN              RAISE;        END;     END LOOP;  EXCEPTION     WHEN e_no_sections THEN        DBMS_OUTPUT.PUT_LINE ('There are no sections for '||           'the course');  END; 

In this version of the example, the exception-handling section of the inner block was modified. The DBMS_OUTPUT.PUT_LINE statement has been replaced by the RAISE statement. In addition, the exception-handling section was included in the outer block.

Notice that the error message has been modified as well. There is no course number displayed by the error message. This change is necessary because the exception-handling section of the outer block is located outside of the cursor FOR loop. Therefore, the course number is not visible by the exception. When run, this version produces the following output:

 Course, 10 has 1 sections  Course, 20 has 4 sections  Course, 25 has 9 sections  There are no sections for the course  PL/SQL procedure successfully completed. 

In order to produce the error message that contains the course number, the script should be modified as follows:

 -- ch10_4c.sql, version 3.0  SET SERVEROUTPUT ON  DECLARE     CURSOR course_cur IS        SELECT course_no           FROM course;     v_total NUMBER;     v_course_no NUMBER;     e_no_sections EXCEPTION;  BEGIN     FOR course_rec in course_cur LOOP        v_course_no := course_rec.course_no;        BEGIN           SELECT COUNT(*)             INTO v_total             FROM section            WHERE course_no = course_rec.course_no;          IF v_total = 0 THEN              RAISE e_no_sections;           ELSE              DBMS_OUTPUT.PUT_LINE ('Course, '||                 course_rec.course_no||' has '||v_total||                 ' sections');           END IF;        EXCEPTION           WHEN e_no_sections THEN              RAISE;        END;     END LOOP;  EXCEPTION     WHEN e_no_sections THEN        DBMS_OUTPUT.PUT_LINE ('There are no sections for '||           'the course '||v_course_no);  END; 

In this version of the example, there is a new variable, v_course_no, that holds the current course number. Notice that the assignment statement for this variable is the first executable statement of the cursor FOR loop. This arrangement guarantees that the variable will have a value assigned to it before the e_no_sections exception is raised. When run, the example produces the following output:

 Course, 10 has 1 sections  Course, 20 has 4 sections  Course, 25 has 9 sections  There are no sections for the course 80  PL/SQL procedure successfully completed. 

Lab 10.3 Self-Review Questions

In order to test your progress, you should be able to answer the following questions.

Answers appear in Appendix A, Section 10.3.

1)

When an exception is raised in the declaration section of the inner block, it propagates to the

  1. _____ exception-handling section of this block.

  2. _____ exception-handling section of the enclosing (outer) block.

  3. _____ host environment and causes a syntax error.

2)

When an exception is raised in the declaration section of the outer block, it propagates to the

  1. _____ exception-handling section of this block.

  2. _____ host environment and causes a syntax error.

3)

When an exception is raised in the executable section of the inner block, it propagates to the

  1. _____ exception-handling section of this block.

  2. _____ exception-handling section of the enclosing block.

  3. _____ host environment and causes a syntax error.

4)

When an exception is re-raised in the inner block, control is transferred to the

  1. _____ exception-handling section of this block.

  2. _____ exception-handling section of the enclosing block.

5)

To re-raise an exception, one must issue which of the following statements?

  1. _____ RAISE exception_name

  2. _____ RAISE

  3. _____ There is no need to issue any statements.


    Team-Fly    
    Top
     



    Oracle PL. SQL Interactive Workbook
    Oracle PL/SQL Interactive Workbook (2nd Edition)
    ISBN: 0130473200
    EAN: 2147483647
    Year: 2002
    Pages: 146

    Similar book on Amazon

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net