[Chapter 8] 8.10 RAISE Nothing but ExceptionsChapter 8Exception Handlers 8.10 RAISE Nothing but Exceptions Have you noticed that the RAISE statement acts in many ways like a GOTO statement? The GOTO statement in PL/SQL looks like this: GOTO label_name;where label_name is the name of a label. This label is placed in a program as follows: <<label_name>>When PL/SQL encounters a GOTO statement, it immediately shifts control to the first executable statement following the label (which must still be in the execution section of the PL/SQL block). The RAISE statement works much the same way: when PL/SQL encounters a RAISE, it immediately shifts control to the exception section, and then looks for a matching exception. A very significant and fundamental difference between GOTO and RAISE, however, is that GOTO branches to another execution statement, whereas RAISE branches to the exception section. The RAISE statement, in other words, shifts the focus of the program from normal execution to "error handling mode." Both from the standpoint of code readability and also of maintenance, you should never use the RAISE statement as a substitute for a control structure, be it a GOTO or an IF statement. If you have not tried to use RAISE in this way, you might think that I am building up a straw man in order to knock it down. Would that it were so. Just in the process of writing this book, I ran across several examples of this abuse of exception handling. Check out, for example, the function description for GET_GROUP_CHAR_CELL in Oracle Corporation's Oracle Forms Reference Volume 1. It offers a function called Is_Value_In_List, which returns the row number of the value if it is found in the record group, as an example of a way to use GET_GROUP_CHAR_CELL. The central logic of Is_Value_In_List is shown in the following example. The function contains three different RAISE statements -- all of which raise the exit_function exception: 1 FUNCTION Is_Value_In_List 2 (value VARCHAR2, rg_name VARCHAR2, rg_column VARCHAR2) 3 RETURN NUMBER 4 IS 5 Exit_Function EXCEPTION; 6 BEGIN 7 If bad-inputs THEN 8 RAISE Exit_Function; 9 END IF; 10 11 LOOP-through-record-group 12 IF match-found 13 RAISE Return_Value; 14 END IF; 15 END LOOP; 16 17 RAISE Exit_Function; 18 19 EXCEPTION 20 WHEN Return_Value THEN 21 RETURN row#; 22 23 WHEN Exit_Function THEN 24 RETURN 0; 25 END;The first RAISE on line 8 is an appropriate use of an exception because we have an invalid data structure. The function should bail out. The second RAISE on line 13 is, however, less justifiable. This RAISE is used to end the program and return the row in which the match was found. An exception is, in this case, used for successful completion. Exception Handling -- Quick Facts and TipsHere are some facts and tips to remember about exception handling: The exception section of a PL/SQL block only handles exceptions raised in the execution section of that block. An exception raised in the declaration section of a PL/SQL block is handled by the exception section of the enclosing block, if it exists. An exception raised in the exception section of a PL/SQL block is handled by the exception section of the enclosing block, if it exists. Use WHEN OTHERS when you want to trap and handle all exceptions in a PL/SQL block. Once an exception is raised, the block's execution section is terminated and control is transferred to the exception section. You cannot return to that execution section after the exception is raised. After an exception is handled, the next executable statement in the enclosing block is executed. To handle a specific exception, it must have a name. You declare exceptions to give them names. Once you have handled an exception, normal program execution continues. You are no longer in an "exception" situation. The third RAISE on line 17 is also questionable. This RAISE is the very last statement of the function. Now, to my mind, the last line of a function should be a RETURN statement. The whole point of the function, after all, is to return a value. In this case, however, the last line is an exception, because the author has structured the code so that if I got this far, I have not found a match. So raise the exception, right? Wrong. "Row-not-found" is not an exception from the standpoint of the function. That condition should be considered one of the valid return values of a function that asks "Is value in list?" This function should be restructured so that the exception is raised only when there is a problem. From the perspective of structured exception handling in PL/SQL, this function suffered from several weaknesses: Poorly named exceptionsThe exception names exit_function and return_value describe actions, rather than error conditions. The name of an exception should describe the error which took place. Exceptions for valid outcomesBy using these "action" names, the developers are actually being very open about how they are manipulating the exception handler. They say, "I use exceptions to implement logic branching." We should say to them, "Don't do it! Use the constructs PL/SQL provides to handle this code in a structured way." If you encounter either of these conditions in code you are writing or reviewing, take a step back. Examine the logical flow of the program and see how you can use the standard control structures (IF, LOOP, and perhaps even GOTO) to accomplish your task. The result will be much more readable and maintainable code. 8.9 Exception Handler as IF Statement9. Records in PL/SQL Copyright (c) 2000 O'Reilly & Associates. All rights reserved.