[Chapter 2] 2.6 Building Flexibility Into Your Packages

2.6 Building Flexibility Into Your Packages

Who is going to argue with this one? Sure, we want our code to be flexible, in a practical sort of way. It is quite another thing to internalize this issue in the context of packages and figure out how to take full advantage of the package structure to offer maximum flexibility.

If a program is going to be widely reusable, it should be able to adapt to different circumstances to meet different needs. It is easy to talk about flexibility. I have found that when it comes to packages there are two basic ways to be flexible when writing programs for others to use:

Offer lots of parameters in the parameter lists of the package's functions and procedures. This is the traditional, well-worn path.
Provide toggles or on-off switches, distinct from the main programs of the package, which modify the behavior of those programs. This approach takes advantage of the package structure to offer a new way of doing things.

It certainly makes sense to offer arguments in a packaged program unit to improve the flexibility of that individual program. Consider the display procedure of the PLVtab package, whose header is shown below:

   PROCEDURE display 
    (table_in IN 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);

This procedure has a whole bunch of parameters, and every one of them makes sense for the display of a particular table. Do you want to provide a header different from the default "Contents of Table"? Provide an argument to the header_in parameter. Do you want to display every fifth row? Pass in 5 for increment_in . Sensible defaults are, on the other hand, provided for almost every parameter, so you only need to provide values if you want to override these defaults.

What do you do, however, when you want to provide flexibility that affects the behavior of the package as a whole , not just for a particular program? What if you want to alter the configuration of a package for an entire session? Furthermore, what if you want to change the behavior of your package without changing the application code that uses your package?

Again, let's take a look at the PLVtab package for an illustration of this situation. PLVtab is a low-level package used throughout PL/Vision under many different circumstances. In some situations, I wanted to be able to display the row number in which the data is found. In other scenarios, I did not want any header to display before the table data was shown. Finally, I thought it would be useful to be able to see a translation of a blank line (i.e., does the line contain actual blanks or is it NULL or is it some other non-printing character?).

I could simply have kept adding new parameters to the display procedure (actually, adding new parameters to the nine different overloaded versions of display ) to handle all of these variations. I would then end up with a header for display that looked like this:

   PROCEDURE display 
    (table_in IN 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,
     use_header_in IN BOOLEAN := TRUE,
     show_rownums_in IN BOOLEAN := FALSE,
     show_blanks_in IN BOOLEAN := FALSE);

I don't know about you, but when I look at programs with more than six or seven parameters, my head starts to spin. Human brains are not, according to numerous studies, well equipped to deal with more than seven items at once. You could contend that these additional parameters increase the flexibility of the display procedure. I would argue, instead, that these additional parameters doom the PLVtab.display procedure to the dustbin of history. Few people will be brave enough to try to use it, particularly if they have to modify the default values of those trailing arguments.

Fortunately, certain aspects of the PL/SQL package provide an alternative to turning your procedure into a sinking ship (weighed down by too many parameters): you can build toggles into your packages that allow a user of the package to change the behavior of the utility with the "flip of a switch."

2.6.1 Toggling Package Behavior

You will find toggles appearing throughout the PL/Vision packages. A toggle is a set of three programs: two procedures that allow you to turn a feature on or off, and a function to tell you the current status (on or off). The liberal application of toggles can transform the usability of your packages. The easiest way to teach you this technique is to show you how I use it in PL/Vision.

In PLVtab, I did not add a use header argument to the nine display procedures. Instead, I offer a toggle or on-off switch using these three programs:

   PROCEDURE showhdr;
   PROCEDURE noshowhdr;
   FUNCTION showing_header RETURN BOOLEAN;

