Answer: Your answer should look similar to the following. All changes are shown in bold letters.
SET SERVEROUTPUT ON SIZE 5000 DECLARE CURSOR section_cur IS SELECT section_id FROM section; v_total NUMBER; e_too_many_students EXCEPTION; BEGIN FOR section_rec in section_cur LOOP BEGIN -- calculate number of students enrolled SELECT COUNT(*) INTO v_total FROM enrollment WHERE section_id = section_rec.section_id; IF v_total >= 15 THEN RAISE e_too_many_students; ELSE DBMS_OUTPUT.PUT_LINE ('There are '||v_total|| ' students for '||section ID '|| section_rec.section_id); END IF; EXCEPTION WHEN e_too_many_students THEN RAISE; END; END LOOP; EXCEPTION WHEN e_too_many_students THEN DBMS_OUTPUT.PUT_LINE ('There are too many students.'); END;
In this exercise, the exception section of the inner has been modified. A DBMS_OUTPUT.PUT_LINE statement has been substituted with the RAISE statement. In addition, an exception section has been added to the outer block. As a result, when an exception is raised in the inner block, it propagates to the outer block, and the cursor loop terminates.
It is important to note that an error message displayed by the DBMS_OUTPUT. PUT_LINE statement must be changed when a E_TOO_MANY_STUDENTS exception is raised in the outer block. In the previous version of this exercise the error message
('There are too many students for '||section_rec.section_id);
was placed inside the body of the cursor FOR loop. If the same error message is placed outside the body of the cursor FOR loop, the following error is generated at runtime:
section_rec.section_id); * ERROR at line 31: ORA-06550: line 31, column 10: PLS-00201: identifier 'SECTION_REC.SECTION_ID' must be declared ORA-06550: line 30, column 7: PL/SQL: Statement ignored
Why do you think this error is generated?