Lab 10.3 Exercise Answers | Oracle PL[s]SQL by Example

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.

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 re-raised 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.