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 re-raised in the outer block.