Lab 10.3 Exercises10.3.1 Understand How Exceptions PropagateIn 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:
10.3.2 Re-raise ExceptionsIn 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:
|