Lab 7.1 Handling Errors

Team-Fly    

Oracle® PL/SQL® Interactive Workbook, Second Edition
By Benjamin Rosenzweig, Elena Silvestrova
Table of Contents
Chapter 7.  Error Handling and Built-In Exceptions


Lab Objectives

After this Lab, you will be able to:

  • Understand the Importance of Error Handling

The following example will help to illustrate some of the differences between compilation and runtime errors.

graphics/intfig03.gif FOR EXAMPLE

 DECLARE     v_num1 INTEGER := &sv_num1;     v_num2 INTEGER := &sv_num2;     v_result NUMBER;  BEGIN     v_result = v_num1 / v_num2;     DBMS_OUTPUT.PUT_LINE ('v_result: '||v_result);  END; 

This example is a very simple program. There are two variables, v_num1 and v_num2. A user supplies values for these variables. Next, v_num1 is divided by v_num2, and the result of this division is stored in the third variable, v_result. Finally, the value of v_result is displayed on the screen.

Now, assume that a user supplies values of 3 and 5 for the variables, v_num1 and v_num2, respectively. As a result, the example produces the following output:

 Enter value for sv_num1: 3  old   2:    v_num1 integer := &sv_num1;  new   2:    v_num1 integer := 3;  Enter value for sv_num2: 5  old   3:    v_num2 integer := &sv_num2;  new   3:    v_num2 integer := 5;     v_result = v_num1 / v_num2;              *  ERROR at line 6:  ORA-06550: line 6, column 13:  PLS-00103: Encountered the symbol "=" when expecting one  of the following:  := . ( @ % ;  ORA-06550: line 7, column 4:  PLS-00103: Encountered the symbol "DBMS_OUTPUT"  ORA-06550: line 7, column 49:  PLS-00103: Encountered the symbol ";" when expecting one  of the following:  . ( * % & - + / mod rem return RETURNING_ an exponent (**)  and or || 

You have probably noticed that the example did not execute successfully. A syntax error has been encountered at line 6. Close inspection of the example shows that the statement

 v_result = v_num1 / v_num2; 

contains an equal sign operator where an assignment operator should be used. The statement should be rewritten as follows:

 v_result := v_num1 / v_num2; 

Once the corrected example is run again, the following output is produced:

 Enter value for sv_num1: 3  old   2:    v_num1 integer := &sv_num1;  new   2:    v_num1 integer := 3;  Enter value for sv_num2: 5  old   3:    v_num2 integer := &sv_num2;  new   3:    v_num2 integer := 5;  v_result: .6  PL/SQL procedure successfully completed. 

As you can see, the example now executes successfully because the syntax error has been corrected.

Next, if you change the values of variables v_num1 and v_num2 to 4 and 0, respectively, the following output is produced:

 Enter value for sv_num1: 4  old   2:    v_num1 integer := &sv_num1;  new   2:    v_num1 integer := 4;  Enter value for sv_num2: 0  old   3:    v_num2 integer := &sv_num2;  new   3:    v_num2 integer := 0;  DECLARE  *  ERROR at line 1:  ORA-01476: divisor is equal to zero  ORA-06512: at line 6 

Even though this example does not contain syntax errors, it was terminated prematurely because the value entered for v_num2, the divisor, was 0. As you may recall, division by 0 is undefined, and thus leads to an error.

This example illustrates a runtime error that cannot be detected by the compiler. In other words, for some of the values entered for the variables v_num1 and v_num2, this example executes successfully. For other values entered for the variables v_num1 and v_num2, this example cannot execute. As a result, the runtime error occurs. You will recall that the compiler cannot detect runtime errors. In this case, a runtime error occurs because the compiler does not know the result of the division of v_num1 by v_num2. This result can be determined only at runtime. Hence, this error is referred to as a runtime error.

In order to handle this type of error in the program, an exception handler must be added. The exception-handling section has the following structure:

 EXCEPTION     WHEN EXCEPTION_NAME THEN        ERROR-PROCESSING STATEMENTS; 

The exception-handling section is placed after the executable section of the block. The preceeding example can be rewritten in the following manner.

graphics/intfig03.gif FOR EXAMPLE

 DECLARE     v_num1 integer := &sv_num1;     v_num2 integer := &sv_num2;     v_result number;  BEGIN     v_result := v_num1 / v_num2;     DBMS_OUTPUT.PUT_LINE ('v_result: '||v_result);  EXCEPTION     WHEN ZERO_DIVIDE THEN        DBMS_OUTPUT.PUT_LINE           ('A number cannot be divided by zero.');  END; 

The section of the example in bold letters shows the exception-handling section of the block. When this version of the example is executed with the values of 4 and 0 for variables v_num1 and v_num2, respectively, the following output is produced:

 Enter value for sv_num1: 4  old   2:    v_num1 integer := &sv_num1;  new   2:    v_num1 integer := 4;  Enter value for sv_num2: 0  old   3:    v_num2 integer := &sv_num2;  new   3:    v_num2 integer := 0;  A number cannot be divided by zero.  PL/SQL procedure successfully completed. 

This output shows that once an attempt to divide v_num1 by v_num2 was made, the exception-handling section of the block was executed. Therefore, the error message specified by the exception-handling section was displayed on the screen.

This version of the output illustrates several advantages of using an exception-handling section. You have probably noticed that the output looks cleaner compared to the previous version. Even though the error message is still displayed on the screen, the output is more informative. In short, it is oriented more toward a user than a programmer.

graphics/intfig07.gif

It is important for you to realize that on many occasions, a user does not have access to the code. Therefore, references to line numbers and keywords in a program are not significant to most users.


An exception-handling section allows a program to execute to completion, instead of terminating prematurely. Another advantage offered by the exception-handling section is isolation of error-handling routines. In other words, all error-processing code for a specific block is located in a single section. As a result, the logic of the program becomes easier to follow and understand. Finally, adding an exception-handling section enables event-driven processing of errors. As in the example shown earlier, in the case of a specific exception event, such as division by 0, the exception-handling section was executed, and the error message specified by the DBMS_OUTPUT.PUT_LINE statement was displayed on the screen.

Lab 7.1 Exercises

7.1.1 Understanding the Importance of Error Handling

In this exercise, you will calculate the value of the square root of a number and display it on the screen.

Create the following PL/SQL script:

 -- ch07_1a.sql, version 1.0  SET SERVEROUTPUT ON;  DECLARE     v_num NUMBER := &sv_num;  BEGIN     DBMS_OUTPUT.PUT_LINE ('Square root of '||v_num||        ' is '||SQRT(v_num));  EXCEPTION     WHEN VALUE_ERROR THEN        DBMS_OUTPUT.PUT_LINE ('An error has occurred');  END; 

In the preceding script, the exception VALUE_ERROR, is raised when conversion or type mismatch errors occur. This exception is covered in greater detail in Lab 7.2 of this chapter. In order to test this script fully, execute it two times. For the first run, enter a value of 4 for the variable v_num. For the second run, enter the value of -4 for the variable v_num. Execute the script, and then answer the following questions:

a)

What output was printed on the screen (for both runs)?

b)

