Lab 7.2 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.

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.

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.

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:

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.