Lab 7.2 Built-In Exceptions

Team-Fly    

Oracle® PL/SQL® Interactive Workbook, Second Edition
By Benjamin Rosenzweig, Elena Silvestrova
Table of Contents
Chapter 7.  Error Handling and Built-In Exceptions


Lab Objectives

After this Lab, you will be able to:

  • Use Built-In Exceptions

As mentioned earlier, a PL/SQL block has the following structure:

 DECLARE     …  BEGIN     EXECUTABLE STATEMENTS;  EXCEPTION     WHEN EXCEPTION_NAME THEN        ERROR-PROCESSING STATEMENTS;  END; 

When an error occurs that raises a built-in exception, the exception is said to be raised implicitly. In other words, if a program breaks an Oracle rule, control is passed to the exception-handling section of the block. At this point, the error-processing statements are executed. It is important for you to realize that after the exception-handling section of the block has executed, the block terminates. Control will not return to the executable section of the block. The following example illustrates this point.

graphics/intfig03.gif FOR EXAMPLE

 DECLARE     v_student_name VARCHAR2(50);  BEGIN     SELECT first_name||' '||last_name       INTO v_student_name       FROM student      WHERE student_id = 101;     DBMS_OUTPUT.PUT_LINE ('Student name is '||        v_student_name);  EXCEPTION     WHEN NO_DATA_FOUND THEN        DBMS_OUTPUT.PUT_LINE ('There is no such student');  END; 

This example produces the following output:

 There is no such student  PL/SQL procedure successfully completed. 

Because there is no record in the STUDENT table with student ID 101, the SELECT INTO statement does not return any rows. As a result, control passes to the exception-handling section of the block, and the error message "There is no such student" is displayed on the screen. Even though there is a DBMS_OUTPUT.PUT_LINE statement right after the SELECT statement, it will not be executed because control has been transferred to the exception-handling section. Control will never return to the executable section of this block, which contains the first DBMS_OUTPUT.PUT_LINE statement.

You have probably noticed that, while every Oracle runtime error has a number associated with it, it must be handled by its name in the exception-handling section. One of the outputs from the example used in the previous lab of this chapter has the following error message:

 ORA-01476: divisor is equal to zero 

where ORA-01476 stands for the error number. This error number refers to the error named ZERO_DIVIDE. Some common Oracle runtime errors are predefined in PL/SQL as exceptions.

The following list explains some commonly used predefined exceptions and how they are raised:

  • NO_DATA_FOUND This exception is raised when a SELECT INTO statement that makes no calls to group functions, such as SUM or COUNT, does not return any rows. For example, you issue a SELECT INTO statement against the STUDENT table where student ID equals 101. If there is no record in the STUDENT table passing this criteria (student ID equals 101), the NO_DATA_FOUND exception is raised.

    When a SELECT INTO statement calls a group function, such as COUNT, the result set is never empty. When used in a SELECT INTO statement against the STUDENT table, function COUNT will return 0 for the value of student ID 123. Hence, a SELECT statement that calls a group function will never raise the NO_DATA_FOUND exception.

  • TOO_MANY_ROWS This exception is raised when a SELECT INTO statement returns more than one row. By definition, a SELECT INTO can return only a single row. If a SELECT INTO statement returns more than one row, the definition of the SELECT INTO statement is violated. This causes the TOO_MANY_ROWS exception to be raised.

    For example, you issue a SELECT INTO statement against the STUDENT table for a specific zipcode. There is a big chance that this SELECT statement will return more than one row because many students can live in the same zipcode area.

  • ZERO_DIVIDE This exception is raised when a division operation is performed in the program and a divisor is equal to zero. An example in the previous lab of this chapter illustrates how this exception is raised.

  • LOGIN_DENIED This exception is raised when a user is trying to login to Oracle with an invalid username or password.

  • PROGRAM_ERROR This exception is raised when a PL/SQL program has an internal problem.

  • VALUE_ERROR This exception is raised when a conversion or size mismatch error occurs. For example, you select a student's last name into a variable that has been defined as VARCHAR2(5). If the student's last name contains more than five characters, the VALUE_ERROR exception is raised.

  • DUP_VALUE_ON_INDEX This exception is raised when a program tries to store a duplicate value in the column or columns that have a unique index defined on them. For example, you are trying to insert a record into the SECTION table for the course number "25," section 1. If a record for the given course and section number already exists in the SECTION table, the DUP_VAL_ON_INDEX exception is raised because these columns have a unique index defined on them.