Why do you think an error message was generated when the script was run a second time?

c)

Assume that you are not familiar with the exception VALUE_ERROR. How would you change this script to avoid this runtime error?

Lab 7.1 Exercise Answers

This section gives you some suggested answers to the questions in Lab 7.1, 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.

7.1.1 Answers

a)

What output was printed on the screen (for both runs)?

A1:

Answer: The first version of the output is produced when the value of v_num is equal to 4. Your output should look like the following:

 Enter value for sv_num: 4  old   2:    v_num NUMBER := &sv_num;  new   2:    v_num NUMBER := 4;  Square root of 4 is 2  PL/SQL procedure successfully completed. 

The second version of the output is produced when v_num is equal to -4. Your output should look like the following:

 Enter value for sv_num: -4  old   2:    v_num NUMBER := &sv_num;  new   2:    v_num NUMBER := -4;  An error has occurred  PL/SQL procedure successfully completed. 
b)

Why do you think an error message was generated when the script was run a second time?

A2:

Answer: Error message "An error has occurred" was generated for the second run of example because a runtime error has occurred. The built-in function SQRT is unable to accept a negative number as its argument. Therefore, the exception VALUE_ERROR was raised, and the error message was displayed on the screen.

c)

Assume that you are not familiar with the exception VALUE_ERROR. How would you change this script to avoid this runtime error?

A3:

Answer: The new version of the program should look similar to the program below. All changes are shown in bold letters.

 -- ch07_1b.sql, version 2.0  SET SERVEROUTPUT ON;  DECLARE     v_num NUMBER := &sv_num;  BEGIN     IF v_num >= 0 THEN        DBMS_OUTPUT.PUT_LINE ('Square root of '||v_num||           ' is '||SQRT(v_num));     ELSE        DBMS_OUTPUT.PUT_LINE ('A number cannot be negative');     END IF;  END; 

Notice that before you calculate the square root of a number, you can check to see if the number is greater than or equal to 0 with the help of the IF-THEN-ELSE statement. If the number is negative, the message "A number cannot be negative" is displayed on the screen. When the value of -4 is entered for the variable v_num, this script produces the following output:

 Enter value for sv_num: -4  old   2:    v_num NUMBER := &sv_num;  new   2:    v_num NUMBER := -4;  A number cannot be negative  PL/SQL procedure successfully completed. 

Lab 7.1 Self-Review Questions

In order to test your progress, you should be able to answer the following questions.

Answers appear in Appendix A, Section 7.1.

1)

A compiler can detect a runtime error.

  1. _____ True

  2. _____ False

2)

Without an exception-handling section, a PL/SQL block cannot be compiled.

  1. _____ True

  2. _____ False

3)

An exception is raised when which of the following occurs?

  1. _____ A compilation error is encountered.

  2. _____ A runtime error is encountered.

4)

An exception-handling section of a PL/SQL block is placed

  1. _____ after the reserved word END.

  2. _____ before the reserved word END.

  3. _____ before the reserved word BEGIN.

5)

The exception ZERO_DIVIDE is raised when number 1 is divided by number 2 and

  1. _____ number 1 is equal to 0.

  2. _____ number 2 is equal to 0.

  3. _____ both numbers are equal to 0.


    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