| Lab Objectives After this Lab, you will be able to: 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.  | 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. _____ True _____ False
| | 2) | Without an exception-handling section, a PL/SQL block cannot be compiled. _____ True _____ False
| | 3) | An exception is raised when which of the following occurs? _____ A compilation error is encountered. _____ A runtime error is encountered.
| | 4) | An exception-handling section of a PL/SQL block is placed _____ after the reserved word END. _____ before the reserved word END. _____ before the reserved word BEGIN.
| | 5) | The exception ZERO_DIVIDE is raised when number 1 is divided by number 2 and _____ number 1 is equal to 0. _____ number 2 is equal to 0. _____ both numbers are equal to 0.
|
| 