So far, you have seen examples of programs able to handle a single exception only. For example, a PL/SQL block contains an exception handler with a single exception ZERO_DIVIDE. However, many times you need to handle different exceptions in the PL/SQL block. Moreover, often you need to specify different actions that must be taken when a particular exception is raised, as the following illustrates.

graphics/intfig03.gif FOR EXAMPLE

 DECLARE     v_student_id NUMBER := &sv_student_id;     v_enrolled VARCHAR2(3) := 'NO';  BEGIN     DBMS_OUTPUT.PUT_LINE        ('Check if the student is enrolled');     SELECT 'YES'       INTO v_enrolled       FROM enrollment      WHERE student_id = v_student_id;     DBMS_OUTPUT.PUT_LINE        ('The student is enrolled into one course');  EXCEPTION     WHEN NO_DATA_FOUND THEN        DBMS_OUTPUT.PUT_LINE ('The student is not enrolled');     WHEN TOO_MANY_ROWS THEN        DBMS_OUTPUT.PUT_LINE           ('The student is enrolled in too many courses');  END; 

Notice that this example contains two exceptions in a single exception-handling section. The first exception, NO_DATA_FOUND, will be raised if there are no records in the ENROLLMENT table for a particular student. The second exception, TOO_MANY_ROWS, will be raised if a particular student is enrolled in more than one course.

Consider what happens if you run this example for three different values of student ID: 102, 103, and 319.

The first run of the example (student ID is 102) produces the following output:

 Enter value for sv_student_id: 102  old   2:    v_student_id NUMBER := &sv_student_id;  new   2:    v_student_id NUMBER := 102;  Check if the student is enrolled  Student is enrolled in too many courses  PL/SQL procedure successfully completed. 

The first time, a user entered 102 for the value of student ID. Next, the first DBMS_ OUTPUT.PUT_LINE statement is executed, and the message "Check if the . . ." is displayed on the screen. Then the SELECT INTO statement is executed. You have probably noticed that the DBMS_OUTPUT.PUT_LINE statement following the SELECT INTO statement was not executed. When the SELECT INTO statement is executed for student ID 102, multiple rows are returned. Because the SELECT INTO statement can return only a single row, control is passed to the exception-handling section of the block. Next, the PL/SQL block raises the proper exception. As a result, the message "The student is enrolled into many courses" is displayed on the screen, and this message is specified by the exception TOO_MANY_ROWS.

graphics/intfig07.gif

It is important for you to note that built-in exceptions are raised implicitly. Therefore, you only need to specify what action must be taken in the case of a particular exception.


A second run of the example (student ID is 103) produces the following output:

 Enter value for sv_student_id: 103  old   2:    v_student_id NUMBER := &sv_student_id;  new   2:    v_student_id NUMBER := 103;  Check if the student is enrolled  The student is enrolled into one course  PL/SQL procedure successfully completed. 

In this second run, a user entered 103 for the value of student ID. As a result, the first DBMS_OUTPUT.PUT_LINE statement is executed, and the message "Check if the . . ." is displayed on the screen. Then the SELECT INTO statement is executed. When the SELECT INTO statement is executed for student ID 103, a single row is returned. Next, the DBMS_OUTPUT.PUT_LINE statement following the SELECT INTO statement is executed. As a result, the message "The student is enrolled into one course" is displayed on the screen. Notice that for this value of the variable v_student_id, no exception has been raised.

A third run of the example (student ID is 319) produces the following output:

 Enter value for sv_student_id: 319  old   2:    v_student_id NUMBER := &sv_student_id;  new   2:    v_student_id NUMBER := 319;  Check if the student is enrolled  The student is not enrolled  PL/SQL procedure successfully completed. 

