#

| Chapter 3 The PL/SQL Development Spiral |

## 3.5 Rough Waters Ahead

I have now fixed the twice function so that a user can enter UL, LU, or N in any case he wants. The function seems functional; I figure that someone else might even want to use it. So I send out an email to my development team describing how to use twice.

A day later I get a call from a co-developer complaining about `twice`. The function is once again not following orders. He asked twice to perform upper-lower conversion and all he got was this message:

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

Suddenly I am in the role of telephone support and it's not much fun. I am quickly baffled and 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 right 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 doesn't take long for me to straighten him out (that is, to tell him the secret codes). Turns out that my email message just assumed that my co-developers would understand the U and L stuff. Intuitive, really. But of course our minds all work differently and what is obvious to one person is obscure, at best, to another.

Unfortunately, the way I built twice assumed that the user would know the correct codes. And my assumption was so strongly held that I don't even include any code to let the user know that a mistake was made. Worse, if the user passes an unacceptable action, twice does not exactly handle it gracefully. Instead, none of the IF statement clauses evaluate to TRUE and the function never executes a RETURN statement, bringing about the -6503 error.

This experience points out two glaring problems with twice, a function that just days ago I thought was, well, pretty solid. These problems are:

1. The function does not execute a RETURN statement in some cases. This is a big no-no, indicating that the structure of the function is very poorly designed.

2. I have made assumptions that I do not bother to validate in my program.

These faults can lead to unexpected program failure and must be corrected.

3.4 Improving the User Interface |  | 3.6 Building a Structured Function

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