| Lab Objectives After this Lab, you will be able to: Often in your programs you may need to handle problems that are specific to the program you write. For example, your program asks a user to enter a value for student_id. This value is then assigned to the variable v_student_id that is used later in the program. Generally, you want a positive number for an id. By mistake, the user enters a negative number. However, no error has occurred because student_id has been defined as a number, and the user has supplied a legitimate numeric value. Therefore, you may want to implement your own exception to handle this situation. This type of an exception is called a user-defined exception because it is defined by the programmer. As a result, before the exception can be used, it must be declared. A user-defined exception is declared in the declarative part of a PL/SQL block as shown: DECLARE exception_name EXCEPTION; Notice that this declaration looks similar to a variable declaration. You specify an exception name followed by the keyword EXCEPTION. Consider the following code fragment.  FOR EXAMPLE DECLARE e_invalid_id EXCEPTION; In the example, the name of the exception is prefixed by the letter "e." This is not a required syntax; rather, it allows you to differentiate between variable names and exception names. Once an exception has been declared, the executable statements associated with this exception are specified in the exception-handling section of the block. The format of the exception-handling section is the same as for built-in exceptions. Consider the following code fragment. FOR EXAMPLE DECLARE e_invalid_id EXCEPTION; BEGIN … EXCEPTION WHEN e_invalid_id THEN DBMS_OUTPUT.PUT_LINE ('An id cannot be negative'); END; You already know that built-in exceptions are raised implicitly. In other words, when a certain error occurs, a built-in exception associated with this error is raised. Of course, you are assuming that you have included this exception in the exception-handling section of your program. For example, a TOO_MANY_ROWS exception is raised when a SELECT INTO statement returns multiple rows. Next, you will explore how a user-defined exception is raised. A user-defined exception must be raised explicitly. In other words, you need to specify in your program under which circumstances an exception must be raised, as shown: DECLARE exception_name EXCEPTION; BEGIN … IF CONDITION THEN RAISE exception_name; ELSE … END IF; EXCEPTION WHEN exception_name THEN ERROR-PROCESSING STATEMENTS; END; In the structure just shown, the circumstances under which a user-defined exception must be raised are determined with the help of the IF-THEN-ELSE statement. If CONDITION evaluates to TRUE, a user-defined exception is raised. If CONDITION evaluates to FALSE, the program proceeds with its normal execution. In other words, the statements associated with the ELSE part of the IF-THEN-ELSE statement are executed. Any form of the IF statement can be used to check when a user-defined exception must be raised. In the next modified version of the earlier example used in this lab, you will see that the exception e_invalid_id is raised when a negative number is entered for the variable v_student_id. FOR EXAMPLE DECLARE v_student_id STUDENT.STUDENT_ID%TYPE := &sv_student_id; v_total_courses NUMBER; e_invalid_id EXCEPTION; BEGIN IF v_student_id < 0 THEN RAISE e_invalid_id; ELSE SELECT COUNT(*) INTO v_total_courses FROM enrollment WHERE student_id = v_student_id; DBMS_OUTPUT.PUT_LINE ('The student is registered for '||v_total_courses||' courses'); END IF; DBMS_OUTPUT.PUT_LINE ('No exception has been raised'); EXCEPTION WHEN e_invalid_id THEN DBMS_OUTPUT.PUT_LINE ('An id cannot be negative'); END; In this example, the exception e_invalid_id is raised with the help of IF-THEN-ELSE statement. Once a user supplies a value for the v_student_id, the sign of this numeric value is checked. If the value is less than zero, the IF-THEN-ELSE statement evaluates to TRUE, and the exception e_invalid_id is raised. Therefore, the control transfers to the exception-handling section of the block. Next, statements associated with this exception are executed. In this case, the message "An id cannot be negative" is displayed on the screen. If the value entered for the v_student_id is positive, the IF-THEN-ELSE statement yields FALSE, and the ELSE part of the IF-THEN-ELSE statement is executed. Run this example for two values of v_student_id: 102 and -102. A first run of the example (student ID is 102) produces the output shown: Enter value for sv_student_id: 102 old 2: v_student_id STUDENT.STUDENT_ID%TYPE := &sv_student_id; new 2: v_student_id STUDENT.STUDENT_ID%TYPE := 102; The student is registered for 2 courses No exception has been raised PL/SQL procedure successfully completed. For this run, you entered a positive value for the variable v_student_id. As a result, the IF-THEN-ELSE statement evaluates to FALSE, and the ELSE part of the statement executes. The SELECT INTO statement determines how many records are in the ENROLLMENT table for a given student_id. Next, the message "The student is registered for 2 courses" statement is displayed on the screen. At this point, the IF-THEN-ELSE statement is complete. So the control is transferred to the DBMS_OUTPUT.PUT_LINE statement that follows END IF. As a result, another message is displayed on the screen. A second run of the example (student ID is -102) produces the following output: Enter value for sv_student_id: -102 old 2: v_student_id STUDENT.STUDENT_ID%TYPE := &sv_student_id; new 2: v_student_id STUDENT.STUDENT_ID%TYPE := -102; An id cannot be negative PL/SQL procedure successfully completed. For the second run, a negative value was entered for the variable v_student_id. The IF-THEN-ELSE statement evaluates to TRUE, and the exception e_invalid_id is raised. As a result, control is transferred to the exception-handling section of the block, and the error message "An id cannot be negative" is displayed on the screen.  | It is important for you to note that the RAISE statement must be used in conjunction with an IF statement. Otherwise, control of the execution will be transferred to the exception-handling section of the block for every single execution. Consider the following example: |
DECLARE e_test_exception EXCEPTION; BEGIN DBMS_OUTPUT.PUT_LINE ('Exception has not been raised'); RAISE e_test_exception; DBMS_OUTPUT.PUT_LINE ('Exception has been raised'); EXCEPTION WHEN e_test_exception THEN DBMS_OUTPUT.PUT_LINE ('An error has occurred'); END; Every time this example is run, the following output is produced: Exception has not been raised An error has occurred PL/SQL procedure successfully completed. Even though no error has occurred, control is transferred to the exception-handling section. It is important for you to check to see if the error has occurred before raising the exception associated with that error.
Just like for built-in exceptions, the same scope rules apply to user-defined exceptions. An exception declared in the inner block must be raised in the inner block and defined in the exception-handling section of the inner block. Consider the following example. FOR EXAMPLE -- outer block BEGIN DBMS_OUTPUT.PUT_LINE ('Outer block'); -- inner block DECLARE e_my_exception EXCEPTION; BEGIN DBMS_OUTPUT.PUT_LINE ('Inner block'); EXCEPTION WHEN e_my_exception THEN DBMS_OUTPUT.PUT_LINE ('An error has occurred'); END; IF 10 > &sv_number THEN RAISE e_my_exception; END IF; END; In this example, the exception, e_my_exception, has been declared in the inner block. However, you are trying to raise this exception in the outer block. This example causes a syntax error because the exception declared in the inner block ceases to exists once the inner block terminates. As a result, this example produces the following output: Enter value for sv_number: 11 old 12: IF 10 > &sv_number THEN new 12: IF 10 > 11 THEN RAISE e_my_exception; * ERROR at line 13: ORA-06550: line 13, column 13: PLS-00201: identifier 'E_MY_EXCEPTION' must be declared ORA-06550: line 13, column 7: PL/SQL: Statement ignored Notice that the error message PLS-00201: identifier 'E_MY_EXCEPTION' must be declared is the same error message you get when trying to use a variable that has not been declared. Lab 10.2 Exercises 10.2.1 Use User-Defined Exceptions In this exercise, you will define an exception that will allow you to raise an error if an instructor teaches ten or more sections. Create the following PL/SQL script: -- ch10_2a.sql, version 1.0 SET SERVEROUTPUT ON DECLARE CURSOR instruct_cur IS SELECT instructor_id, COUNT(*) tot_sec FROM section GROUP BY instructor_id; v_name VARCHAR2(30); e_too_many_sections EXCEPTION; BEGIN FOR instruct_rec IN instruct_cur LOOP IF instruct_rec.tot_sec >= 10 THEN RAISE e_too_many_sections; ELSE SELECT RTRIM(first_name)||' '||RTRIM(last_name) INTO v_name FROM instructor WHERE instructor_id = instruct_rec.instructor_id; DBMS_OUTPUT.PUT_LINE ('Instructor, '||v_name|| ', teaches '|| instruct_rec.tot_sec|| ' sections'); END IF; END LOOP; EXCEPTION WHEN e_too_many_sections THEN DBMS_OUTPUT.PUT_LINE ('This instructor teaches too much'); END; Execute the script, and then answer the following questions: | a) | What output was printed on the screen? | | b) | What is the condition that causes the user-defined exception to be raised? | | c) | How would you change the script so that the cursor FOR loop processes all records returned by the cursor? In other words, once an exception is raised, the cursor FOR loop should not terminate. | | d) | How would you change the script to display an instructor's name in the error message as well? |
Lab 10.2 Exercise Answers This section gives you some suggested answers to the questions in Lab 10.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. 10.2.1 Answers| a) | What output was printed on the screen? | | A1: | Answer: Your output should look like the following: Instructor, Fernand Hanks, teaches 9 sections This instructor teaches too much PL/SQL procedure successfully completed. | | b) | What is the condition that causes the user-defined exception to be raised? | | A2: | Answer: The user-defined exception is raised if the condition instruct_rec.tot_sec >= 10 evaluates to TRUE. In other words, if an instructor teaches ten or more sections, the exception e_too_many_sections is raised. | | c) | How would you change the script so that the cursor FOR loop processes all records returned by the cursor? In other words, once an exception is raised, the cursor FOR loop should not terminate. | | A3: | Answer: Your script should look similar to the script shown. All changes are shown in bold letters. -- ch10_2b.sql, version 2.0 SET SERVEROUTPUT ON DECLARE CURSOR instruct_cur IS SELECT instructor_id, COUNT(*) tot_sec FROM section GROUP BY instructor_id; v_name VARCHAR2(30); e_too_many_sections EXCEPTION; BEGIN FOR instruct_rec IN instruct_cur LOOP -- inner block BEGIN IF instruct_rec.tot_sec >= 10 THEN RAISE e_too_many_sections; ELSE SELECT RTRIM(first_name)||' '||RTRIM(last_name) INTO v_name FROM instructor WHERE instructor_id = instruct_rec. instructor_id; DBMS_OUTPUT.PUT_LINE ('Instructor, '||v_name|| ', teaches '||instruct_rec.tot_sec|| ' sections'); END IF; EXCEPTION WHEN e_too_many_sections THEN DBMS_OUTPUT.PUT_LINE ('This instructor teaches too much'); END; -- end inner block END LOOP; END; There are several changes in the new version of this script. First, the inner block has been created inside the body of the cursor FOR loop. Next, the exception-handling section has been moved from the outer block to the inner block. In this script, the exception has been declared in the outer block, but it is raised in the inner block. This does not cause any errors because the exception, e_too_many_sections, is global to the inner block. Hence, it can be raised anywhere in the inner block. The new version of this script produces the output shown: Instructor, Fernand Hanks, teaches 9 sections This instructor teaches too much This instructor teaches too much This instructor teaches too much This instructor teaches too much This instructor teaches too much This instructor teaches too much Instructor, Charles Lowry, teaches 9 sections PL/SQL procedure successfully completed. | | d) | How would you change the script to display an instructor's name in the error message as well? | | A4: | Answer: Your script should look similar to the script shown. All changes are shown in bold letters. -- ch10_2c.sql, version 3.0 SET SERVEROUTPUT ON DECLARE CURSOR instruct_cur IS SELECT instructor_id, COUNT(*) tot_sec FROM section GROUP BY instructor_id; v_name VARCHAR2(30); e_too_many_sections EXCEPTION; BEGIN FOR instruct_rec IN instruct_cur LOOP BEGIN SELECT RTRIM(first_name)||' '||RTRIM(last_name) INTO v_name FROM instructor WHERE instructor_id = instruct_rec.instructor_id; IF instruct_rec.tot_sec >= 10 THEN RAISE e_too_many_sections; ELSE DBMS_OUTPUT.PUT_LINE ('Instructor, '||v_name|| ', teaches '||instruct_rec.tot_sec|| ' sections'); END IF; EXCEPTION WHEN e_too_many_sections THEN DBMS_OUTPUT.PUT_LINE ('Instructor, '||v_name|| ', teaches too much'); END; END LOOP; END; In order to achieve the desired result, the SELECT INTO statement has been moved outside the IF-THEN-ELSE statement. This change allows you to get an instructor's name regardless of the number of sections he or she teaches. As a result, you are able to include an instructor's name in the error message, thus improving the error message itself. The new version of the output is shown: Instructor, Fernand Hanks, teaches 9 sections Instructor, Tom Wojick, teaches too much Instructor, Nina Schorin, teaches too much Instructor, Gary Pertez, teaches too much Instructor, Anita Morris, teaches too much Instructor, Todd Smythe, teaches too much Instructor, Marilyn Frantzen, teaches too much Instructor, Charles Lowry, teaches 9 sections PL/SQL procedure successfully completed. This version of the output is oriented more toward a user than the previous versions because it displays the name of the instructor in every message. The previous versions of the output were confusing because it was not clear which instructor caused this error. For example, consider the output produced by the first version of this script: Instructor, Fernand Hanks, teaches 9 sections This instructor teaches too much It is not clear to a user whether the message "This instructor teaches too much" is caused by the fact that Fernand Hanks teaches nine sections, or whether another instructor teaches more than nine sections. Remember, you have created this script, and you know the exception that you have defined. However, as mentioned earlier, most of the time, a user does not have access to your program. Therefore, it is important for you to provide clear error messages in your programs. |
Lab 10.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 10.2. | 1) | In order to use a user-defined exception, it must be _____ declared. _____ declared and raised.
| | 2) | How does any user-defined exception get raised? _____ Implicitly _____ Explicitly
| | 3) | If a user-defined exception has been declared in the inner block, it can be raised in the outer block. _____ True _____ False
| | 4) | When a user-defined exception is raised and executed, control is passed back to the PL/SQL block. _____ True _____ False
| | 5 | A user-defined exception is raised with the help of which of the following? _____ IF-THEN and RAISE statements _____ IF-THEN statement only _____ RAISE statement only
|
|
