#

| Chapter 3 The PL/SQL Development Spiral |

## 3.7 Handling Program Assumptions

Now let's address the problem of invalid action codes. You've already seen the downside: the user is not notified of an invalid entry; the program simply failed with -6503. With my latest version of twice, you no longer get the error. On the other hand, the function now returns the same value if you pass in a NULL string or if you pass in a bad action code. This is not a good way for a function to notify the user of errors. And that is because I do not explicitly handle an underlying assumption of my program.

Just about every piece of software you write makes assumptions about the data it manipulates. For example, parameters may have only certain values or must be within a certain range; a string value should have a certain format, or perhaps an underlying data structure is assumed to have been created. It's fine to have such rules and assumptions, but it is also very important to verify, or assert, that none of the rules are being violated. Because if you assume it and you don't check, your program can end up acting very strangely.

In the twice function, I assume that you, the user, know that you use UL for UPPER-lower, LU for lower-UPPER, and N for no case conversion. But how are you supposed to know this? You either have to see the source code, which is not always going to be possible or desirable, or you have to be given external documentation about the function. And even if you read the documentation on Monday, who says you are going to remember it on Friday?

If a low-level utility like twice is going to be successfully reused, it has to have the smarts built into it to check for bad actions and inform the user of the problem. The best way to do this is to assert that the incoming argument is correct. The following line of code asserts, for example, that the action code is correct. If not, it raises an exception.

>     IF v_action NOT IN ('UL', 'LU', 'N')
>     THEN
>        RAISE VALUE_ERROR;
>     END IF;

If the action is valid, then twice would function as it normally does. Now if the action code is invalid, an exception is raised and no value is returned from the function. Is this a violation of my recommendation that a function always return a value? I would suggest that in this case an exception is more appropriate. The use of twice is invalid if it is not passed a valid code. In this context, it doesn't even make sense to continue processing. This is not the kind of error that occurs in production. My IF statement uncovers a design-level error in the code that must be corrected before you can even worry about data entry errors or other application-level concerns.

One problem with the IF statement is that it doesn't really inform the user about the problem. It just raises a generic, system exception. I think that if you are going to assert assumptions, you should display some feedback when the assumption is not met. Furthermore, I suggest that instead of building IF statements like this throughout your code, you create a single assert procedure like the one shown in Example 3.5. This program accepts the Boolean expression that needs to be true and a string to be displayed in case of failure.

#### Example 3.5: A Very Generic Assertion Routine

>     PROCEDURE assert
>         (bool_in IN BOOLEAN, stg_in IN VARCHAR2 := NULL)
>     IS
>     BEGIN
>        IF NOT bool_in OR bool_in IS NULL
>        THEN
>           IF stg_in IS NOT NULL
>           THEN
>              DBMS_OUTPUT.PUT_LINE (stg_in);
>           END IF;
>           RAISE VALUE_ERROR;
>        END IF;
>     END;

With the `assert` routine added to my arsenal, I now have a very robust twice function (see Example 3.6). If another codeveloper tries the same BS from an anonymous block in SQL*Plus, here is the feedback she will receive:

>     Please enter UL LU or N
>     declare
>      *
>     ERROR at line 1:
>     ORA-06502: PL/SQL: numeric or value error

With my assert program in place, I spend less time on telephone support for twice. And if someone does call, I will tell them to "RTFM!", as in: "Read The Fancy Message!"

#### Example 3.6: Using an Assertion Routine Inside twice

>     CREATE OR REPLACE FUNCTION twice
>        (string_in IN VARCHAR2,
>         action_in IN VARCHAR2 DEFAULT 'N')
>     RETURN VARCHAR2
>     IS
>        v_action VARCHAR2(10) := UPPER (action_in);
>        v_retval VARCHAR2(100);
>     BEGIN
>        assert
>           (v_action IN ('UL', 'LU', 'N'),
>            'Please enter UL LU or N');
>        IF v_action = 'UL'
>        THEN
>           v_retval := UPPER (string_in) || LOWER (string_in);

>        ELSIF v_action = 'LU'
>        THEN
>           v_retval := LOWER (string_in) || UPPER (string_in);

>        ELSIF v_action = 'N'
>        THEN
>           v_retval := string_in || string_in;
>        END IF;
>        RETURN v_retval;
>     END twice;
>     /

3.6 Building a Structured Function |  | 3.8 Broadening the Scope

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.