2.5.1 Seeing Developers as Users

The vast majority of the packages I build are utilities and components for other developers. PL/SQL developers are, in other words, my users. Now you are probably aware that developers generally don't have many good things to say about their users. "Those users" are always modifying their requirements and are incredibly lazy; they change their minds on a daily or hourly basis; and they could care less about your resource issues. Why (I hear again and again), if it's not absolutely obvious and easy to navigate through an application, "those users" complain and complain -- and sometimes refuse outright to use your application. Ungrateful wretches.

Well, I have news for you: developers as users are no different from end users as users. They (and I include myself fully in this characterization) have a very low tolerance for rigmarole and wasted motion. They will use a utility I offer them only if they can understand it intuitively and put it to use instantly. I believe that attitude is appropriate. Our software should be smart and easy to use. Ease of use is, however, just the first requirement. Developers also want total flexibility. They don't want to have to do things my way just because I "wrote the book" on PL/SQL and wrote the package, too.

How do I know that my users will be so fussy? Because for the last year I have been struggling to use my own software and have constantly needed more flexibility in order to make that software useful to me . I have watched PL/Vision grow both in number of packages and internal complexity of those packages. In the process I have taught myself several techniques that I explore in this section. You'll see these over and over again in PL/Vision (especially the building into my packages of toggles and windows).

As far as I am concerned, it is always worth it for me to spend more time in the design and development of my code if it results in programs that are smarter and therefore easier to use. I don't necessarily believe that we should follow the tenet that the user is always right, but we should generally take a more respectful view towards our users -- especially the developer ones. In almost every case, they have a legitimate gripe. Computers and the software installed on them are not nearly as intuitive and accessible as they should be. Do your part in your design of PL/SQL packages to improve that situation.

2.5.2 Making Your Programs Case-Insensitive

Make sure users don't trip over senseless obstacles on the path to using your programs. A common source of frustration is the requirement that arguments to a program be in one case or another (usually upper or lower).

Consider the following program:

CREATE OR REPLACE FUNCTION twice 
   (string_in IN VARCHAR2, action_in IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
   IF action_in = 'UL'
   THEN
      RETURN (UPPER (string_in) || LOWER (string_in));      
      
   ELSIF action_in = 'LU'
   THEN
      RETURN (LOWER (string_in) || UPPER (string_in));      
      
   ELSIF action_in = 'N' 
   THEN
      RETURN string_in || string_in;
   END IF; 
END twice;
/

This function (which is not even defined inside a package; this best practice, like many others, applies equally to standalone modules as well) returns a string concatenated to itself, with some optional case-conversion action. You pass it UL or LU or N, and the appropriate transformation of the string is made. But what if someone calls twice as follows?

bigger_string := twice (smaller_string, 'ul');

The PL/SQL runtime engine will actually raise an exception:

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

This is very poor behavior by the function. If developers are going to reuse your code, they need to get dependable results from it. It should never raise the -6503 exception or, in general, any exception at all. Instead it should return a value that indicates a problem whenever possible. Beyond that, users of twice should not have to care about the case of the string they pass for the action code. Your program should automatically force all entries of this kind (action codes and types) to either lower- or upper-case and then proceed from there. The best way to do this, I have found, is to declare a local variable that accepts as a default value the case-converted argument. This technique is shown in the following example:

CREATE OR REPLACE FUNCTION twice 
   (string_in IN VARCHAR2, action_in IN VARCHAR2)
RETURN VARCHAR2
IS
   v_action VARCHAR2(10) := UPPER (action_in);
BEGIN

With this approach, you never reference the parameter action_in in the function. Instead, you work with v_action in the body of the function, and case is never an issue. This may seem like a small issue, but it can loom large when a developer is under lots of pressure, wants to use your code, and fails the first three times because the case is wrong or the literal used for the action code is in some way erroneous.

2.5.3 Avoiding Need for User to Know and Pass Literals

If you follow the advice of the previous section, a user of the twice function will be able to enter UL , ul , uL , LU , lu , N , or n, and the program will react properly. But in an ideal world, users wouldn't even have to know about these literal values -- and they certainly wouldn't have to place such literals in their program. What if someone decides to change the particular constants used by twice to recognize different kinds of actions?

Removing literals from your programs for these kinds of arguments is made particularly easy using packages. There are two ways to achieve this objective:

1. Provide separate programs for each of the different actions.

2. Provide package-based constants that hide the action values and offer a named element in their places.

Creating different program specifications for each action is practical only if there is a fixed number of actions. I use this approach in PLVexc; there, I convert a handler action argument in the handle procedure to four different procedures:

PROCEDURE stop;
PROCEDURE recNstop;
PROCEDURE go;
PROCEDURE recNgo;

This proliferation of procedures is not desirable if you think that the set of possible actions might change or expand. Also, in some cases, you really want to stick with one overloaded name and not bewilder the user with a whole suite of programs. For example, if I took the PLVexc approach with the twice function I would end up with:

FUNCTION twiceUL ...;
FUNCTION twiceLU ...;
FUNCTION twiceN ...;

As an alternative, I could define a set of constants, one for each action, as shown in the package specification below:

CREATE OR REPLACE PACKAGE dup
IS
   lu CONSTANT VARCHAR2(1) := 'A';
   ul CONSTANT VARCHAR2(1) := 'B';
   n  CONSTANT VARCHAR2(1) := 'X';
   FUNCTION stg 
      (stg_in IN VARCHAR2, 
       action_in IN VARCHAR2 := n,
       num_in IN INTEGER := 1)
   RETURN VARCHAR2;
END dup;
/        

Notice that the twice function has now been replaced with dup.stg , a more generalized string-duplication function. The default action for a call to dup.stg is now the constant n , rather than the literal N . So if I want to duplicate a string 10 times and convert it to UPPER-lower format, I would call dup.stg as follows:

v_bigone := dup.stg (v_ittybitty, dup.ul, 10);

Sure, I have to know the names of the constants, but I will be informed at compile time if I got it wrong. This is a very important distinction from the mysterious, hard-to-trace error I will receive if I simply pass the wrong literal value. The compiler could care less about if I pass the right literal. There are no right or wrong literal values as far as the compiler is concerned; my code must therefore be qualitatively more robust to handle this error gracefully.

The other advantage to the package constant approach is that you can change the underlying values without affecting anyone's use of dup.stg . As you can see in the package specification, I deliberately gave these constants values that did not match the previous values. This will flush out old usages and force compliance with the use of the constants, rather than the literals. You don't have to do this, and may not be able to for reasons of backward compliance, but it is a useful technique to keep in mind.