The showhdr program turns on the showing of the header. The noshowhdr turns off the display of the header. The showing_header function returns TRUE if the header is currently set to be shown. These three programs contain very little. They simply maintain and access a private global variable, as shown below:

   v_display_header BOOLEAN := TRUE;

   PROCEDURE showhdr IS
   BEGIN
     v_display_header := TRUE;
   END;

   PROCEDURE noshowhdr IS
   BEGIN
     v_display_header := FALSE;
   END;

   FUNCTION showing_header RETURN BOOLEAN IS
   BEGIN
     RETURN v_display_header;
   END;

How do I put these toggles to use? Suppose that in most cases in my application I wish to hide the header. Since the default value for v_display_header is TRUE, I must turn off the display of the header at the start of my session. I could do that in my SQL*Plus login.sql script as follows:

exec PLVtab.noshowhdr;

Alternatively, if I am using PLVtab within an Oracle Developer/2000 Oracle Forms screen, I might place this call inside the When-New-Form-Instance trigger:

PLVtab.noshowhdr;

If, at some point in my application, I want to display a table with its header, I can temporarily override the default setting as follows:

PLVtab.showhdr;
PLVtab.display (selected_comp_tab, v_tot_selected, 'Selected Companies');
PLVtab.noshowhdr;

2.6.2 Toggles for Code Generation

Now consider the PLVgen package. PLVgen generates many different kinds of PL/SQL code elements. I used PLVgen earlier in this chapter, in fact, to generate a template for a package to show you a recommended format for packages. Since there are many variations in the way you might want to generate your code, PLVgen contains nine toggles that affect the appearance and contents of the generated code. It is totally impractical to add nine arguments to every one of my two dozen code generator procedures. It is very practical, on the other hand, to offer you the toggles to set, in effect, your own standard approach to generating PL/SQL code.

To offer just two examples, the default settings for this PL/SQL code generator package are to include auto-generated comments and to not include a standard header for program units. I can, however, change those defaults with calls to the appropriate toggles as shown below:

SQL> exec PLVgen.usehdr
SQL> exec PLVgen.nousecmnt

These toggle programs set the values of private global variables in the package. These variables are then referenced to determine the behavior of the package. If you look inside the PLVgen.spb file (the package body), you will also see instances where I call PLVgen toggles from inside some code generators so that I can achieve just the behavior I desire. Consider the helptext package below.

   PROCEDURE helptext (context_in IN VARCHAR2 := PLVhlp.c_main)
   IS
      v_save BOOLEAN := using_hlp;
   BEGIN
      /* Turn off help, but then restore if necessary. */
      usehlp;
      put_help (context_in);
      IF NOT v_save THEN nousehlp; END IF;
   END;

This procedure generates a comment stub for help text. It calls the private put_help procedure to construct that stub. If, however, the user has previously turned off help text generation, this program will do nothing. So the helptext procedure saves the current setting for using help text, turns that toggle on, generates the help text, and then turns the help text setting off, if that was the previous setting.

2.6.3 Changing Package Behavior Without Changing the Application

One of the most exciting benefits of package toggles is that they allow a user of the package to modify the behavior of the package without changing any application code that calls the package element. Let's start with an example to explain that complicated statement, and then I will generalize.

Suppose you want to use the PLVlog package to keep track of any changes made to the emp table. To do this, you will make calls to PLVlog.put_line in the appropriate database triggers. Here is an example of one such call:

PLVlog.put_line ('insert', :new.empno, :new.empname);

This request logs the fact that I am inserting a new employee with the specified ID number and name. The log mechanism also records the current user, as well as date and time. This code works just fine and goes into production. Then my company, in the true enterprising spirit of the 1980s and 1990s, purchases a company ten times its own size (which means no more raises for me, since they must now use all their money to pay off interest on the assumed debt). Suddenly, I must add 25,000 employees to my emp table. My log table cannot handle this volume of data in its current structure. Furthermore, I don't even really want an audit of this activity. The data should just be "slammed" in and used as a new baseline for corporate employment.

If I did not have a toggle in PLVlog, what would I have to do to turn off logging? I can think of two options:

Go into each trigger and comment out the call to PLVlog.
Disable all triggers on the emp table.

