Lab 10.2 User-Defined Exceptions
Lab 10.2 User -Defined Exceptions
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. 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. 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 . 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.
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. -- 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.  |
Use User-Defined Exceptions 
![Oracle PL[s]SQL by Example](/icons/blank_book.jpg "Oracle PL[s]SQL by Example"){kind=icon}