Cogitative Coding

By Steven Feuerstein
OTJ, Fall 1996

Climbing the Spiral to PL/SQL Excellence, Part 1

The master of PL/SQL shows you how to travel upward, step by step, through your development spiral toward more enhanced code.

How many times have you written a program, gotten it to compile the first time around, and found that it had no bugs? Let me rephrase that question: Have you ever written a program of more than, say, five lines that compiled and executed without bugs the first time around? Please send me your resume if the answer is yes. We need developers like you. I have a confession to make: I have never once been able to get it right the first time. Perhaps I am just too impatient to walk through my code properly. I certainly haven't found the patience to discover the joys of computer-assisted software engineering. I am just a hacker at heart.

Even with lots of patience and prior analysis, however, I believe that it is wrong to expect to "get it right the first time." Software development should instead be seen as an iterative process. You get closer and closer to perfection as you take multiple passes at a solution. I like to think of this process as a "spiral towards excellence." A spiral is different from a cycle, which is the term often used to portray the life cycle of development. A cycle or circle has you coming back around to where you were before. A spiral implies that when you come back around, you are at a higher place than you were on the previous spin.

There are many ways to apply this type of thinking to PL/SQL development. The single most important technical skill to nurture as a programmer is that of code modularization (the single most important nontechnical skill is problem-solving). In this article I explore the "spiral towards excellence," using the building of a very basic and (on the surface) trivial PL/SQL utility as an example.

The Basic Need

When writing an application that performs a lot of string manipulation, one action that I find myself coding again and again is the duplication of a string. For example, let's say that I need to convert "abc" to "abcabc." I would write the following:

v_new_string := v_old_string ||
v_old_string;

and then I write:

v_comp_name := v_comp_name ||
v_comp_name;

[Note: you are not allowed to wonder why I would need to do something like this. You simply must take my word for it.]

I sense a pattern. Whenever I notice repetition in my coding, I instinctually put on the brakes and examine it more closely. A pattern implies that I can generalize to a formula, encapsulate my formula into a function or procedure, and write the formula once and apply it often. This cuts down on my typing and improves my ability to maintain and even enhance my code. In this example, the pattern is clear: I want to double the supplied string and return it to another PL/SQL variable. I want, in other words, to create a function. So I quickly write the twice function shown in Listing 1.

With twice created in the database, I can replace those two explicit concatenations with these calls to twice:

v_new_string := twice (v_old_string);
v_comp_name := twice (v_comp_name);

Thus I have added another fine tool to my toolbox and I continue on my merry programming way. Lo and behold, just as I predicted, I soon run into the need for the twice function again. This time, I want to double the description of a product type, but I also need to make sure it is in uppercase, so I type:

v_prodtype:= twice
(UPPER (v_prodtype));

So far, so very good. I code my little heart out until I run into a new variation on my twice theme: I need to double the string, but this time I want to uppercase the first instance and lowercase the second. Unfortunately, the twice function cannot handle this requirement. I now face a crucial and common modularization dilemma: Should I try to enhance the twice function to handle this new twist, or should I leave it as is and build yet another function for UPPER-lower? A part of me would like to tell you that this really isn't much of a dilemma, that you should always widen the scope of your existing program. That would be the most elegant solution, but it would also be irresponsible advice.

We write programs so that people can use them, not so we can marvel at their elegance. There are situations in which it makes more sense to start with a brand-new build. In the case of the twice function, however, I am not yet ready to give up for two reasons:

• The point of this article is to step you upward through the spiral. Abandoning the function now would mean starting a new spiral, not climbing this one.
• The twice function is still very basic and unexplored from the standpoint of scope expansion. It is at least worth trying to incorporate this new requirement. If it proves unnatural, then the effort ought to be abandoned.

Adding Value

Sometimes it is helps to step back and search for the bigger picture before embarking on further enhancements. In this case, I find myself wondering what other twists and turns I might encounter in my application development. I have a need for UPPER-lower string duplication. It is reasonable to expect that at some point I might also need to perform lower-UPPER string duplication. As long as I am changing the twice function for one of these variations, I should try to stay ahead of the game and handle both of these variations.

So I will restate the new requirements of the twice function: Double the specified string. Return the new string with the same case as the original: Return it in UPPER-lower or in lower-UPPER, depending on the user's request. When stated this way an obvious question pops up: How will the user specify the case handling in the call to the twice function? For a standalone function, this means adding a parameter. Instead of simply accepting the string value for doubling, the twice function must also receive the type of action to perform. The new header for the twice function, therefore, must be:

FUNCTION twice

(string_in IN VARCHAR2,

action_in IN VARCHAR2)

where the action can be one of the following values:

N No change to case
UL UPPER-lower case conversion
LU lower-UPPER case conversion

After the parameter and valid options are in place, the implementation is straightforward. (See Listing 2). I use a simple if statement to direct the runtime engine to the correct return statement. With this new version of the twice function, I can display the following string doublings:

SQL> exec DBMS_OUTPUT.PUT_LINE -
> (twice ('abc', 'UL'));
ABCabc

SQL> exec DBMS_OUTPUT.PUT_LINE -
> (twice ('abc', 'LU'));
abcABC

SQL> exec DBMS_OUTPUT.PUT_LINE -
> (twice ('abc', 'N'));
abcabc

My twice function is starting to look interesting. It handles a number of different flavors of conversion and seems easy to use.

Supplying Backward Compatibility

I now have a very flexible twice function that I've tested successfully with my new UPPER-lower and lower-UPPER requirements. I can now step back into the stream of application development. (I consider my work on the twice function part of the process of building my generic PL/SQL toolset.) So I continue to code and, after a while, return to one of my earlier uses of the twice function:

v_prodtype:= twice
(UPPER (v_prodtype));

Although I don't need to change this line of code, I do need to modify others in the same procedure. After I perform the necessary code modifications, I recompile this procedure and am shocked to get the following error:

PLS-00306: wrong number or types of arguments in call to 'TWICE'

Suddenly, code that worked earlier in the day won't compile. What went wrong? When I enhanced the twice function, I added a second parameter. I did not, unfortunately, consider the existing uses of the twice function. The way that I changed the parameter list actually invalidated those prior instances. Because I did not provide a default value for the action_in parameter, it became necessary for all executions of the twice function to include two values in the argument list. This is an unacceptable way to enhance existing code.

When I make changes to programs currently in use across my production applications (or in any version of previously existing programs), I must do so in a way that lets the code continue to work as it did previously. Otherwise I face a maintenance nightmare that would, in effect, stop me from enhancing code. It simply isn't possible (especially given the state of PL/SQL development and analysis tools) to search (and replace!) efficiently for all uses of a given program. So I developed a technique that supports backward compatibility with earlier uses of the twice function, while simultaneously letting me use that same program in new ways. Default values for my action_in parameter made this possible.

IN parameters with default values are a critical technique for ensuring backward compatibility for enhanced PL/SQL programs. When an IN parameter has a default value, it is not necessary to include a value for that argument when the program is called. If a calling program does not specify an argument, the program will use the default value in its execution. If these IN parameters are all trailing parameters (they come at the end of the parameter list), you can simply ignore them when calling the program. If the IN parameters are positioned before one or more IN or IN OUT parameters, you must use named notation to skip over that parameter. (You can refer to my book Oracle PL/SQL Programming for more details on named notation.) So I make a simple change to the header of the twice function:

FUNCTION twice

(string_in IN VARCHAR2,

action_in IN VARCHAR2

DEFAULT 'N')

This way, if I call the twice function with only a single argument, the function will assume that I do not want to perform any type of case conversion. With this change in place, all previous occurrences of the twice function will work as they did before I even thought of a case-conversion action parameter.

Improving the User Interface

Well, a couple of weeks went by before I encountered a need for the twice function. I needed to call it for lower-UPPER conversion on a company name. So I put the following line in my program:

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

