Advanced Oracle PL/SQL Programming with Packages

Advanced Oracle PL/SQL Programming with PackagesSearch this book
Previous: 3.4 Improving the User InterfaceChapter 3
The PL/SQL Development Spiral
Next: 3.6 Building a Structured Function
 

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.


Previous: 3.4 Improving the User InterfaceAdvanced Oracle PL/SQL Programming with PackagesNext: 3.6 Building a Structured Function
3.4 Improving the User InterfaceBook Index3.6 Building a Structured Function

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference