8.10 RAISE Nothing but Exceptions

Chapter 8
Exception Handlers

 

---

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 Tips

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