But when I executed the program, the full name was not in lower-UPPER format. It appeared in all uppercase and, as I traced 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 easy to view the source code as it exists in the USER_SOURCE data dictionary view. Fortunately, I have already built a PL/Vision package named PLVvu to view the code; I execute that program to refresh my memory. (See Listing 3. PL/Vision is a library of packages that forms the backbone of the author's upcoming second book on PL/SQL - see www.saraswati.com for more details.) Then the problem becomes clear: The action must be passed in as uppercase "LU" and not "lu." The solution seems equally clear: Fix my line of code to pass uppercase:

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 of "blame the victim." Why can't I pass in "lu" in lowercase to get the action I want? It's not as if the lowercase version is used by the twice function to perform some other type of conversion. In fact, the case of the action should not be a factor in the way that the twice function works. Unfortunately, though, because of the way that I wrote the program, users must be made aware of the case sensitivity of the twice function. In other words, they must be aware of minute implementational details of the twice function or risk introducing bugs in their code.

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

The solution in this case is straightforward: Convert the action value provided by the user to uppercase or lowercase and then test based on that case. This way, users can enter lowercase, uppercase, or mixed case and the program will function as expected. Listing 4 shows the "smart" version of twice, which utilizes this parameter-conversion technique.

Stepping back to a more general situation, my recommendation is: Whenever you require users to enter literals to direct activity in your program, ensure that they don't have to know about the "proper" case in which to enter the literal. Make your program smart enough to interpret a range of entries. Your users will appreciate your efforts - and they will show their appreciation by using your software.

Rough Waters Ahead

I have now fixed the twice function so that users can enter "UL," "LU," or "N" in any case they wish. The function seems functional; someone else might even want to use it. So I send out an email to my development team describing how to use the twice function. A day later I get a call from a co-developer complaining about the twice function. It seems that the function is once again not following orders. He explains that he asked the twice function to perform UPPER-lower conversion and he received the following message:

ORA-06503: PL/SQL: Function returned without value

Suddenly I am in the role of telephone support and it's not much fun. Baffled, I ask him to read to me exactly what he typed in. He says:

new_name := twice (old_name, 'BS');

"What's 'BS'?" I ask him, feeling as though I am walking into something I will regret.

"Big-Small," he responds. I sigh with relief. He continues: "I thought that's what I was supposed to pass to twice: B for big letters and S for small letters."

It turns out that my email message assumed that my co-developers would understand the U and L stuff. But of course our minds all work differently and what is obvious to one person is obscure at best to another. The way I built the twice function assumed that users would know the correct codes. And my assumption was so strongly held that I didn't even include any code to let the user know that a mistake was made. Worse, if the user passes an unacceptable action, the twice function does not handle it gracefully. Instead, none of the if statement clauses evaluates to TRUE and the function never executes a RETURN statement, bringing about the -06503 error.

This experience points out two glaring weaknesses of the twice function (a function that just days ago I thought was pretty solid):

1. In some cases it does not execute a return statement, which indicates that the structure of the function is poorly designed.
2. I make assumptions that I don't bother to validate in my program.

Both of these weaknesses can lead to unexpected program failure and must be corrected.

Building a Structured Function

Consider the problem of a function that does not execute a RETURN. The whole point of a function is to return a value. Not only should it return a value when everything went right, it should also return a value when the function for some reason fails and raises an exception (NULL being the usual candidate under these circumstances).

In the twice function, all of my RETURN statements are nested inside IF clauses. So an invalid entry by the user means that those RETURN statements are ignored. There are several ways to fix this problem. I could include an ELSE statement, or I could make sure that the action was valid at the start of the function (I look at that in a moment). The best all-around solution, however, is to construct your functions with the following templated structure:

1 FUNCTION twice RETURN VARCHAR2
2 IS
3 v_retval VARCHAR2(100) := 'null';
4 BEGIN
5
6 RETURN v_retval;
7
8 EXCEPTION
9 WHEN OTHERS
10 THEN
11 RETURN NULL;
12 END twice;

In this template I declare a local variable (the "return value" or v_retval) with the same data type as the function itself. I then make the last line of the function a RETURN of the v_retval variable's value. In addition, my exception returns NULL if any kind of exception is raised. You will never get a -06503 error with this template, and it is easier to debug than functions with RETURN statements scattered throughout the body of the program.

A version of the twice function that follows the template is shown in Listing 5. Now I have a return value variable as the last line of the function body. I did this by simply replacing each of the individual RETURN statements inside the if statement with an assignment to v_retval. I did not add any type of special handling for invalid actions. Yet I no longer have to worry about -06503 because I chose a structure for my function that automatically rules out that possibility. Furthermore, it even returns a sensible value in the case of a bad action code. The v_retval is initialized by PL/SQL to NULL. If the user passes a code such as "BS," the value of v_retval will not change and, as a result, NULL will be returned, indicating an incorrect value (or, come to think of it, NULL input).

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 a -06503 error. With my latest version of the twice function, you no longer get the error. Instead, the function now returns the same value if you pass in a NULL string or if you pass in a bad action code. This occurs because I did not explicitly handle an underlying assumption of my program. This is not a good way for a function to notify a user of errors.

Just about every piece of software that 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 important to verify or "assert" that none of the rules is violated. If you assume it without checking, your program may act strangely.

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

If a low-level utility such as the twice function is to be reused successfully, it must have intelligence built into it to check for bad actions and to inform users of problems. The best way to do this is to assert that the incoming argument is correct. For example, the following code asserts that the action code is correct. If not, it raises a built-in PL/SQL exception.

IF v_action NOT IN ('UL', 'LU', 'N')

THEN

RAISE VALUE_ERROR;

END IF;

If the action is valid, then the twice function will execute normally. 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 returns a value? I make an exception here. The use of the twice function 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 type of error that occurs in production. My IF statement uncovers a design-level error in the code that must be corrected before I worry about data-entry errors or other application-level concerns.

One problem with the previous IF statement is that it doesn't really inform users about the problem; it only raises a generic system exception. If you are going to assert assumptions, you should display some feedback when the assumption is not met. Furthermore, instead of building these types of if statements throughout your code, it is better to create a single assert procedure such as the one shown in Listing 6. The routine in Listing 6 accepts the Boolean expression that needs to be true and a string that is to be displayed in case of failure.

With this assert routine added to my arsenal, I have a very robust twice function. (See Listing 7.) If another co-developer tries the same "BS" from an anonymous block in SQL*Plus, he or she will receive the following feedback:

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

With this assert program in place, I will spend less time performing telephone support for the twice function.

MORE TO COME. . . THE COMPLETE EVOLUTION OF THE TWICE FUNCTION

Seems like we're done, doesn't it? Well we're not. While we've managed to create quite a robust twice function, I still have a few more tricks to show you. See Part 2 in the Winter issue of OTJ for the conclusion of this article.

LISTING 1. The first version of the twice function.

CREATE OR REPLACE FUNCTION twice (string_in IN VARCHAR2)

RETURN VARCHAR2

IS

BEGIN

RETURN string_in || string_in;

END twice;

/

LISTING 2. The twice function with alternative actions.