Lab 7.1 Handling Errors


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.

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.

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/trick_icon.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.



Oracle PL[s]SQL by Example
Oracle PL[s]SQL by Example
ISBN: 3642256902
EAN: N/A
Year: 2003
Pages: 289

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