Lab 10.1 Exercise Answers

This section gives you some suggested answers to the questions in Lab 10.1, 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.1.1 Answers

a)	What output was printed on the screen?
A1:	Answer: Your output should look like the following: There is(are) 1 student(s) for zipcode 01247 There is(are) 1 student(s) for zipcode 02124 There is(are) 1 student(s) for zipcode 02155 There is(are) 1 student(s) for zipcode 02189 There is(are) 1 student(s) for zipcode 02563 There is(are) 1 student(s) for zipcode 06483 There is(are) 1 student(s) for zipcode 06605 There is(are) 1 student(s) for zipcode 06798 There is(are) 3 student(s) for zipcode 06820 There is(are) 3 student(s) for zipcode 06830 There is(are) 1 student(s) for zipcode 06850 There is(are) 1 student(s) for zipcode 06851 There is(are) 1 student(s) for zipcode 06853 There is(are) 1 student(s) for zipcode 06870 There is(are) 1 student(s) for zipcode 06877 There is(are) 2 student(s) for zipcode 06880 There is(are) 1 student(s) for zipcode 06902 There is(are) 2 student(s) for zipcode 06903 There is(are) 1 student(s) for zipcode 06905 There is(are) 1 student(s) for zipcode 06907 There is(are) 2 student(s) for zipcode 07003 There is(are) 1 student(s) for zipcode 07008 There is(are) 6 student(s) for zipcode 07010 There is(are) 2 student(s) for zipcode 07011 There is(are) 2 student(s) for zipcode 07012 There is(are) 2 student(s) for zipcode 07016 There is(are) 1 student(s) for zipcode 07023 There is(are) 9 student(s) for zipcode 07024 There is(are) 1 student(s) for zipcode 07029 There is(are) 2 student(s) for zipcode 07036 There is(are) 1 student(s) for zipcode 07040 There is(are) 5 student(s) for zipcode 07042 There is(are) 1 student(s) for zipcode 07044 There is(are) 5 student(s) for zipcode 07047 Done PL/SQL procedure successfully completed.
b)	The first run of this example was successful. The output produced by the example shows that there are 9 students for zipcode 07024. What will happen if there are 10 students with a zip code 07024? What output will be produced? Note that in order to answer this question you will need to add a record to the STUDENT table as follows : INSERT INTO student (student_id, salutation, first_name, last_name, street_address, zip, phone, employer, registration_date, created_by, created_date, modified_by, modified_date) VALUES (STUDENT_ID_SEQ.NEXTVAL, 'Mr.', 'John', 'Smith', '100 Main St.', '07024', '718-555-5555', 'ABC Co.', SYSDATE, USER, SYSDATE, USER, SYSDATE);
A2:	Answer: The example will produce a partial output only. When the total number of students is calculated for zipcode 07024, the error occurs.

The SELECT INTO statement returns a value of 10. However, the variable v_total has been defined so that it is able to hold only single digit numbers . Because 10 is a two-digit number, the error occurs during the execution of the SELECT INTO statement. As a result, an error message is displayed on the screen.

The following output contains only a portion of the output produced by the example:

  There is(are) 1 student(s) for zipcode 01247     There is(are) 1 student(s) for zipcode 07023   DECLARE   *   ERROR at line 1:   ORA-06502: PL/SQL: numeric or value error: number   precision too large   ORA-06512: at line 13  

Notice that as soon as the error occurs, the example terminates because there is no exception handler for this error.

c)

Based on the error message produced by the example in the previous question, what exception handler must be added to the script?

A3:

Answer: The error message produced by the example in the previous question referred to a numeric or value error. Therefore, an exception VALUE_ERROR or INVALID_NUMBER must be added to the script. Your script should look similar to the following script. Changes are shown in bold letters . -- ch10_1b.sql, version 2.0 DECLARE CURSOR zip_cur IS SELECT zip FROM zipcode WHERE rownum <= 50 ORDER BY zip; v_total NUMBER(1); -- outer block BEGIN FOR zip_rec IN zip_cur LOOP -- inner block BEGIN SELECT count(*) INTO v_total FROM student WHERE zip = zip_rec.zip; IF v_total != 0 THEN DBMS_OUTPUT.PUT_LINE ('There is(are) ' v_total' student(s) for zipcode ' zip_rec.zip); END IF; END; END LOOP; DBMS_OUTPUT.PUT_LINE ('Done...'); EXCEPTION WHEN VALUE_ERROR OR INVALID_NUMBER THEN DBMS_OUTPUT.PUT_LINE ('An error has occurred'); END;

When run, this version of the example produces the following output (only a portion of the output is shown):

  There is(are) 1 student(s) for zipcode 01247     There is(are) 1 student(s) for zipcode 07023   An error has occurred   PL/SQL procedure successfully completed.  

Notice that because an exception handler has been added to the script, it was able to terminate successfully.

d)

How would you change this script so that when an error occurs, the cursor loop does not terminate prematurely?

A4:

Answer: Your script should look similar to the script shown. All changes are shown in bold letters. -- ch10_1c.sql, version 3.0 DECLARE CURSOR zip_cur IS SELECT zip FROM zipcode WHERE rownum <= 50 ORDER BY zip; v_total NUMBER(1); -- outer block BEGIN FOR zip_rec IN zip_cur LOOP -- inner block BEGIN SELECT count(*) INTO v_total FROM student WHERE zip = zip_rec.zip; IF v_total != 0 THEN DBMS_OUTPUT.PUT_LINE ('There is(are) ' v_total' student(s) for zipcode ' zip_rec.zip); END IF; EXCEPTION WHEN VALUE_ERROR OR INVALID_NUMBER THEN DBMS_OUTPUT.PUT_LINE ('An error has occurred'); END; END LOOP; DBMS_OUTPUT.PUT_LINE ('Done...'); END;

In order for the cursor loop to be able to execute after an exception has occurred, the exception handler must be moved inside the loop in the inner block. In this case, once an exception has occurred, control is transferred to the exception handler of the block. Once the exception is raised, control is passed to the next executable statement of the outer block. That statement is END LOOP. If the end of the loop has not been reached and there are more records to process, control is passed to the top of the loop, and the inner block is executed again. As a result, this version of the script produces the following output (again, only a portion of the output is shown):

  There is(are) 1 student(s) for zipcode 01247     There is(are) 1 student(s) for zipcode 07023   An error has occurred   There is(are) 1 student(s) for zipcode 07029   There is(are) 2 student(s) for zipcode 07036   There is(are) 1 student(s) for zipcode 07040   There is(are) 5 student(s) for zipcode 07042   There is(are) 1 student(s) for zipcode 07044   There is(are) 5 student(s) for zipcode 07047   Done...   PL/SQL procedure successfully completed.