Lab 7.2 Exercises

7.2.1 Use Built-In Exceptions

In this exercise, you will learn more about some built-in exceptions discussed earlier in the chapter.

Create the following PL/SQL script:

 -- ch07_2a.sql, version 1.0 SET SERVEROUTPUT ON DECLARE    v_exists NUMBER(1);    v_total_students NUMBER(1);    v_zip CHAR(5):= '&sv_zip'; BEGIN    SELECT count(*)      INTO v_exists      FROM zipcode     WHERE zip = v_zip;    IF v_exists != 0 THEN       SELECT COUNT(*)         INTO v_total_students         FROM student        WHERE zip = v_zip;       DBMS_OUTPUT.PUT_LINE          ('There are 'v_total_students' students');    ELSE       DBMS_OUTPUT.PUT_LINE (v_zip' is not a valid zip');    END IF; EXCEPTION    WHEN VALUE_ERROR OR INVALID_NUMBER THEN       DBMS_OUTPUT.PUT_LINE ('An error has occurred'); END; 

This script contains two exceptions, VALUE_ERROR and INVALID_NUMBER. However, only one exception handler is written for both exceptions. You can combine different exceptions in a single exception handler when you want to handle both exceptions in a similar way. Often the exceptions VALUE_ERROR and INVALID_NUMBER are used in a single exception handler because these Oracle errors refer to the conversion problems that may occur at runtime.

In order to test this script fully, execute it three times. For the first run, enter "07024," for the second run, enter "00914," and for the third run, enter "12345" for the variable v_zip . Execute the script, and then answer the following questions:

a)	What output was printed on the screen (for all values of zip)?
b)	Explain why no exception has been raised for these values of the variable v_zip .
c)	Insert a record into the STUDENT table with a zip having the value of "07024." INSERT INTO student (student_id, salutation, first_name, last_name, zip, registration_date, created_by, created_date, modified_by, modified_date) VALUES (STUDENT_ID_SEQ.NEXTVAL, 'Mr.', 'John', 'Smith', '07024', SYSDATE, 'STUDENT', SYSDATE, 'STUDENT', SYSDATE); Run the script again for the same value of zip ("07024"). What output was printed on the screen? Why?
d)	How would you change the script to display a student's first name and last name instead of displaying the total number of students for any given value of a zip? Remember, only one record can be returned by a SELECT INTO statement.