Lab 10.1 Exception Scope

Team-Fly    

Oracle® PL/SQL® Interactive Workbook, Second Edition
By Benjamin Rosenzweig, Elena Silvestrova
Table of Contents
Chapter 10.  Exceptions


Lab Objectives

After this Lab, you will be able to:

  • Understand the Scope of an Exception

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.

graphics/intfig03.gif 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:

graphics/intfig03.gif 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?

graphics/intfig07.gif

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.

graphics/intfig03.gif 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

  1. _____ both inner and outer blocks.

  2. _____ the outer block only.

  3. _____ 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.

  1. _____ True

  2. _____ 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?

  1. _____ Control will be passed to the inner block to handle the raised exception.

  2. _____ The script will terminate due to an exception that is not handled.

4)

An exception defined inside the body of the loop

  1. _____ terminates this loop after it has been raised.

  2. _____ allows the loop to proceed with next iteration.

  3. _____ causes an error.

5)

A WHEN clause of the exception-handling section of a PL/SQL block can reference a single exception only.

  1. _____ True

  2. _____ False


    Team-Fly    
    Top
     



    Oracle PL. SQL Interactive Workbook
    Oracle PL/SQL Interactive Workbook (2nd Edition)
    ISBN: 0130473200
    EAN: 2147483647
    Year: 2002
    Pages: 146

    Similar book on Amazon

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net