2.8.1.1 Supporting many data combinations

This is probably the most common reason to employ overloading. The p package of PL/Vision (see the following sidebar) offers an excellent example of this kind of overloading opportunity. This package contains eight overloadings of the l procedure so that you can pass many different combinations of data and have the package interpret and display the information properly. The following headers show, for example, a simplified portion of the specification for the p package, which illustrates the overloading:

PROCEDURE l (date_in IN DATE, mask_in IN VARCHAR2 := PLV.datemask);   
PROCEDURE l (char_in IN VARCHAR2, number_in IN NUMBER);
PROCEDURE l (boolean_in IN BOOLEAN);

Because of my extensive overloading, I can pass a complex date expression (taking me back 18 years) and see the date and time in a readable format with a minimum of effort:

SQL> exec p.l(ADD_MONTHS(SYSDATE,-316));
February 18, 1970 17:50:12

I can combine strings and numbers together easily, as shown in this exception section:

BEGIN
    p.l (1/0);
EXCEPTION
   WHEN ZERO_DIVIDE
   THEN
      p.l (SQLERRM, SQLCODE);
END;
/

SQL> @above_script
ORA-01476: divisor is equal to zero: -1476

And, finally, I can pass a Boolean expression directly to the p.l procedure and have it display meaningful information:

SQL> exec p.l ('a' IN ('d', 'e', 'f'));
FALSE

Just to give you a sense of the benefit of overloading in this case, if I did not have access to the p package and instead relied on DBMS_OUTPUT.PUT_LINE to generate my output, I would have to write the following code to handle the last call to p.l:

IF bool_value IN ('d', 'e', 'f')
THEN
   DBMS_OUTPUT.PUT_LINE ('TRUE');
ELSE
   DBMS_OUTPUT.PUT_LINE ('FALSE');
END IF;

Why do I need to do this? The DBMS_OUTPUT package does overload its PUT_LINE procedure, but only for single string, date, and number values. It does not handle Booleans at all. It also does not allow me to pass combinations of data. And it does not show the time component of a date variable. What a hassle! For all these reasons, my extra layer of overloaded code in the p package liberates me from having to write extra code. I just tell p.l what I want to see and it figures out how to display that information.

The same technique is also readily visible in the PLVtab package. This PL/SQL table-oriented package offers nine overloadings of the display procedure, one for each kind of PL/SQL table predefined in the package. As far as a user of PLVtab.display is concerned, there is just one program to display a PL/SQL table. The only difference between each of the versions of PLVtab.display is the first argument, the table type, as shown in the following header for the display procedure:

PROCEDURE display 
 (table_in IN number_table|boolean_table|date_table, 
  end_row_in IN INTEGER,
  header_in IN VARCHAR2 := NULL,
  start_row_in IN INTEGER := 1,
  failure_threshold_in IN INTEGER := 0,
  increment_in IN INTEGER := +1);

When you see that vertical bar in documentation for program headers, by the way, that means you are dealing with an overloaded program. The more variations of data you provide in your overloadings, the more useful you make your package. There is, of course, a price to pay for your overloadings. While the user thinks there is just one program to call, you know that in reality there is a different program for each overloading.

A key challenge, therefore, that comes with successful overloading is to figure out how to implement all those programs without creating a total mess in your package body. This challenge is addressed in the section called "Modularizing for Maintainable Packages" later in this chapter.

2.8.1.2 Fitting the program to the user

Does the idea of fitting a program to your user sound odd or unnecessary? If so, change your attitude. We write our software to be used, to help others get their jobs done more easily or more efficiently. You should always be on the lookout for ways to improve your code so that it responds as closely as possible to the needs of your users. Overloading offers one way to achieve a very close fit.

You may sometimes end up with several overloadings of the same program because developers will be using the program in different ways. In this case, the overloading does not provide a single name for different activities, so much as providing different ways of requesting the same activity. Consider the overloading for the PLVlog.put_line (shown in simplified form below):

PROCEDURE put_line
   (context_in IN VARCHAR2,
    code_in IN INTEGER,
    string_in IN VARCHAR2 := NULL,
    create_by_in IN VARCHAR2 := USER);

PROCEDURE put_line (string_in IN VARCHAR2);

The first header is the low-level version of put_line. It allows you to specify a full set of arguments to the program, including the context, the code, a string and the Oracle account providing the information. The second header asks only for the string, the text to be logged. What happened to all the other arguments? I suppressed them, because I found that in many situations a user of PLVlog simply doesn't care about all of those arguments. He simply wants to pass it a string to be saved. So rather than make him enter dummy values for all the unnecessary data, I provide a simpler interface, which in turn calls the low-level put_line with its own dummy values:

PROCEDURE put_line (string_in IN VARCHAR2) IS
BEGIN
   put_line (NULL, 0, string_in, USER);
END;

