Lab 10.2 User-Defined Exceptions

Team-Fly    

Oracle® PL/SQL® Interactive Workbook, Second Edition
By Benjamin Rosenzweig, Elena Silvestrova
Table of Contents
Chapter 10.  Exceptions


Lab Objectives

After this Lab, you will be able to:

  • Use 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.

graphics/intfig03.gif 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.

graphics/intfig03.gif 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.

graphics/intfig03.gif 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.

graphics/intfig07.gif

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.

graphics/intfig03.gif 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

  1. _____ declared.

  2. _____ declared and raised.

2)

How does any user-defined exception get raised?

  1. _____ Implicitly

  2. _____ Explicitly

3)

If a user-defined exception has been declared in the inner block, it can be raised in the outer block.

  1. _____ True

  2. _____ False

4)

When a user-defined exception is raised and executed, control is passed back to the PL/SQL block.

  1. _____ True

  2. _____ False

5

A user-defined exception is raised with the help of which of the following?

  1. _____ IF-THEN and RAISE statements

  2. _____ IF-THEN statement only

  3. _____ RAISE statement only


    Team-Fly    
    Top
     



    Oracle PL. SQL Interactive Workbook
    Oracle PL/SQL Interactive Workbook (2nd Edition)
    ISBN: 0130473200
    EAN: 2147483647
    Year: 2002
    Pages: 146

    Similar book on Amazon

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