The first approach should make you shudder. You never, ever want to have to go into production code and make such temporary changes -- even (especially?) if those changes are not in a program per se, but are instead a part of the data structures. The second solution is not much better. You have to write a script to disable all the triggers and then this code is disabled for all users of the application, not just the single process, which is going to batch load all of the new employees. So if you disable triggers, you have to deny access to the application by other users. Two very ugly prospects.

If, on the other hand, you have a PL/Vision toggle in place, this situation does not cause you any grief at all. Before you start the process to load the employees (let's call it session A), you simply execute this command:

SQL> exec PLVlog.turn_off

Now, whenever the database trigger calls PLVlog.put_line for DML initiated by session A, nothing happens. Why? Because the first thing put_line does is check the value of the private toggle variable (by calling the toggle function) as shown below:

IF logging OR override_in
THEN
   ... log the information ...
END IF;

You didn't have to change your program and you didn't have to modify the state of your database. From outside the package, you call the toggle program to reach inside the package and change the way the package will behave. This ability to leave your own code intact comes in particularly handy not only for special exceptions but also for testing, as I explore below.

2.6.3.1 The test/debug cycle in PL/SQL

A common debug and test cycle in PL/SQL shops goes like this:

You identify incorrect behavior in your program.
Unable to understand the cause of the behavior, you place numerous calls to DBMS_OUTPUT.PUT_LINE (or, with your purchase of this book, PL/Vision's much more friendly p.l procedure) and other kinds of tracing lines of code so that you can see what is going on.
You analyze the output, track down the problem, and fix it.
You finally decide that all the bugs are gone.
You notify your manager that the application is ready to go. Excitement mounts. Other organizations are told to start moving the code from test to production. Suddenly, you break out in a cold sweat and tell your bewildered manager to "hold off a minute."
You forgot about all that debugging code you littered into your application. It can't go into production like that. You have to go back into the program to comment out or outright remove all that trace code. No problem, you tell yourself. Easy to do...but there could be a problem. After all, any time you touch the code, you can break it. After any changes of any kind to your code, you really should retest.
So you have to go back to your manager and ask for more time to make sure everything really is all right. Not a pleasant situation in which to find yourself.

If, on the other hand, you used packages with toggles to trace your debugging activity (such as PLVtrc and even the lower-level p package), you would not have to worry about any of that. You could keep your code intact and simply issue a call to the appropriate package toggle to turn off any superfluous activity, as in:

SQL> exec PLVtrc.turn_off
SQL> exec p.turn_off

Of course, you can do more with toggles than simply turn functionality on and off. Remember that logging capability I built into my emp table triggers? Suppose that I want to write my log information to an operating system file instead of to a database table. That is a pretty major change in how the log will work, and a daunting task if the log mechanism is designed poorly. Yet with PL/Vision it requires no change at all to the database triggers. The call to PLVlog.put_line remains exactly the same. Instead of modifying that application's code, I can simply redirect the output of the logging package with a call to the appropriate procedure as follows:

SQL> exec PLVlog.to_file ('log.dat');

and then all subsequent calls to PLVlog.put_line for that particular Oracle session will write the information to the log.dat file on the server.

In my experience, package toggles make an enormous difference in the flexibility and usability of my packages. You can never add too many toggles. Just make sure that the default setting is the value that's normally desired. Then only those people who need flexibility in that particular fashion ever need to bother with the toggle. You can always add toggles later; it is generally not the kind of thing you have to plan in advance. This is particularly true if you have been aggressive in modularizing your package body code. If you have religiously avoided code redundancy and repetition (get it?), there will usually be just one place you have to apply the toggle to achieve a new level of flexibility.

I cannot overemphasize the importance of toggles in your packages. They are an essential element in transforming your package from a handy utility into a robust, flexible component or what is, in essence, a product.

To paraphrase an over-paraphrased saying: "If you toggle your package, they will use it."