| Lab Objectives After this Lab, you will be able to: You are already familiar with the term scopefor example, the scope of a variable. Even though variables and exceptions serve different purposes, the same scope rules apply to them. Now examine the scope of an exception by means of an example.  FOR EXAMPLE DECLARE v_student_id NUMBER := &sv_student_id; v_name VARCHAR2(30); BEGIN SELECT RTRIM(first_name)||' '||RTRIM(last_name) INTO v_name FROM student WHERE student_id = v_student_id; DBMS_OUTPUT.PUT_LINE ('Student name is '||v_name); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('There is no such student'); END; In this example, you display the student's name on the screen. If there is no record in the STUDENT table corresponding to the value of v_student_id provided by the user, the exception NO_DATA_FOUND is raised. Therefore, you can say that the exception NO_DATA_FOUND covers this block, or this block is the scope of this exception. In other words, the scope of an exception is the portion of the block that is covered by this exception. Now, you can expand on that: FOR EXAMPLE DECLARE v_student_id NUMBER := &sv_student_id; v_name VARCHAR2(30); v_total NUMBER(1); -- outer block BEGIN SELECT RTRIM(first_name)||' '||RTRIM(last_name) INTO v_name FROM student WHERE student_id = v_student_id; DBMS_OUTPUT.PUT_LINE ('Student name is '||v_name); -- inner block BEGIN SELECT COUNT(*) INTO v_total FROM enrollment WHERE student_id = v_student_id; DBMS_OUTPUT.PUT_LINE ('Student is registered for '|| v_total||' course(s)'); EXCEPTION WHEN VALUE_ERROR OR INVALID_NUMBER THEN DBMS_OUTPUT.PUT_LINE ('An error has occurred'); END; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('There is no such student'); END; The part of the example shown in bold letters has been added to the original version of the example. The new version of the example has an inner block added to it. This block has a structure similar to the outer block. It has a SELECT INTO statement and an exception section to handle errors. When a VALUE_ERROR or an INVALID_NUMBER error occurs in the inner block, the exception is raised. It is important that you realize that the exceptions VALUE_ERROR and INVALID_ NUMBER have been defined for the inner block only. Therefore, they can be raised in the inner block only. If one of these errors occurs in the outer block, this program will be unable to terminate successfully. On the other hand, the exception NO_DATA_FOUND has been defined in the outer block; therefore, it is global to the inner block. This version of the example will never raise the exception NO_DATA_FOUND in the inner block. Why do you think this is the case?  | It is important to note that if you define an exception in a block, it is local to that block. However, it is global to any blocks enclosed by that block. In other words, in the case of nested blocks, any exception defined in the outer block becomes global to its inner blocks. |
Note what happens when the example is changed so that the exception NO_DATA_FOUND can be raised by the inner block. FOR EXAMPLE DECLARE v_student_id NUMBER := &sv_student_id; v_name VARCHAR2(30); v_registered CHAR; -- outer block BEGIN SELECT RTRIM(first_name)||' '||RTRIM(last_name) INTO v_name FROM student WHERE student_id = v_student_id; DBMS_OUTPUT.PUT_LINE ('Student name is '||v_name); -- inner block BEGIN SELECT 'Y' INTO v_registered FROM enrollment WHERE student_id = v_student_id; DBMS_OUTPUT.PUT_LINE ('Student is registered'); EXCEPTION WHEN VALUE_ERROR OR INVALID_NUMBER THEN DBMS_OUTPUT.PUT_LINE ('An error has occurred'); END; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('There is no such student'); END; The part of the example shown in bold letters has been added to the original version of the example. The new version of the example has a different SELECT INTO statement. To answer the question posed earlier, the exception NO_DATA_FOUND can be raised by the inner block because the SELECT INTO statement does not contain a group function, COUNT(). This function always returns a result, so when no rows are returned by the SELECT INTO statement, the value returned by the COUNT(*) equals zero. Now, run this example with the value of 284 for student ID. As a result, the following output is produced: Enter value for sv_student_id: 284 old 2: v_student_id NUMBER := &sv_student_id; new 2: v_student_id NUMBER := 284; Student name is Salewa Lindeman There is no such student PL/SQL procedure successfully completed. You have probably noticed that this example produces only a partial output. Even though you are able to see the student's name, the error message is displayed saying that this student does not exist. This error message is displayed because the exception NO_DATA_FOUND is raised in the inner block. The SELECT INTO statement of the outer block returns the student's name, and it is displayed on the screen by the DBMS_OUTPUT.PUT_LINE statement. Next, control is passed to the inner block. The SELECT INTO statement of the inner block does not return any rows. As a result, the error occurs. Next, PL/SQL tries to find a handler for the exception NO_DATA_FOUND in the inner block. Because there is no such handler in the inner block, control is transferred to the exception section of the outer block. The exception section of the outer block contains the handler for the exception NO_DATA_FOUND. So this handler executes, and the message "There is no such student" is displayed on the screen. The process is called exception propagation, and it will be discussed in detail in Lab 10.3. It is important to realize that this example has been shown for illustrative purposes only. In its current version, it is not very useful. The SELECT INTO statement of the inner block is prone to another exception, TOO_MANY_ROWS, that is not handled by this example. In addition, the error message "There is no such student" is not very descriptive when the exception NO_DATA_FOUND is raised by the inner block. Lab 10.1 Exercises 10.1.1 Understand the Scope of an Exception In this exercise, you will display the number of students in each zipcode (you still use the first 50 zipcodes only). You will use nested PL/SQL blocks to achieve the desired results. The original PL/SQL script will not contain any exception handlers. Therefore, you will be asked to identify possible errors that may occur and define exception handlers for them. Create the following PL/SQL script: -- ch10_1a.sql, version 1.0 SET SERVEROUTPUT ON 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…'); END; Execute the script, and then answer the following questions: | a) | What output was printed on the screen? | | 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); | | c) | Based on the error message produced by the example in the previous question, what exception handler must be added to the script? | | d) | How would you change this script so that when an error occurs, the cursor loop does not terminate prematurely? |
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. |
LAB 10.1 Self-Review Questions In order to test your progress, you should be able to answer the following questions. Answers appear in Appendix A, Section 10.1. | 1) | An exception defined in the inner block can be raised in _____ both inner and outer blocks. _____ the outer block only. _____ the inner block only.
| | 2) | If an exception has been raised in the inner block and has been handled in the outer block, control is transferred back to inner block for further execution of the script. _____ True _____ False
| | 3) | If an exception has been raised in the outer block, and its handler is defined in the inner block, which of the following will occur? _____ Control will be passed to the inner block to handle the raised exception. _____ The script will terminate due to an exception that is not handled.
| | 4) | An exception defined inside the body of the loop _____ terminates this loop after it has been raised. _____ allows the loop to proceed with next iteration. _____ causes an error.
| | 5) | A WHEN clause of the exception-handling section of a PL/SQL block can reference a single exception only. _____ True _____ False
|
|