It wasn't necessary for me to take this step and provide this overloading. I could simply require that anyone who uses PLVlog.put_line provide values for all those non-defaulted parameters. If developers really had to use PLVlog, they would follow my bidding. And if I were on some kind of power trip, I would feel properly stroked. But if a developer could choose between PLVlog and another package or utility that didn't make him feel dumb, PLVlog would simply not be used. We almost always have choices. I would rather that my software be used because it was too useful and easy to use to reject.

2.8.1.3 Unambiguous, simple arguments

A less common application of overloading offers a way for developers to specify very easily which of the overloaded programs should be executed. The best way to explain this technique is with an example. The PLVgen package allows you to generate PL/SQL source code, including procedures, functions, and packages. Let's consider how to request the generation of a function.

A function has a datatype: the type of data returned by the function. So when you generate a function, you want to be able to specify whether it is a number function, string function, date function, etc. If I ignored overloading, I might offer a package specification like this:

PACKAGE PLVgen
IS
    PROCEDURE stg_func (name_in IN VARCHAR2);
    PROCEDURE num_func (name_in IN VARCHAR2);
    PROCEDURE date_func (name_in IN VARCHAR2);
END;

to name just a few. Of course, this means that a user of PLVgen must remember all of these different program names. Is it num or nbr? Stg or strg or string? Why use the four-letter date when the others are just three letters? Wow! That is very confusing. Let's try overloading of the kind previously encountered in this chapter. I will declare a named constant for each kind of data and then, well, it would seem that I really only need one version of the func procedure:

PACKAGE PLVgen
IS
    stg CONSTANT VARCHAR2(1) := 'S';
    num CONSTANT VARCHAR2(1) := 'N';
    dat CONSTANT VARCHAR2(1) := 'D';
    PROCEDURE func (name_in IN VARCHAR2, type_in IN VARCHAR2);
END;

I could then generate a numeric function as follows:

SQL> exec PLVgen.func ('booksales', PLVgen.num);

Now, I still need to know the names of the constants, so it is pretty much the same situation as we encountered in my first func attempt. Furthermore, I would like to be able to pass a default value to be returned by the generated function, so I really would need to overload as shown in the next iteration:

PACKAGE PLVgen
IS
    stg CONSTANT VARCHAR2(1) := 'S';
    num CONSTANT VARCHAR2(1) := 'N';
    dat CONSTANT VARCHAR2(1) := 'D';
    PROCEDURE func 
      (name_in IN VARCHAR2, type_in IN VARCHAR2, defval_in IN VARCHAR2);
    PROCEDURE func 
      (name_in IN VARCHAR2, type_in IN VARCHAR2, defval_in IN NUMBER);
    PROCEDURE func 
      (name_in IN VARCHAR2, type_in IN VARCHAR2, defval_in IN DATE);
END;

Might there not be a simpler way to handle this? Notice that the second parameter is a way for the user to specify the datatype of the function. You pass in a string constant, and PLVgen uses an IF statement to determine which constant you have provided. Why not skip the constant and simply pass in data itself of the right type? Then the PL/SQL runtime engine itself would automatically perform the conditional logic to determine which program to run, which code to execute. Consider this next version of the PLVgen package specification:

PACKAGE PLVgen
IS
    PROCEDURE func 
      (name_in IN VARCHAR2, type_in IN VARCHAR2, defval_in IN VARCHAR2);
    PROCEDURE func 
      (name_in IN VARCHAR2, type_in IN NUMBER, defval_in IN NUMBER);
    PROCEDURE func 
      (name_in IN VARCHAR2, type_in IN DATE, defval_in IN DATE);
END;

The named constants are gone, no longer needed. I can now generate a numeric function with a default value of 15,000 as follows:

SQL> exec PLVgen.func ('booksales', 1, 15000);

It doesn't really matter what value I pass as the second argument; it doesn't matter if the argument is a literal or a variable or an expression. It just has to evaluate to a number, so that the PL/SQL runtime engine will know to execute the code associated with the second header in the specification. What could be simpler? You want a numeric function? Pass a number -- any number -- as the type argument. You want a date function? Pass a date -- be it SYSDATE or some locally declared variable.

I am sure that many readers are looking at that last specification and wondering why I just didn't use the defval_in argument to determine the datatype of the function and skip the type_in argument entirely. Take a look at the final PLVgen package specification. I provide a default value of NULL for all the defval_in arguments. I reasoned that you shouldn't have to provide a default value for the function. So I do need that separate, second argument (always required since it has no default value) to guarantee that you will unambiguously specify one of the function generators.

PLVgen uses this technique both for the func procedures and the gas (get-and-set) procedures. Oracle Corporation also uses this overloading approach in the DBMS_SQL built-in package (check out the DEFINE_COLUMN procedure). In fact, it was the DEFINE_COLUMN overloading that gave me the idea for the overloading you find in the PLVgen package. It took me a while to think through what PL/SQL was doing with DEFINE_COLUMN; I found the simplicity simultaneously clever, devilishly simple, and extremely elegant. It is a technique we should all put into use whenever appropriate.