| Lab Objectives After this Lab, you will be able to: 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
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 _____ exception-handling section of this block. _____ exception-handling section of the enclosing (outer) block. _____ 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 _____ exception-handling section of this block. _____ 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 _____ exception-handling section of this block. _____ exception-handling section of the enclosing block. _____ host environment and causes a syntax error.
| | 4) | When an exception is re-raised in the inner block, control is transferred to the _____ exception-handling section of this block. _____ exception-handling section of the enclosing block.
| | 5) | To re-raise an exception, one must issue which of the following statements? _____ RAISE exception_name _____ RAISE _____ There is no need to issue any statements.
|
|
