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: