Search the Catalog
Advanced Oracle PL/SQL Programming with Packages

Advanced Oracle PL/SQL Programming with Packages

By Steven Feuerstein
1st Edition October 1996
1-56592-238-7, Order Number: 2387
690 pages, $42.95, Includes diskette

Chapter 2 Best Practices for Packages

In this chapter:
Starting with Packages
Using Effective Coding Style for Packages
Selecting Package Names
Organizing Package Source Code
Constructing the Optimal Interface
Building Flexibility
Building Windows
Overloading for Smart Packages
Modularizing for Maintainable Packages
Hiding Package Data
Simultaneous Construction

Packages are the most important construct in PL/SQL for building reusable code and plug-and-play components, and for employing object-oriented design techniques. As you become more comfortable with the language, you will find more of your time spent inside packages--and using programs from packages built by other developers. This may be a very pleasant and rewarding experience--if the packages are designed and implemented properly. If, on the other hand, you decide to build your packages in the same helter-skelter method (or lack thereof) I run into way too often, life out there in front of the monitor may get pretty miserable.

This chapter discusses some of the most important "best practices" for package construction and goes into detail on an effective coding style for packages. If you follow the ideas presented below, you have a very good chance of writing packages that are readable, maintainable, and enhanceable. It is also much more likely that other developers will find your packages usable and useful. You will find additional explanation regarding these practices, along with examples of the application of these practices, in the sections covering specific PL/Vision packages.

The following list offers a summary of the best practices for packages covered in this chapter:

Starting With Packages

Late in July 1996, I received this note from one of my technical reviewers, John M. Beresniewicz:

I've built half a dozen pretty hefty packages, and still I find myself wondering at the start of implementing some new functionality: how should I do this? I think packages are intimidating developers out there (maybe I'm wrong) and part of the reason may be that it is very hard to decide what to put where and why. It seems like most of my packages start with an idea that becomes a JMB_procname stored procedure. (All initial experiments are named with the prefix JMB_ to let me know they are part of my playground.) As soon as the procedure becomes more than 100 lines long or contains code duplication or a related but different procedure suggests itself or needs to stash some persistent data, a package is magically born.

Once spawned, packages often have a life of their own, they grow and mature and sometimes die or are subsumed by larger packages. I don't know if there is an idea here, but something that makes deciding what and how to start a package may help developers... I suppose the whole book is just that in a sense.

There is definitely an idea in there, but my perspective is somewhat simpler than what John probably had in mind: Get out of the habit of building standalone procedures and functions. Instead, start with a package! It is certainly the case that most complex programs eventually mutate into or are absorbed by packages. There is nothing wrong with that evolutionary process. You can, however, save yourself some trouble by creating a package to hold that seemingly simple and lonely procedure or function.

If you start with a package, several benefits accrue:

You will never regret the minuscule amount of extra time required to encapsulate your standalone programs inside a package.

Using Effective Coding Style for Packages

A package is a collection of PL/SQL elements, including data structures (from cursors to constants) and program units (procedures and functions). Packages are generally the most complicated and extended pieces of code PL/SQL developers will write. To make matters worse, the current array of PL/SQL development environments do not offer any tools for viewing and managing a package as a collection. A package is treated and presented no differently from a single function--just a whole bunch of lines of source code.

As a result, it is up to you to design and write your package to make it as readable and maintainable as possible. There are two fundamental strategies you can employ to help meet this objective:

Strategy 1: Use all available techniques to make your code as clean, modular, and structured as possible.

Strategy 2: Come up with a consistent coding style and format for your packages--and get people to follow that style.

Many of the other best practices covered in this chapter address the first strategy--which is clearly the more important and difficult of the two. In this section, I suggest elements of a coding style for packages. It is absolutely critical that you adopt an effective coding style and employ it consistently. This style should be compatible, of course, with the style you use throughout your PL/SQL code. It should also, however, include components that reflect and support the structure and significance of the package.

The most basic elements of a package style are, first of all, no different from the style I encourage for all other kinds of PL/SQL code. These elements include:

The style elements I find valuable particularly for packages include the following:

The best way to demonstrate these coding styles is to show you the template I use for package construction. I have been writing a lot of PL/SQL code in the past year and I found myself typing the same words and phrases over and over again. To improve my productivity and also the consistency of my code, I built a package called PLVgen to generate PL/SQL programs (see Chapter 15, PLVvu: Viewing Source Code and Compile Errors). Example 1 shows the basic template of a package generated with PLVgen.

Note

The PLVgen.pkg procedure also generated the line numbers to go with the source code.

Example 1


SQL> exec PLVgen.pkg('emp_maint');

 1 CREATE OR REPLACE PACKAGE emp_maint
 2 /*
 3 || Program: emp_maint
 4 ||  Author: Steven Feuerstein
 5 ||    File: emp_maint.SQL
 6 || Created: APR 13, 1996 18:56:59
 7 */
 8 /*HELP
 9 Add help text here...
10 HELP*/
11
12 /*EXAMPLES
13 Add help text here...
14 EXAMPLES*/
15
16 IS
17 /* Public Data Structures */
18
19 /* Public Programs */
20
21    PROCEDURE help (context_in IN VARCHAR2 := NULL);
22
23 END emp_maint;
24 /
25
26 CREATE OR REPLACE PACKAGE BODY emp_maint
27 IS
28 /* Private Data Structures */
29
30 /* Private Programs */
31
32 /* Public Programs */
33
34    PROCEDURE help (context_in IN VARCHAR2 := NULL)
35    IS
36    BEGIN
37       PLVhlp.show ('s:emp_maint', context_in);
38    END help;
39 END emp_maint;
40 /

There are several features I would like to highlight in my package template:
LinesSignificance
2-7A standard header for the package, showing the author, filename, and date created.
8-14Stubs for help text. I have developed an architecture (and the PLVhlp package, described in Chapter 16, PLVgen: Generating PL/SQL Programs) to provide online help for PL/SQL programs. These comment blocks provide both inline code documentation and help text to users.
17-19Banners to identify the two main kinds of elements that can appear in a package specification: data structures and program units.
21Header for a procedure that delivers online help for this package. Of course, this should only be included if the online help package is being used.
23The END statement with the package name appended.
28-32Banners to identify the three kinds of elements that can appear in a package body: private data structures, program units, and the implementation of the public program units.
34-38The implementation of the help procedure. Notice that the procedure uses an end label with the program name and is also indented in multiple steps from the overall package.

The emp_maint package shown in Example 1 contains the most important elements of a package's "look and feel." All elements declared in the specification are indented in from the package definition statement. They exist within the context of the package, and that relationship is made clear through the indentation. The same rule holds true in the package body (you can see this with the definition of the help procedure). The banner comment lines, on the other hand, are left-justified to match the margin of the package itself. I do this to make sure that these boundary markers stand out as you scan the code.

The banners identifying the different sections become very critical when the package is full of many different elements and runs to hundreds or thousands of lines. They also provide an internal guide during development. As you write a new package program, you may find that you need to create another private variable or private function. If you have the banners in place, you can easily perform a search and then drop this new element into its rightful spot. The alternative (throwing the code in at whatever point of the package you happen to be coding) results in a very chaotic package that is difficult to follow and maintain.

As I make clear in the way I created Example 1, you can use PLVgen.pkg to generate a package with this (or a modified) format.

Choosing the Order of Elements

As with the declaration sections of procedures and functions, you must (both in the package specification and body) declare all variables and data structures before you declare any program units. But what about the order of these program units themselves? As you can see from my banners, I always try to define all my private modules before any of my public modules. These are the building blocks used by the public programs. I group them together so they are easier to locate.

Is this ordering strictly necessary? Yes and no. Yes, you must define a private program before it is referenced by another program in the package (public or private). No, you do not have to group them together. You could instead define all private modules just before they are used by their public counterparts. This can make sense if the private program is only used by a single public program. If it is shared by many public programs (or other private ones, for that matter), then this placement does not accurately reflect its role in the package.

You can, by the way, place the definitions of the public program units anywhere in the package body (after the variable declarations)--even after they are referenced by another program. How is this possible? Since their headers have already been established in the package specification, the PL/SQL compiler has all the information it needs to resolve the reference.

Selecting Package Names

Have you ever noticed that a package is never executed or referenced? The package is only a container for all the elements inside the package. In your code you will execute package-based procedures and functions. You will reference package-based constants, variables, cursors, and so on. Consequently, all references to package-based elements are accomplished with qualified notation: package.element. You should take this format into account when you name both the package and the elements within a package.

In this section I discuss the following aspects of naming package-based elements:

If you follow the advice in this section, you will design packages that are more easily used and understood by other developers.

Choosing Appropriate and Accurate Names

As a rule, developers are much too careless about the names they give to their packages and the elements inside those packages, (most importantly, procedures and functions.) There are two aspects to coming up with the right names for your code elements:

Have you ever thought about the structure of the names you choose? PL/SQL is a computer language. It is much simpler than human languages like Japanese or English, but it still has many of the same grammatical components, such as nouns and verbs. RAISE_APPLICATION_ERROR, for example, contains a verb (RAISE)-noun (APPLICATION_ERROR) combination, as in: "Raise this error." The built-in function, SUBSTR, is an example of a noun (SUBSTR), as in: "if the substring is NULL, then ask for a dollar amount."

PL/SQL on the other hand, is more complicated than human languages because you, the developer, get to make up words in the language as you go. You define new nouns and verbs every time you declare a variable or define a new program. This means that each and every one of us is, at least in part, responsible for the integrity of the PL/SQL language. Keep this in mind as you name your program elements. Let's apply this consideration to packages.

First of all, the name of the package should always be structured as a noun. The package itself does not do anything, so it cannot and should not be an action verb. The package name declares, as simply as possible, the contents of the package. If you are writing a package to analyze sales, the name of the package should be something like: 

sales_analysis
and not either of these: 

perform_sales_analysis
calculate_sales
It should also probably not be something as vague as "sales". There are many different aspects to sales; there would be no way to tell from the name that this package performs analyses on sales figures.

Beyond the package name itself, you must be very careful in your naming of elements within the package. A procedure is an executable statement, a command to the PL/SQL compiler. Consequently, the structure of the procedure name should be similar to a command: 

Verb_Subject
as in: 

Calculate_P_and_L
Display_Errors
Confirm_New_Entries
A function, on the other hand, is used like an expression in an executable statement. Because it returns, or represents, a value, the structure of a function name (as well as all constants and variables) should also be a noun: 

Description_of_Returned_Value
as in: 

Net_Profit
Company_Name
Number_of_Jobs
Earliest_Hire_Date
If I use the wrong grammatical structure for my names, they do not read properly.

Avoiding Redundancy

Keep in mind that when you reference a package element outside of the package you must use dot notation (package.element). As a result, you will want to avoid redundancy in your package and element names. For example, suppose I have a package named emp_maint for employee maintenance. One of the procedures in the package sets the employee salary.

Here is a redundant naming scheme: 

PACKAGE emp_maint
IS
   PROCEDURE set_emp_sal;
END;
With this approach, I would then execute the procedure as follows: 

emp_maint.set_emp_sal;
I do not need to mention emp again in the procedure name. The entire package is all about maintaining employees. That should be assumed in the names of all elements defined within the package. A more sensible approach would be: 

PACKAGE emp_maint
IS
   PROCEDURE set_sal;
END;
With this new approach, I can then execute the procedure as follows: 

emp_maint.set_sal;
In this way, I type less and the resulting code is more readable.

Avoiding Superfluous Naming Elements

I often recommend that you include as a suffix or prefix to an element name an abbreviation that indicates clearly the type of element. So whenever I declare a cursor, for example, I always append a suffix of "_cur" as shown in the example below: 

DECLARE
    CURSOR emp_cur IS
       SELECT ...;
BEGIN
    FOR emp_rec IN emp_cur
    LOOP
        ...
    END LOOP;
END;
You can go overboard with these abbreviations and end up with names that are unwieldy and trip over themselves. The package name is one of those instances. I recommend that you do not append suffixes like "pkg" or "pak" to the names of packages. It will be clear enough from the way the packaged elements are referenced and used that they are defined within a package. Let's look at an example to illustrate the point.

Suppose I define my emp_maint package as follows: 

PACKAGE emp_maint_pak
IS
   PROCEDURE set_sal;
END;
With this verbose approach, I then execute the procedure as follows: 

emp_maint_pak.set_sal;
What do I gain by including the "pak" in the call, except to add to my typing? There can be no doubt at all that the set_sal procedure is defined within a package.

Similarly, I have worked at companies whose naming conventions dictate that whenever you create a procedure, you must preface the name with "pr" as in: 

pr_calc_totals;
Function names must, of course, be prefaced with "fu" as in: 

v_totsal := fu_total_salary;
This is serious overkill; if you have conventions like these, you need to find a better balance between self-documentation, readability, and productivity.

Some readers may notice an inconsistency in my approach to using suffixes. I suggest that you do not include "pkg" in your package names. I do continue to recommend, on the other hand, that you use a suffix for cursors and records, such as emp_cur and obj_rec. Why not drop the suffix for all of these elements? After all, it is usually pretty clear when I refer to a cursor or record. The clearest justification has to do with avoiding name duplication within the same PL/SQL block scope. Package names must be unique within an Oracle account. Within a single package, however, you may well want to define records, cursors, PL/SQL tables, programmer-defined record TYPEs, and so on for, say, the emp entity. If you do not use standard suffixes (or prefixes), you will end up with a bewildering variety of names. Conventions based on the entity name--such as emp or dept or orders--offer the simplest and clearest way to distinguish between these different elements of the PL/SQL language.[1]

Organizing Package Source Code

Most Oracle shops still rely on SQL*Plus to create and compile PL/SQL programs. This means that the source code resides in one or more operating system files. To avoid losing control of that source, you should adopt some simple conventions for the extensions of your files. The approach I have taken is shown in the table below.
File ExtensionDescription
procname.spThe definition of a stored procedure.
funcname.sfThe definition of a stored function.
pkgname.sppA stored package definition that contains the code for both the package specification and the package body.
pkgname.spsThe definition of a stored package specification only.
pkgname.spbThe definition of a stored package body only.
scriptname.sqlAn anonymous PL/SQL block or a SQL*Plus script (SQL statement plus SQL*Plus commands).
procname.wpThe wrapped[2] definition of a stored procedure.
funcname.wfThe wrapped definition of a stored function.
pkgname.wppA stored package definition that contains the wrapped code for both the package specification and the package body.
pkgname.wpsThe wrapped definition of a stored package specification only.
pkgname.wpbThe wrapped definition of a stored package body only.
scriptname.sqlAn anonymous PL/SQL block or a SQL*Plus script (SQL statement plus SQL*Plus commands).

With your code separated and easily identified in this manner, you will be able to locate and maintain it more easily. You can fine-tune these extensions even more. For example, I often use the ".tab" extension for SQL*Plus Data Definition Language (DDL) scripts that create tables. The most important aspect of these naming conventions is the implied separation of package specification and body (sps and spb).

There are two advantages to creating and compiling specifications and bodies separately:

Creating Codependent Packages

Codependency is not just an issue for psychologists and the self-help publishing industry. It can also rear its ugly head with PL/SQL packages. Suppose that package A calls a program in package B, and that package B calls a program in package A. These two packages depend on each other. How can one be defined before the other? How can either or both of these packages be made to compile? Simple: define all specifications and then define all bodies.

I ran into this codependency problem just before I was to give a class on packages. I planned to give out a copy of PL/Vision Lite and started work on an installation disk. Most of my packages were stored in spp files. The package specification and body were, in other words, stored in the same file. So I placed calls to execute all of these scripts in my installation file and tested the process in a fresh (of PL/Vision) Oracle account. The installation failed miserably and I couldn't understand the problem. I was able to compile any of these individual packages in my existing PL/Vision account (PLV) without any difficulty.

Suddenly, I realized the problem: when I compiled a package in my PLV account, it could reference the other packages that already existed. The package would, as a result, compile successfully. In an account with no preexisting PL/Vision code, however, when I tried to compile the p package body (a very basic package used by almost every other package in PL/Vision), it could not find the PLVprs package, which was not yet defined because it referenced the p package (among others). PLVprs was compiled later in the installation script.

For about five minutes I despaired. Had I constructed a product that wasn't even capable of installing? Then I came to my senses. The package specification and body do not have to be compiled together. And if the p package relies on the PLVprs package, it only requires the package specification for PLVprs to be in place. The PL/SQL compiler only needs to know, in other words, that the p.l procedure is calling PLVprs.display_wrap properly--and that information is contained in the specification. I didn't have a faulty product. I had a faulty installation script!

Take a look at the PLVinst.sql file on your disk. This SQL*Plus script now installs the PL/Vision packages in a more sensible fashion. You will see that there are two phases to the installation of PL/Vision: first, all the package specifications are created, and then package body creation scripts are executed. In this way, I can leverage all the different, handy elements of PL/Vision in other parts of the product.

I learned from this experience that I should always separate the scripts for the creation of the package specification and body, even if the packages are very short and simple.

Note

This separation of specification and body will not work in all codependent situations. If the specification of package A references an element in package B, and the specification of package B references an element in package A, you will not be able to compile these two packages. Each specification requires the other specification to be previously defined, which simply isn't possible.

Constructing the Optimal Interface
to Your Package

The interface to your package consists of the names of the public elements and, in the cases of procedures, functions, and cursors, the parameter lists and RETURN datatypes.

The interface of your package is, in the broadest sense, affected by almost every best practice in this chapter. There are several recommendations that apply more narrowly to the interface (particularly the parameter lists); those are covered in the following sections. Before delving into those particulars, however, I need to step way back and address a philosophical issue of package design that is fundamental to making your packages as useful and usable as possible: the need to see other developers as users, and the impact that has on your package design.

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.

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.

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.

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:

  1. Offer lots of parameters in the parameter lists of the package's functions and procedures. This is the traditional, well-worn path.

  2. 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."

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;

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.

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:

  1. Go into each trigger and comment out the call to PLVlog.

  2. 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.

The test/debug cycle in PL/SQL

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

  1. You identify incorrect behavior in your program.

  2. 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.

  3. You analyze the output, track down the problem, and fix it.

  4. You finally decide that all the bugs are gone.

  5. 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."

  6. 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.

  7. 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."

Building Windows Into Your Packages

A special kind of toggle can be used to provide what I call a window into a package. This window allows a restricted view into the inner workings of a package, which can be critical to making the package usable in a complex, multilayered application.

As I've explained in Chapter 1, packages are broken up into the specification and the body. The specification defines those elements that can be called from outside of the package (the public elements). The body contains the implementation of public elements and also of private elements (those elements that can only be referenced inside the body of the package). This dichotomy allows us to hide quite securely implementation details that users need not be aware of in order to make use of the package.

This "information hiding" aspect of packages is a great feature--until a developer needs to know what is going on inside the package. The black box in this case can become a hindrance. For example, I built a package called PLVdyn (which stands for "PL/Vision DYNamic SQL") to make it easier for developers to use the built-in DBMS_SQL package. PLVdyn lets the user parse and execute dynamically constructed SQL and PL/SQL statements without fussing with all the details inherent in the built-in package.

With PLVdyn, you construct a SQL statement and pass that string to a PLVdyn program for parsing or execution. It's a big time-saver, but it also implies a loss of some control. You trust PLVdyn to do the right thing--and it does. The question that is more likely in need of an answer: what is your code passing to PLVdyn?

The code we write to construct the dynamic SQL statement is often complicated. The PL/Vision packages themselves make extensive use of PLVdyn. As I tested PLVdyn, I often found that I wanted to see the SQL statement that PLVdyn was executing, so I could verify that my calling program (in PLVio or PLVlog or...) had put the SQL together properly. This was not, conceptually, a difficult problem. I could simply place calls to DBMS_OUTPUT before each of my calls to PLVdyn modules. In this way, I would not have to change PLVdyn (it is not, after all, the fault of PLVdyn--or its author!--that I wasn't sure what my code was doing). With this approach, if I used PLVdyn.ddl to execute a DDL statement, I could simply preface it with a call to p.l (the PL/Vision version of DBMS_OUTPUT) as follows: 

p.l (ddl_statement);
PLVdyn.ddl (ddl_statement);
For all its simplicity, there is a key drawback to this solution: I would have to add calls to p.l in all the places I call a PLVdyn program. This meant going back to existing programs to make changes. I would have to remember to add this call whenever I used PLVdyn or felt the need to trace my activity. In either case, it involved changes to my code. Such changes invite misspellings and logical bugs.

This weakness, combined with the need to see what PLVdyn is doing almost caused me to abandon PLVdyn. Rather than use the package, developers would cannibalize it for the parts that seem useful. Or they would simply ignore this package-based solution and write all of their dynamic SQL directly into their programs. The result? Applications that do not reuse prebuilt code, but instead create maintenance and enhancement nightmares.

Centralizing the View Mechanism

A far superior approach would allow users to view the string they passed to PLVdyn without changing any of their own code. This view mechanism would be sophisticated enough to handle any number of different scenarios for SQL statement output, such as very long strings. The way to implement this approach successfully is to build the viewing feature directly into the PLVdyn package itself.

With the trace implemented inside PLVdyn, I can avoid modifying my own code when the output from that trace is needed. Instead, I can simply call a program in the PLVdyn package to tell it turn on the trace. I can then view the output until it is no longer needed and call a program to direct the package to turn off the trace. This sequence of commands is illustrated below, along with the toggle, a call to the PLVcmt which turns off commit processing. I want to run a test of my program to shift employees; I want to check my dynamic SQL without actually committing any possible mistakes. 

SQL> exec PLVdyn.showsql
SQL> exec PLVcmt.turn_off
SQL> @test_move_emps
PLVdyn: INSERT INTO emp VALUES (1506, 1105, 'SMITH')
PLVdyn: UPDATE emp SET sal = 150000
PLVdyn: UPDATE emp SET hiredate = SYSDATE
I execute these steps, look over the trace, and decide that this all looks good. I then turn on commit processing, turn off the SQL trace, and run the program. All without making a single change to the move_emps program. 

SQL> exec PLVdyn.noshowsql
SQL> exec PLVcmt.turn_on
SQL> @move_emps
By incorporating the trace into PLVdyn, I can't deny that I make my own job that much more difficult. I have to write the code for the trace and then figure out how best to implement it comprehensively for all PLVdyn modules. Yet once I have provided this feature, it is available for all users of PLVdyn. This kind of tradeoff (author effort vs. user ease of use) is always worthwhile in my view.

There are two aspects to keep in mind when building a trace or window into a package:

  1. You need to provide the programmatic interface so that a developer can turn on/off the trace. This interface is a typical PL/Vision toggle and will usually take the same form in any package. As a result, it is a prime candidate for generation with the PLVgen package (see the toggle and gas procedures in Chapter 15).

  2. You need to implement the trace carefully inside your package. What information will you provide? What mechanism will you use to display the trace? DBMS_OUTPUT or the p package or maybe even the PLVlog package? And, most importantly, where will you put the trace in the package so that you can minimize the number of different places it will appear? Remember: you want to avoid code redundancy. If you were aggressive about modularizing your package body, you should be able to identify a few programs or maybe even just one program (when you wish upon a star...) in which the trace can be implemented, but will then be used by all programs in the package. This process is explored in the next section.

Designing the Window Interface

The first step in installing a window in a package is to design the interface for the window, also referenced in this section as a trace. To do this, I must ask and answer these questions:

The easiest way for developers to specify their desires is to call a procedure. The first inclination might be to build a single procedure that accepts actions such as ON or TRACE vs. OFF or NO_TRACE as a single parameter. The header for such a procedure would look like this: 

PROCEDURE set_trace (onoff_in IN VARCHAR2);
The developer would then call set_trace in SQL*Plus or another execution environment as follows: 

SQL> exec PLVdyn.set_trace ('ON');
SQL> exec PLVdyn.set_trace ('OFF');
The problem with this approach is that the developer must then know what value to pass to the procedure to achieve the proper effect. Is it ON or YES? Is the value case-insensitive? Why, I ask myself in this situation, should a developer have to worry about such things? Even the seemingly clear TRUE/FALSE Boolean values are open to interpretation. If you generally do not want the trace in action, then TRUE should mean "keep it off." If you are often interested in the output of the trace, you would most naturally conclude that TRUE means "show the trace."

A completely different technique is to provide two different programs to turn the trace on and off. The names of the programs themselves would make it very clear what they did. You don't have to worry about getting a literal value wrong. If you type in the wrong program name, the runtime engine will inform you immediately of the error.

I can employ a very generic naming convention for this pair of on/off procedures as follows: 

PROCEDURE turn_on;
PROCEDURE turn_off;
As an alternative, I could use names that describe the type of trace being provided. This is especially important when more than one trace is provided in the same package.

In PLVdyn, I opted for the less generic style and so provide these two procedures in the package specification: 

PROCEDURE showsql;
PROCEDURE noshowsql;
With these programs in place, I could turn on my trace in SQL*Plus as follows (ssoo.sql is a PL/Vision script that sets SERVEROUTPUT to ON, enabling the DBMS_OUTPUT package in SQL*Plus): 

SQL> @ssoo
SQL> execute PLVdyn.showsql;
The third program of the package trace is a function that lets me know the current status of the PLVdyn trace facility. In the PLVdyn package, I offer the showing_sql function. This program returns TRUE if the trace is turned on, FALSE otherwise: 

FUNCTION showing_sql RETURN BOOLEAN;
The PLVdyn uses this function (shown later in this section) when trying to determine whether or not the SQL should be shown. Even the package body respects the interface of the toggle and uses the function instead of a direct reference to the private variable.

The showing_sql function also provides a sense of completeness to the interface for the trace facility. A developer using PLVdyn can remain within the API of the package to obtain all the information she needs to use the trace and get the most out of the package.

The full implementation of the trace facility in PLVdyn even goes a bit further than you have seen so far. The header for the showsql procedure is: 

PROCEDURE showsql (start_with_in IN VARCHAR2 := NULL);
You can, in other words, provide a string to showsql to indicate the point in the SQL from which you want to view the text. You could ask to see, for example, everything after the WHERE keyword by calling showsql as follows: 

SQL> exec PLVdyn.showsql ('where');
This additional flexibility can come in handy when you don't want to have to read your way through a long, complicated SQL statement. It's quite easy to provide additional functionality to a package window once it has been put in place. My first implementation of showsql did not support this "start with" argument nor did it display long strings very gracefully. I was able to add all of this functionality incrementally as I identified the need.

Implementing the Window

Now that the interface to the window has been defined, I need to implement the code that will fill that window with data. One of the biggest challenges in crafting a trace facility in a package like PLVdyn is to figure out where to put the trace. PLVdyn is a big package, offering many different high-level operators to perform dynamic SQL.

I did not want to have to add calls to DBMS_OUTPUT all over the package. That would make it more difficult to maintain and enhance. So I analyzed the way the package (and dynamic SQL) works and found my attention drawn back continually to the open_and_parse function. Before you can execute a SQL statement, you have to open a cursor and then parse the SQL.

The open_and_parse function was one of the first programs I created in PLVdyn, and it is used by all other programs before they move on to their specific dynamic tasks. As a result, open_and_parse acts as a kind of gateway into the rest of the package. I reasoned, therefore, that if I added the trace capability to open_and_parse, I could then make the trace available to the entire package. Now that's a payoff from earlier modularization! Here is the body of open_and_parse: 

FUNCTION open_and_parse (string_in IN VARCHAR2,
    mode_in IN INTEGER := DBMS_SQL.NATIVE) RETURN INTEGER
IS
   cur INTEGER := DBMS_SQL.OPEN_CURSOR;
BEGIN
   display_dynamic_sql (string_in);
   DBMS_SQL.PARSE (cur, string_in, mode_inDBMS_SQL.V7);
   RETURN cur;
END;
As you can see, the display_dynamic_sql procedure intercepts the string that is going to be parsed by the built-in PARSE procedure. A simplified version of the display program is shown below: 

PROCEDURE display_dynamic_sql (string_in IN VARCHAR2)
IS
BEGIN
   IF showing_sql
   THEN
      PLVprs.display_wrap ('PLVdyn: ' || v_string, 60);
   END IF;
END;
Notice that display_dynamic_sql only displays information when showing_sql returns TRUE. It also takes advantage of the PLVprs.display_wrap procedure to show long SQL statements in paragraph form wrapped at a line size of 60 columns.

The open_and_parse program is called six times in PLVdyn. Actually, as I wrote this section, I had been thinking that the count would be even higher. It turns out that any of the programs that call open_and_parse are, in turn, called by other PLVdyn modules, keeping the direct references to open_and_parse from exploding. The display_dynamic_sql program, on the other hand, is called just once. When I want to upgrade or change the functionality of my trace, I can go to this one program and make all the changes.

The way I was able to implement the trace in PLVdyn is a best-case scenario. The requirement in dynamic SQL to parse your SQL statement, combined with my initial modularization and reuse of open_and_parse, offered an easy way to put the trace in place. In other PL/Vision packages and your own as well, you may need to include calls to your trace display mechanism more than once. That's fine, as long as you do create a separate procedure to display the information (do not just call DBMS_OUTPUT.PUT_LINE directly in your package) and as long as you minimize the number of repetitions of the program.

Summarizing the Window Technique

The trace facility of PLVdyn illustrates some important principles of both generic package structure and high-quality reusable code (see Figure 1). First, the public-private nature of the package allows me to construct a window into PLVdyn. This window offers a very controlled glimpse into the interior of the package. I let developers view the dynamic SQL string, but they can't look at or do anything else. This level of control allows Oracle to give us all of those wonderful built-in packages like DBMS_SQL and DBMS_PIPE. And it lets developers provide reusable PL/SQL components to other developers without fearing corruption of internal data structures.
Figure 2-1

The three elements of the interface to the window are:

  1. A procedure to open the window and turn on the trace

  2. A procedure to close the window and turn off the trace

  3. A function that returns TRUE if the window is open, FALSE otherwise

Your package can have more than one window, in which case you will want to have a distinct triumvirate of programs for each trace (multiple toggles, in other words). If you have a number of different kinds of windows, you may also want to build a master switch that turns on and off all of the windows at once. The PLVgen usemin and usemax procedures are good examples of this meta-toggle for multiple flags in the package.

As you build more and more sophisticated packages, you will find yourself building code in multiple layers that interact in ways mysterious to normal human beings. The windowing technique illustrated by PLVdyn.showsql will be absolutely critical to making your packages widely accessible and usable. If you do not provide clearly defined windows into your inner workings, there is a good chance that developers will first be baffled and then become frustrated. The end result is that they will not use your packages.

Overloading for Smart Packages

One of the most powerful aspects of the package is the ability to overload program units. When you overload, you define more than one program with the same name. These programs will differ in other ways (usually the number and types of parameters) so that at runtime the PL/SQL engine can figure out which of the programs to execute. You can take advantage of the overloading feature of packages to make your package-based features as accessible as possible.

Does overloading sound unfamiliar or strange? Well, have you ever used the TO_CHAR function? If so, then you have already been enjoying the creature comforts of overloading. TO_CHAR converts both numbers and dates to strings. Have you ever wondered why you don't have to call functions with names like TO_CHAR_FROM_DATE or TO_CHAR_FROM_NUMBER? Probably not. You probably just took TO_CHAR for granted, and that is how it should be.

In reality, there are two different TO_CHAR functions (both defined in the STANDARD package): one to convert dates and another to convert numbers. The reason that you don't have to care about such details and can simply execute TO_CHAR is that the PL/SQL runtime engine examines the kind of data you pass to TO_CHAR and then automatically figures out which of the two functions (with the same name) to execute. It's like magic, only it's better than magic: it's intelligent software!

When you build overloaded modules, you spend more time in design and implementation than you might with separate, standalone modules. This additional up-front time will be repaid handsomely down the line in program productivity and ease of use.

You will not have to try to remember the different names of the modules and their specific arguments. Properly constructed, overloaded modules will have anticipated the different variations, hidden them behind a single name, and liberated your brain for other, more important matters.

See Chapter 16, Procedures and Functions, of Oracle PL/SQL Programming for a more comprehensive coverage of overloading restrictions and examples.

When to Overload

When you overload, you take the first step towards providing a declarative interface to PL/SQL-based functionality. With a declarative approach, a developer does not write a program to obtain the necessary functionality. Instead, she describes what she wants and lets the underlying code handle the details (this follows the approach used by the SQL language). The process of overloading involves abstracting out from separate programs into a single action.

You want to display a date? You want to display a number? You want to display a string and a number? Hold on a minute. The common element is that you want to display something--lots of somethings, in fact. So don't create display_date, display_string, etc. procedures. Instead, offer a single display procedure, which is in fact many overloaded display procedures.

With the overloading in place, your user must only remember this: when I want to display something, I simply ask the display program to take care of it for me. What do I pass to it? Whatever I want it to display. I will not (and do not have to) worry about the how of the display mechanism. Those details are hidden from me.

Here are some of the circumstances that cause the PL/SQL fairy to whisper in my ear "Overload, overload...":

I explore these circumstances in the following sections.

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.
Why Name a Package "p"?

I talk about coming up with names for your package that are clear, accurate, and easy to remember. Then I showcase the p.l procedure in my best practice on overloading. Surely I am not going to argue that p is a good name for a package--and what about l as the name of a procedure? What justification could I possibly have for the names I chose for these elements?

My p.l procedure is a substitute for and rebellion against the DBMS_OUTPUT.PUT_LINE procedure. I hated the 20 characters I had to type to generate output from my PL/SQL programs (in uppercase no less, since that is my convention). I was frustrated by the limited overloading of the package itself. So when I set out to build my own layer of code around DBMS_OUTPUT, I was determined to use the fewest characters possible. The result is p.l.

I found it difficult to justify this obscure name, but John Beresniewicz, my able and deep-thinking reviewer, contributed this observation: "It's possible that the need for clearly descriptive (i.e., lengthy) names is directly proportional to the amount of work performed by the procedure and inversely proportional to the frequency of use. That is, procedures that implement a high level of functionality need clearly descriptive names and they will presumably be called less frequently (and these long names won't clog up the source code). Conversely, low-level routines called frequently need shorter names (to avoid clog) but nobody forgets their names (even if cryptic) since they are used all the time." I couldn't have stated it better myself!

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.

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.

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.

Developing an Appreciation of Overloading

You should now have a solid feeling for the technique of overloading. To build excellent packages, however, you will need to move beyond simply overloading occasionally to overloading at every possible opportunity and in every possible way. You need to develop a sensitivity to when you should overload and how you can overload most effectively.

The benefits and the beauty of overloading can be appreciated fully only by using overloaded programs--and then in most cases, you won't even notice, because overloading hides the underlying complexity so you can concentrate on more important issues. You will, I hope, get a sense of the value of overloading from using--and perhaps even extending--PL/Vision. Do take some time to pursue the various spb files (the package bodies) and examine the many different examples of overloading you will find there.

When I've successfully overloaded an interesting set of programs and succeeded in hiding much of the underlying complexity of my package, I get an all-the-pieces-falling-into-place feeling and a this-is-as-it-should-be feeling and a sense of how-elegant! If you think I sound a bit strange, please withhold judgment until you do some really fancy and extensive overloading and then tell me how you feel.

The more you overload your packaged procedures and functions, the more functionality you offer to your users. Where overloading is appropriate, it is also impossible to overdo your overloading. If you see another interesting and useful combination, if you see a way to simplify the way a user passes information to your package, then overload for it! It will always be the right thing to do; your biggest challenge will be in figuring out how to implement all these overloadings in a modular and maintainable fashion. This issue is addressed in the next section.

Modularizing for Maintainable Packages

To build packages that are both immediately useful and enhanceable over the long-run, you must avoid any kind of code duplication inside the package. You need to be ready, willing, and able to create private programs in your package to contain all the shared code behind the public programs of the package. The alternative is a debilitating reliance on the Windows cut-and-paste feature. Cut-and-paste will let you build rapidly--but what you will be building is a wide, deep hole from which you will never see the light of day.

I set a simple rule for myself when building packages: never repeat a line of code. Instead, construct a private module and call that module twice (or more, depending on the circumstances). By consolidating any reused logic rigorously, you have less code to debug and maintain. You will often end up with multiple layers of code right inside a single package. These layers will make it easier to enhance the package and also to build in additional functionality, such as the windows and toggles discussed earlier in this chapter.

The PLVdyn package offers an example of in-package layering. As explained in the section on "Building Windows into Packages," the open_and_parse function consolidates the open and parse phases of dynamic SQL. This function is then called by many other higher-level operators in PLVdyn. These operators are in turn called by still other programs. The result is at least five layers of code as shown in Figure 2.

Note

While it is uncommon, it is certainly possible for two programs to have the same name (and therefore be overloaded) but have little or nothing in common in their implementation. In this situation, you will probably not be able to consolidate the code in the package body for these two programs into a single, private program. There is nothing wrong with this situation (except that it might raise question of why you are using the same name for both programs).

The need for modularization inside a package is most clear when it comes to implementing overloaded programs. The next section will explore implementation strategies for overloading.
Figure 2-2

Implementing Overloading with Private Programs

Overloading and modularization must be considered two sides of the same coin if you are going to implement your package properly. The previous section encouraged you to overload frequently and thoroughly. When you overload, you offer multiple versions of the same program. By doing so, you simplify the interface for the user, which is critical. At some point, however, you have to deal with the package body. If you've overloaded a particular procedure ten times, are you going to end up with ten completely separate procedure bodies and a large volume of redundant code that is very difficult to maintain?

Let's first understand the problem you can encounter inside packages when you overload. Consider that simplest (at first glance) of packages: the p package. You might be tempted to think that all it really does is provide a layer of code over the DBMS_OUTPUT.PUT_LINE built-in so that you can pass it more and different types of data. If that were the case, I could implement the p.l procedure as shown by the two of seven implementations below: 

PROCEDURE l (char_in IN VARCHAR2, number_in IN NUMBER) IS
BEGIN
   DBMS_OUTPUT.PUT_LINE (char_in || ': ' || TO_CHAR (number_in));
END;

PROCEDURE l 
   (char_in IN VARCHAR2, date_in IN DATE,
    mask_in IN VARCHAR2 := PLV.datemask) 
IS
BEGIN
   DBMS_OUTPUT.PUT_LINE
      (char_in || ': ' || TO_CHAR (date_in, mask_in));
END;
I have achieved the objective of overloading by taking on the job of combining the different pieces of data before passing it to the built-in package. No need for a private program shared by all the l procedures, is there? Well, that depends on just how useful you want that package to be.

Let's pretend that it is July 1994. I am writing Oracle PL/SQL Programming and just beginning to get a handle on packages. The p package (at that time called the do package) is one of my first and I throw it together, just as you see it above: a "raw" call to DBMS_OUTPUT. Then I start to use it to debug the PLVlst package (as it first appeared in that book) and at some point pass it a string with 463 characters. Suddenly, my program is generating a VALUE_ERROR exception. After a hour of debugging, I realize that the problem is not occurring in PLVlst, but in my p package. The DBMS_OUTPUT.PUT_LINE program cannot handle values with more than 255 bytes. I mutter venomously about the brain-dead implementations proffered at times by Oracle Corporation and quickly move to fix the problem, as you can see below: 

PROCEDURE l (char_in IN VARCHAR2, number_in IN NUMBER) IS
BEGIN
   DBMS_OUTPUT.PUT_LINE 
      (SUBSTR (char_in || ': ' || TO_CHAR (number_in), 1, 255));
END;

PROCEDURE l 
   (char_in IN VARCHAR2, date_in IN DATE,
    mask_in IN VARCHAR2 := PLV.datemask) 
IS
BEGIN
   DBMS_OUTPUT.PUT_LINE
     (SUBSTR (char_in || ': ' || TO_CHAR (date_in, mask_in), 1, 255));
END;
Remember, I do this for all eight versions of the l procedure, not just the two you see. Well, that certainly takes care of that problem! So I continue my debugging and soon discover that when I ask DBMS_OUTPUT.PUT_LINE to display a NULL value or any string that LTRIMs to NULL, it just ignores me. I do not see a blank line; it just pretends that I never made the call. This is very confusing and irritating, but again the fix is clear: use the NVL operator. So now each of the l procedures looks like this: 

PROCEDURE l (char_in IN VARCHAR2, number_in IN NUMBER) IS
BEGIN
   DBMS_OUTPUT.PUT_LINE 
      (NVL (SUBSTR (char_in || ': ' || TO_CHAR (number_in), 1, 255), 
       'NULL'));
END;

PROCEDURE l 
   (char_in IN VARCHAR2, date_in IN DATE,
    mask_in IN VARCHAR2 := PLV.datemask) 
IS
BEGIN
   DBMS_OUTPUT.PUT_LINE
     (NVL (SUBSTR (char_in || ': ' || TO_CHAR (date_in, mask_in),
       1, 255), 'NULL'));
END;
On and on I go, discovering new wrinkles in the implementation of DBMS_OUTPUT.PUT_LINE and scrambling to compensate in each of my eight procedures (see Chapter 6, PLV: Top-Level Constants and Functions, for more details on these wrinkles). Eventually each of my l procedures grows very convoluted, very similar to all the others, and very tedious to maintain. This is clearly not the way to go.

Now compare that process with the final state of the p package. Each of the l procedures consists of exactly one line of code, as you can see below: 

PROCEDURE l 
   (char_in IN VARCHAR2, number_in IN NUMBER,
    show_in IN BOOLEAN := FALSE) 
IS
BEGIN
   display_line (show_in, char_in || ': ' || TO_CHAR (number_in));
END;

PROCEDURE l 
   (char_in IN VARCHAR2, date_in IN DATE,
    mask_in IN VARCHAR2 := PLV.datemask,
    show_in IN BOOLEAN := FALSE) 
IS
BEGIN
   display_line 
      (show_in, char_in || ': ' || TO_CHAR (date_in, mask_in));
END;
Only two actions are performed inside the l procedure:

  1. Create the string to be displayed (usually occurring right inside the call to display_line), and

  2. Call the private module, display_line, to handle all the other issues.

The display_line procedure, in turn, looks like this: 

PROCEDURE display_line (show_in IN VARCHAR2, line_in IN VARCHAR2)
IS
   v_maxline INTEGER := 80;
BEGIN
   IF v_show OR show_in
   THEN
      IF RTRIM (line_in) IS NULL
      THEN
         put_line (v_prefix || PLV.nullval);

      ELSIF LTRIM (RTRIM (line_in)) = v_linesep 
      THEN
         put_line (v_prefix); 

      ELSIF LENGTH (line_in) > v_maxline
      THEN
         PLVprs.display_wrap (line_in, v_maxline-5, NULL);

      ELSE 
         put_line 
            (v_prefix ||
             SUBSTR (line_in, 1, c_max_dopl_line-v_prefix_len));
      END IF;
   END IF;
END;
Wow! It got really complicated, didn't it? In the final version of p.l, in fact, you can turn off the display of information using the built-in toggle. You can display long lines in paragraph-wrapped format. You can identify a character as a line separator so that white space can be preserved inside stored code and displayed as true blank lines in SQL*Plus.

I didn't come up with all of these features in a single flash of inspiration. I built them in over a period of months. Once I had transferred all common logic into the display_line procedure, however, it was a cinch to provide significant new functionality: I only had to make the changes in one location in my package. No user of the p package ever calls the display_line procedure; it is hidden. It exists only to consolidate all the common logic for displaying information.

I use this same approach throughout PL/Vision. Again and again, you will see the many overloadings of the package specification reduced to a single program inside the package body. I like to think of this of the overload-modularize diamond for packages, which is shown in Figure 3. The upper point of the diamond is the user view: a single action (i.e., overloaded name) known and called by the user. The facets of the diamond broaden out to the different, overloaded programs in the specification. The lower point of the diamond represents the narrowing of the different programs to a single private program in the package body.
Figure 2-3

Sometimes creating this diamond shape in your packaged code is straightforward. The p package illustrates this simple case. The only difference between each of the overloaded programs is the way the string is constructed for display. In other packages, it takes lots more thought and creative programming to come up with a way to conform to my "only in one place" rule. The PLVtab package is such a package; in fact, the complexity of modularizing the internals of PLVtab resulted in what I call the lava lamp effect.

Lava Lamp Code Consolidation

The objective of PLVtab is to make it easier for developers to use PL/SQL tables, particularly when it comes to displaying the contents of these tables. PLVtab predefines a set of table TYPE structures, such as tables of numbers, strings of various lengths, Booleans, and dates. It then offers a separate display procedure for each of the table TYPEs. Since each table TYPE is a different datatype, a separate, overloaded program is needed for each TYPE. The headers for two of these follow: 

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

PROCEDURE display 
 (table_in IN vc30_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);
As you can see from all of the parameters in the display procedures, PLVtab offers lots of flexibility in what you display and how you display it. This kind of flexibility always means a more complex implementation behind the scene in the package body. In fact, I use 184 lines of code spread across two private procedures to handle all the logic. Yet the body of each display procedure consists of just three lines as illustrated by the number_table display procedure below: 

PROCEDURE display 
   (table_in IN number_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)
IS
BEGIN
   internal_number := table_in;
   internal_display
      (c_number, end_row_in, header_in, start_row_in, 
       failure_threshold_in, increment_in);
   internal_number := empty_number;
END;
The first line copies the incoming table to a private PL/SQL table of the same type; the second line calls the consolidated, internal version of the display program; and the third line empties the private PL/SQL table to minimize memory utilization. These same three lines appear in each of the nine display procedures, the only difference being the type of the private PL/SQL table and the first argument in the call to internal_display. This value tells internal_display which table is to be displayed. This approach allows me to create the lower point of my diamond: a single program called by each display program.

The difference in this case--what I call the lava lamp effect--is that deep within the internal_display procedure, I broaden out my code again (creating a base for my lava lamp; see Figure 4) with a large IF statement.
Figure 2-4

The main algorithm of internal_display is this WHILE loop: 

WHILE in_range (current_row) AND within_threshold
LOOP
   display_row
      (type_in, failure_threshold_in, increment_in, 
      count_misses, current_row, within_threshold);
END LOOP;
which translates roughly as "display each row within the specified range." The display_row is another private procedure that converts the type_in argument (the type of table being displayed) and the current_row into the row value to be displayed. To do this, it uses a big IF statement, a portion of which is shown here: 

...
ELSIF type_in = c_date
THEN
   rowval := TO_CHAR (internal_date (current_row), PLV.datemask);

ELSIF type_in = c_integer
THEN
   rowval := TO_CHAR (internal_integer (current_row));

ELSIF type_in = c_number
THEN
   rowval := TO_CHAR (internal_number (current_row));

ELSIF type_in = c_vc30
THEN
   rowval := internal_vc30 (current_row);
...
The overloading and modularization in PLVtab (and PLVgen as well) reminds me of playing an accordion: First, I squeeze in to present a single program for the user. Then I pull out to define the many overloadings in the specification. Squeeze back in to implement all those overloadings with a single private module. Pull back out inside that private module to handle all the different types of data. It may seem like a convoluted road to travel, but the end result is code that is very easily maintained, enhanced, and expanded.

As an exercise for the reader, I suggest that you perform this exercise: make a copy of PLVtab.sps and PLVtab.spb and see if you can figure out the steps required to add support for another type of PL/SQL table.

Hiding Package Data

You implement PL/SQL-based global data with package data. Package data is any data structure declared in a package body or specification. There are two kinds of package data: public data (declared in the specification) and private data (declared in the body).

What's the difference between public and private? Public global data is the proverbial "loose cannon" of programming. Public package data is certainly very convenient. Simply declare a few variables in a package specification and they are available from/to any module. If you need to get a piece of information, just grab it from the global. If you want to change the value of that variable, go at it. Reliance on global data structures, however, leads to two significant problems:

You don't have to create these troublesome globals to gain many of the advantages of PL/SQL global data structures. You can regain control of your package data and also ease your maintenance and enhancement frustrations by building a programmatic interface around your data. This interface is also referred to as get-and-set programs or "access routines," since they usually get and set the values of data and control access to those data structures.

Gaining Control of Your Data

I recommend, in fact, that you never define variables in the specification of a package (except when explicitly needed that way, as discussed at the end of this section). Instead, you always declare the variable in the package body. You then provide a procedure to set the value of that variable and a function to retrieve the value of that variable.

Let's look at a very simple example to drive home the point and then move on to more interesting applications of this practice. Suppose I have a profit-and-loss package that maintains a "last statement date" in a package variable. With the variable defined in the specification, my package looks like this: 

PACKAGE P_and_L 
IS
   last_stmt_dt DATE;                                                        
END P_and_L;
Suppose further that I have a business rule that applies to the last statement date: it can never be in the future. Since the variable is defined in the package specification, any user with execute authority on this package can directly reference and modify the variable as shown in these code fragments: 

P_and_L.last_stmt_dt := SYSDATE + 12;
v_newdate := P_and_L.last_stmt_dt;
In the first line, my code violates the business rule--and there is nothing I can do to stop this violation.

Let's now move the last_stmt_dt inside the package body. When I do this, I must write some code to provide a programmatic interface to that date variable. The resulting package specification and body shown in Example 2 provide get-and-set routines to get the current value of the last statement date and also set the value of that variable.

Example 2

PACKAGE P_and_L
IS
   FUNCTION last_date RETURN DATE;
 
   PROCEDURE set_last_date (date_in IN DATE);
END P_and_L;
 
PACKAGE BODY P_and_L
IS
   last_stmt_dt DATE; 
 
   FUNCTION last_date RETURN DATE IS 
   BEGIN
      RETURN last_stmt_dt;
   END;
 
   PROCEDURE set_last_date (date_in IN DATE) IS
   BEGIN
      last_stmt_dt := LEAST (date_in, SYSDATE);
   END;
END P_and_L;

Sure, this is a lot more code than was necessary to simply "publish" the last statement date variable in the package specification. The benefits of this code are, however, significant and will now be explored. First of all, notice that the set_last_date procedure applies or enforces the business rule whenever anyone tries to change the value of the last_stmt_dt variable. Let's examine the impact of this enforcement. With my packaged interface, the two lines of code I showed you earlier would be changed to: 

P_and_L.set_last_date (SYSDATE + 12);
v_newdate := P_and_L.last_date;
Now instead of setting the last statement date to twelve days in the future, set_last_date intervenes and sets the date to the system date. (Of course, in the real world, you would probably not enforce a business rule by simply overriding a user action. For purposes of demonstration, however, it gets the point across.)

By moving last_stmt_dt to the inside of the package, I have exerted control over my package data. I can now guarantee the integrity of this data to any user of the package; you know what you are getting when you call the last_date function. In the first version of the P_and_L package, there was no way to know how the value was set.

This control and integrity is the most important benefit accrued from hiding your data in the body of the package. Many other wonderful advantages are possible, however, once you have taken this step. These are covered in the following sections.

Tracing Variable Reads and Writes

Have you ever lost control of your application? I once worked on an Oracle Forms application in which there was no doubt that the complexity of the code (and workarounds in the code) had caused it take on a life of its own. This application relied heavily on Oracle Forms GLOBAL variables--to the tune of 400 or so of these useful, but dangerous constructs. And, sad to say, we could not, in a number of circumstances, figure out why and how a particular global was being set to NULL or to some other value that made no sense for the action at hand.

There had been no forethought in the use of the global variables. Everyone was scrambling to meet deadlines with a very early version of Oracle Forms (4.0.6 for those of you who know to shudder at such things) and just threw direct references to the globals willy-nilly throughout the code. There was no way, consequently, to trace where and when a global value was changed. If, on the other hand, the original developers of the application had built a package around the use of Oracle Forms globals, such a trace would have been very possible, and much agony would have been averted.

I demonstrate below the tracing technique for the P_and_L package. You can then apply this technique to Oracle Forms global variables and any other variable data structure.

Let's go back to the P_and_L package shown in Example 2 and the last statement date. The variable is declared in the package body. A function is provided to return the current value of last_stmt_dt. A procedure, set_last_date, allows me to change the variable's value. I build an application making many references to these programs and then I start testing that application. I soon run into trouble. The last statement date is being set improperly, but it is very difficult for me to figure out how and why its value is being changed.

What I would really like to do is obtain a trace of every contact with that variable. If I had not hidden the last statement date variable inside a package, my situation would be hopeless. I would have no way to know when my programs were touching the last statement date.

With my last_date function and set_last_date procedure in place, on the other hand, I can with just a few lines of code get all the information I need. In the upgraded version of the P_and_L package below, I use the PLVtrc package (see code in bold) to add an execution trace to the last statement date's get-and-set: 

PACKAGE BODY P_and_L
IS
   last_stmt_dt DATE; 

   FUNCTION last_date RETURN DATE IS 
   BEGIN
      RETURN last_stmt_dt;
   END;

   PROCEDURE set_last_date (date_in IN DATE) IS
   BEGIN
      last_stmt_dt := LEAST (date_in/, SYSDATE);
   END;
END P_and_L;
The PLVtrc.show procedure intercepts attempts to read or write the last_stmt_dt variable. This trace is, however, not active, until the following command is used to turn on the trace for the current session: 

PLVtrc.turn_on;
When she turns trace on, a developer can view (or write to the PL/Vision log) a record of every effort to read or write the variable. And if the PL/SQL programs that call the P_and_L package make use of the PLVtrc startup and terminate programs, this record will automatically include the names of the programs or context when the last_stmt_dt variable was referenced (see Chapter 20, PLVcmt and PLVrb: Commit and Rollback Processing). Just a little bit of added code produced a significant enhancement in functionality!

Furthermore, all of my tracing changes occurred to the package body; the specification was left intact. As a result, none of the programs that call the P_and_L elements need to be changed or even recompiled. No one even has to know that the package has been upgraded with the new feature; it will be invisible until turned on--and then only for the current Oracle session, not for all users.

Once I built the get-and-set around my date variable, adding an execution trace facility was very simple. Just get that layer of code in place and many seemingly and formerly impossible tasks become easy!

Simplifying Package Interfaces

Another reason for moving data into the package body is to simplify the interfaces to the package elements. When data are declared in the package body, they are global within the package. All programs defined in the package (specification and body) can reference these variables directly. You can use this fact to your advantage by not passing in these values in the parameter lists of the package elements.

Consider the PLVobj package, which provides a programmatic interface to the ALL_OBJECTS data dictionary view. PLVobj works with a current object, which is made up of three elements:

The PLVobj package and other packages such as PLVio, perform many different operations on this current object, including the following: bind the object for dynamic SQL execution, open a cursor into the ALL_OBJECTS view for this object, read the source code for that object, and so on.

Suppose that I did not store this current object in the package. Then every time I wanted to perform one of the above actions, I would have to provide the values for each of these elements of the current object in the parameter list. Let's look at some examples.

Instead of calling PLVobj.open_objects without any arguments like this: 

PROCEDURE open_objects;
I would need to modify the header as follows: 

PROCEDURE open_objects
   (name_in IN VARCHAR2, type_in IN VARCHAR2, schema_in IN VARCHAR2);
And deep within the PLVio package, I could no longer simply call the bindobj program relying on the context or current object previously set, as I do here: 

PLVobj.bindobj (cur);
Instead, I would have to maintain variables inside PLVio with the current object values and then pass them into bindobj as follows: 

PLVobj.bindobj (cur, currobj_name, currobj_type, currobj_schema);
Would you use a package designed that way? I don't think I would. All those arguments, passed in over and over again. Each time thinking: why can't the package just keep track of that for me?

Well, it can and PLVobj does just that. The current object of PLVobj is defined by three private package variables:

v_currschema

The owner of the object

v_currname

The name of the object

v_currtype

The type of the object(s)

Since the above elements are private variables, a user of PLVobj will never see or reference these variables directly. Instead, I provide a program to set the current object. Its header is: 

PROCEDURE setcurr (name_in IN VARCHAR2);
where the argument is the module name, which can actually be a composite of the schema, name, and type.

With the setcurr procedure assigning values to my current object, the parameter lists of my object-management programs in PLVobj become short and sweet. They are much easier to use.

There is, of course, a tradeoff when you rely on package global data instead of passing parameters. Sure, the data is private and access to it is controlled. But it also means that the package program is completely dependent on that data. You cannot use the program to analyze or manipulate data until it is set into the package globals. The only way you can use the PLVobj package is to first call the setcurr procedure.

I believe that in many cases, this tradeoff is a good investment. It reinforces my perspective on the package as an environment more than simply a collection of related code elements.

When to Make Data Public

You shouldn't always hide your data in the package body. Sometimes you really do want to let someone directly access the information. I have found, for example, that if you are going to execute dynamically constructed PL/SQL code with the DBMS_SQL package and you want to reference any kind of external data directly, it must be defined in the specification of some package. Dynamically executed PL/SQL blocks are never nested within another block. As a result, they can only reference variables declared in the dynamic block or in a package specification (see Chapter 18, PLVcase and PLVcat: Converting and Analyzing PL/SQL Code, for more details).

Another place in PL/Vision where I violate this practice and declare data structures in the specification is the PLVio package. You can choose to use a PL/SQL table as a target with the following call: 

PLVio.settrg (PLV.pstab);
Then all subsequent calls to PLVio.put_line will deposit information in another row of data in the PLVio-based PL/SQL table, defined in the specification as follows: 

   target_table PLVtab.vc2000_table;
   target_row BINARY_INTEGER;
Why did I put this table in the specification? I suppose I could have hidden it away in the body and then built some programs that would maintain the contents of the table, along these lines: 

   PROCEDURE init_table;
   PROCEDURE set_row (val_in IN VARCHAR2);
   FUNCTION rowval (row_in IN INTEGER) RETURN VARCHAR2;
   PROCEDURE display;
Maybe I just got lazy that night. But maybe, just maybe, it actually makes more sense in this case to allow the developer to do whatever she wants with the table and its contents. It is just a repository, after all, for the output from calls to the PLVio.put_line procedure. You might, in fact, want to write some information from PLVio and then add a few rows of data from your own, independent source. Rather than put up the barrier of get-and-set routines, I just leave the table in the specification and make the user responsible for its contents.

Anchoring to Public Variables

There is one other case in which specification-based variables are useful: anchored declarations. You can anchor or base the declaration of a variable on another, predefined structure. To do this, you use the %TYPE and %ROWTYPE attributes. The most common way %TYPE is used is to anchor a local PL/SQL variable to a database column, as shown below: 

   v_ename emp.ename%TYPE;
You can also, however, anchor variables to other PL/SQL data structures. You can define variables in one package (a repository of subtypes) that are used to define variables in another package. In this case, the variables must be declared in the specification. An example from PL/Vision will demonstrate this technique.

A number of PL/Vision packages manipulate PL/SQL source code (PLVgen, PLVcase, PLVcat, etc.). One important element of PL/SQL code is the identifier. An identifier is a named element of the language. Today, identifiers can be up to 30 characters in length and must start with a letter.

As I built packages to read and parse identifiers (see PLVprsps), I would declare local variables to hold those values. At first, I declared the variable as follows: 

v_ident VARCHAR2(30);
This always made me uncomfortable, though. I could just see Oracle Corporation in its next release announce that it would now allow identifiers to be up to, say, 60 characters in length. My code would instantly become very vulnerable. So I would often compensate by declaring the variable as: 

v_ident VARCHAR2(100);
I felt safe, but dissatisfied. The justification for that declaration was weak; it would be hard (embarrassing?) to explain to another developer why I chose this number. After too many months, I found the ideal solution: use an anchored declaration.

So I added the following declaration to the PLV package specification: 

plsql_identifier VARCHAR2(100) := 'IRRELEVANT';
I then changed my hard-coded declaration of v_ident and many other variables to this format: 

v_ident PLV.plsql_identifier%TYPE;
Now if I ever do need to change the length or other characteristic of variables that represented PL/SQL identifiers, I could make that change in just one place. Notice that I assigned the default value of IRRELEVANT to the variable. I did that to emphasize that the value contained in plsql_identifier is irrelevant. It is never referenced (or intended to be referenced) for its value, only for its datatype.

Note

You might be thinking that I should just have declared plsql_identifier as a constant and then the value of this "reference only" structure could not be mucked with. That certainly makes sense. I found, however, that you cannot reference a constant in an anchored declaration. If I wanted to use plsql_identifier to anchor other variable declarations, it had to be declared a variable.

So there are certainly circumstances in which you will want to declare data structures in the package specification. This should occur, however, on an exception basis--and you should be able to justify your action with some application-specific requirements. Otherwise, hide that package data in the body and you will reap many benefits.

Simultaneous Construction of
Multiple Packages

The underlying precept of PL/Vision is to identify distinct areas of functionality needed for PL/SQL development (such as error logging, exception handling, dynamic SQL, etc.) and then map those areas of functionality into separate packages. In this approach, every feature, every requirement, has its place in one or another package.

The first thing I noticed as I embarked on my development effort was that I never got it right the first time. My basic idea for the package would usually be correct, but I would either not anticipate a particular need properly or I would frame the need too narrowly. As a result, I would find myself returning again and again to code that I had earlier considered finished. And the motivation for that upgrade would be to make the program more usable and reusable.

I soon discovered that I would rarely work with just one package at a time. Instead, I would enhance multiple packages simultaneously. This approach minimized redundancy and enhanced the base of reusable code. I step through an example scenario below to make this process more concrete.

Suppose that I am building an order entry system. I have previously constructed some generic packages to do string parsing and to perform analysis for on-time shipping. I decide in advance that I will have a separate package for all of my application's constants, and one package for each of my tables (orders, line items, etc.). I begin to construct my application. Every time I run into the need for a new constant (for example, if status of order is C or closed), I open up the constants package and add another named constant. I then reference this identifier in my program and avoid hard-coding.

I find that I am using PL/SQL tables to manipulate date information. Instead of repeating the code to manage these tables in all of my different programs, I create a separate package. I need to perform some complex parsing on the address for an order. My string-parsing package does not handle this requirement. I face a fork in the road: do the parsing I need specifically for the address and bury it inside the order entry program or make this program as generic as possible and put it in the string-parsing package. By choosing the latter option, I not only implement my order entry application, but also build up the robustness of the string-parsing package.

Rather than work in any one package, I might find myself adding functionality and content to as many as three or four packages in the processing of implementing one program for my application. It might take a little bit longer to finish that one program, but when I am done I will have strengthened my overall PL/SQL development environment. As new challenges appear, I continue to add to this base environment or library and also pull from this library prebuilt programs that allow me to get the job done faster (see Figure 5).
Figure 2-5

If you develop your applications using this approach, you will one day reach critical mass. You will find yourself creating very sophisticated and rich new programs by doing little more than linking together a series of high-level calls to prebuilt package programs. The PLVcat package that catalogues PL/SQL code is a good example of a critical mass package. It performs some very complex processing, but it is a very small package. Its procedures consist of little more than a series of calls to other PL/Vision utilities.

[1] Thanks to John Beresniewicz for this insight.
[2] As of PL/SQL Release 2.2, you can "wrap" your PL/SQL source code into an encrypted format. This format can be compiled into the database, but is not readable. Wrapped code is primarily of value to third-party vendors who provide PL/SQL-based applications, but are not interested in letting the competition see how they built their applications.

Back to: Advanced Oracle PL/SQL Programming with Packages

oreilly.com Home | O'Reilly Bookstores | How to Order | O'Reilly Contacts
 International | About O'Reilly | Affiliated Companies | Privacy Policy

© 2001, O'Reilly & Associates, Inc.