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:
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.
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. |