This time, a user entered 319 for the value of student ID. The first DBMS_ OUTPUT.PUT_LINE statement is executed, and the message "Check if the . . ." is displayed on the screen. Then the SELECT INTO statement is executed. When the SELECT INTO statement is executed for student ID 319, no rows are returned. As a result, control is passed to the exception-handling section of the PL/SQL block, and the proper exception is raised. In this case, the NO_DATA_FOUND exception is raised because the SELECT INTO statement failed to return a single row. Thus, the message "The student is not enrolled" is displayed on the screen.

So far, you have seen examples of exception-handling sections that have particular exceptions, such as NO_DATA_FOUND or ZERO_DIVIDE. However, you cannot always predict beforehand what exception might be raised by your PL/SQL block. In cases like this, there is a special exception handler called OTHERS. All predefined Oracle errors (exceptions) can be handled with the use of the OTHERS handler.

Consider the following:

graphics/intfig03.gif FOR EXAMPLE

 DECLARE     v_instructor_id NUMBER := &sv_instructor_id;     v_instructor_name VARCHAR2(50);  BEGIN     SELECT first_name||' '||last_name       INTO v_instructor_name       FROM instructor      WHERE instructor_id = v_instructor_id;     DBMS_OUTPUT.PUT_LINE ('Instructor name is '||        v_instructor_name);  EXCEPTION     WHEN OTHERS THEN        DBMS_OUTPUT.PUT_LINE ('An error has occurred');  END; 

When run, this example produces the following output:

 Enter value for sv_instructor_id: 100  old   2:    v_instructor_id NUMBER := &sv_instructor_id;  new   2:    v_instructor_id NUMBER := 100;  An error has occurred  PL/SQL procedure successfully completed. 

This demonstrates not only the use of the OTHERS exception handler, but also a bad programming practice. The exception OTHERS has been raised because there is no record in the INSTRUCTOR table for instructor ID 100.

This is a simple example, where it is possible to guess what exception handlers should be used. However, in many instances you may find a number of programs that have been written with a single exception handler, OTHERS. This is a bad programming practice, because such use of this exception handler does not give you or your user good feedback. You do not really know what error has occurred. Your user does not know whether he or she entered some information incorrectly. There are special error-reporting functions, SQLCODE and SQLERRM, that are very useful when used with the OTHERS handler. You will learn about them in Chapter 11.

Lab 7.2 Exercise

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.

Lab 7.2 Exercise Answers

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

7.2.1 Answers

a)

What output was printed on the screen (for all values of zip)?

A1:

Answer: The first version of the output is produced when the value of zip is 07024. The second version of the output is produced when the value of zip is 00914. The third version of the output is produced when the value of zip is 12345.

Your output should look like the following:

 Enter value for sv_zip: 07024  old   4:    v_zip CHAR(5):= '&sv_zip';  new   4:    v_zip CHAR(5):= '07024';  There are 9 students  PL/SQL procedure successfully completed. 

When "07024" is entered for the variable v_zip, the first SELECT INTO statement is executed. This SELECT INTO statement checks whether the value of zip is valid, or, in other words, if a record exists in the ZIPCODE table for a given value of zip. Next, the value of the variable v_exists is evaluated with the help of the IF statement. For this run of the example, the IF statement evaluates to TRUE, and, as a result, the SELECT INTO statement against the STUDENT table is evaluated. Next, the DBMS_OUTPUT.PUT_LINE following the SELECT INTO statement is executed, and the message "There are 9 students" is displayed on the screen.

Your output should look like the following:

 Enter value for sv_zip: 00914  old   4:    v_zip CHAR(5):= '&sv_zip';  new   4:    v_zip CHAR(5):= '00914';  There are 0 students  PL/SQL procedure successfully completed. 

For the second run, the value 00914 is entered for the variable v_zip. The SELECT INTO statement against the STUDENT table returns one record, and the message "There are 0 students" is displayed on the screen.

Because the SELECT INTO statement against the STUDENT table uses a group function, COUNT, there is no reason to use the exception NO_DATA_FOUND, because the COUNT function will always return data.

