3.4 Improving the User Interface

Chapter 3
The PL/SQL Development Spiral

 

---

A couple of weeks go by before I encounter another need for twice. Then I need to call it for lower-UPPER conversion on a company name. So I put this line in my program:

v_full_name := twice (comp_rec.short_name, 'lu');

but when I execute the program, the full name is not in lower-UPPER format. It is all uppercased and, as I trace my way back to the data, that is just how the company short name is stored in the database. It doesn't seem to be doing any conversion at all.

Frustrated, I decide to head back to the source code. Of course, I can't remember where I stored the source code on disk. It was just a dinky little program. And it's generally not too easy to view the source code as it exists in the USER_SOURCE data dictionary view. Fortunately, I have built a PL/Vision package named PLVvu (more about this in Chapter 14, PLVtmr: Analyzing Program Performance) to view the code and so I execute that program to refresh my memory:

SQL> exec PLVvu.code('twice'); ----------------------------------------------------------- Code for FUNCTION TWICE ----------------------------------------------------------- Line#  Source -----------------------------------------------------------     1 FUNCTION twice     2    (string_in IN VARCHAR2, action_in IN VARCHAR2)     3 RETURN VARCHAR2     4 IS     5 BEGIN     6    IF action_in = 'UL'     7    THEN     8       RETURN (UPPER (string_in) || LOWER (string_in));     9    ELSIF action_in = 'LU'    10    THEN    11       RETURN (LOWER (string_in) || UPPER (string_in));    12    ELSIF action_in = 'N'    13    THEN    14       RETURN string_in || string_in;    15    END IF;    16 END twice;

The problem becomes clear: the action must be passed in as uppercase: LU and not lu. The solution seems to me to be equally clear: fix my line of code to pass upper case.

v_full_name := twice (comp_rec.short_name, 'LU');

Well, that certainly is one way to solve the problem. Unfortunately, it is really just a variation on "blame the victim." Why couldn't I pass lu in lowercase to get the action I wanted? It's not as if the lowercase version is used by twice to perform some other kind of conversion. The case of the action should not be a factor in the way twice works. Unfortunately, the way I wrote the program, any user must be aware of this inflexibility of twice -- be aware of minute implementation details of twice -- or risk introducing bugs in her code.

These are danger signs pointing to a poorly designed program. A user should not have to know anything about the internals of twice to use it. Furthermore, the program should be smart enough to accept the action in any number of different formats and do the right thing for the user.

The solution is straightforward: convert the action value provided by the user to upper or lower case and then test based on that case. In this way, the user can enter lower, upper, or mixed case and the program will function as expected. Example 3.3 shows the "smart" version of twice, which utilizes this parameter-conversion technique.

Example 3.3: The twice Function with Parameter Conversion

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); BEGIN    IF v_action = 'UL'    THEN       RETURN (UPPER (string_in) || LOWER (string_in));                 ELSIF v_action = 'LU'    THEN       RETURN (LOWER (string_in) || UPPER (string_in));                 ELSIF v_action = 'N'     THEN       RETURN string_in || string_in;    END IF;  END twice; /