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.

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 exceptions

The 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 outcomes

By 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.