Your output should look like the following:

 Enter value for sv_zip: 12345  old   4:    v_zip CHAR(5):= '&sv_zip';  new   4:    v_zip CHAR(5):= '12345';  12345 is not a valid zip  PL/SQL procedure successfully completed. 

For the third run, the value 12345 is entered for the variable v_zip. The SELECT INTO statement against the ZIPCODE table is executed. Next, the variable v_exists is evaluated with the help of the IF statement. Because the value of v_exists equals 0, the IF statement evaluates to FALSE. As a result, the ELSE part of the IF statement is executed. The message "12345 is not a valid zip" is displayed on the screen.

b)

Explain why no exception has been raised for these values of the variable v_zip.

A2:

Answer: The exceptions VALUE_ERROR or INVALID_NUMBER have not been raised because there was no conversion or type mismatch error. Both variables, v_exists and v_total_students, have been defined as NUMBER(1).

The group function COUNT used in the SELECT INTO statement returns a NUMBER datatype. Moreover, on both occasions, a single digit number is returned by the COUNT function. As a result, neither exception has been raised.

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?

A3:

Answer: After a student has been added, your output should look like the following:

 Enter value for sv_zip: 07024  old   4:    v_zip CHAR(5):= '&sv_zip';  new   4:    v_zip CHAR(5):= '07024';  An error has occurred  PL/SQL procedure successfully completed. 

Once the student has been inserted into the STUDENT table with a zip having a value of "07024," the total number of students changes to 10 (remember, previously this number was 9). As a result, the SELECT INTO statement against the STUDENT table causes an error, because the variable v_total_students has been defined as NUMBER(1). This means that only a single-digit number can be stored in this variable. The number 10 is a two-digit number, so the exception INVALID_NUMBER is raised. As a result, the message "An error has occurred" is displayed on the screen.

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.

A4:

Answer: The new version of your program should look similar to this program. All changes are shown in bold letters.

 -- ch07_2b.sql, version 2.0  SET SERVEROUTPUT ON  DECLARE     v_exists NUMBER(1);     v_student_name VARCHAR2(30);     v_zip CHAR(5):= '&sv_zip';  BEGIN     SELECT count(*)       INTO v_exists       FROM zipcode      WHERE zip = v_zip;     IF v_exists != 0 THEN        SELECT first_name||' '||last_name          INTO v_student_name          FROM student         WHERE zip = v_zip           AND rownum = 1;        DBMS_OUTPUT.PUT_LINE ('Student name is '||           v_student_name);     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');     WHEN NO_DATA_FOUND THEN        DBMS_OUTPUT.PUT_LINE           ('There are no students for this value of '||              'zip code');  END; 

This version of the program contains several changes. The variable v_total_students has been replaced by the variable v_student_name. The SELECT INTO statement against the STUDENT table has been changed as well. Another condition has been added to the WHERE clause:

 rownum = 1 

You have seen from the previous runs of this program that for any given value of zip there could be multiple records in the STUDENT table. Because a SELECT INTO statement returns only a single row, the condition rownum = 1 has been added to it. Another way to deal with multiple rows returned by the SELECT INTO statement is to add the exception TOO_MANY_ROWS.

Finally, another exception has been added to the program. The SELECT INTO statement against the STUDENT table does not contain any group functions. Therefore, for any given value of zip, the SELECT INTO statement may not return any data, and it causes an error. As a result, the exception NO_DATA_FOUND will be raised.

Lab 7.2 Self-Review Questions

In order to test your progress, you should be able to answer the following questions.

Answers appear in Appendix A, Section 7.2.

1)

How does a built-in exception get raised?

  1. _____ Implicitly

  2. _____ Explicitly

2)

An Oracle error, or exception, is referred to by its

  1. _____ Number.

  2. _____ Name.

  3. _____ Both.

3)

When a group function is used in the SELECT INTO statement, exception NO_DATA_FOUND is raised if there are no rows returned.

  1. _____ True

  2. _____ False

4)

When an exception is raised and executed, control is passed back to the PL/SQL block.

  1. _____ True

  2. _____ False

5)

An exception-handling section of a PL/SQL block may contain a single exception handler 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

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