By Steven Feuerstein
OTN Member since 2001

Time to spec your code packages, piece by piece

Installment 4 of Feuerstein's "Building a Code-Analysis Utility" series

One approach I could take is bottom-up construction: Start with the lowest-level, or atomic, packages. Build them and test them, feeling confident that I can deploy them in more-complex packages without introducing bugs. Then move up to the next level in the hierarchy, tackling the more challenging tasks but building on my previous work.

That sounds awfully sensible. There is, however, one big problem. I plan to follow the Extreme Programming mantra—"design only what is needed now"—that is, keep things as simple as possible. If I start from the lowest-level elements and build up, how will I know what is "needed now" and what will be needed later or not at all?

For example, I could start with cc_types, a package with a straightforward mandate: to encapsulate knowledge about the various datatypes known to and supported by PL/SQL. Fine. What functionality should I offer in my package specification? Now I go into brainstorming mode and come up with a whole lot of ideas:

• Named constants for each of the numeric codes for the datatypes (most, but not all, of this taken from DBMS_TYPES, a new package in Oracle9i Database)
• Named constants or some sort of container for the names of the various datatypes
• Functions for returning TRUE or FALSE: Is this type a numeric type (for instance, in the NUMBER "family")? A string type? A composite type (such as a record, array, or object)?
• A function for returning the name of a datatype from its numeric code
• A function for returning the numeric code of a datatype from its name
• A function for returning TRUE if the type is a record type
• A function for returning TRUE if the type is a record type declared with %ROWTYPE (I have to handle some funky stuff in ALL_ARGUMENTS in this case)
• A function for evaluating whether two datatypes are in the same family or are of the same type
• A procedure for displaying all the currently supported datatypes in PL/SQL
• A function for returning TRUE if the datatype is available in the database or just in PL/SQL (such as Boolean)
• A function for returning TRUE if Oracle discourages using the datatype (such as CHAR or LONG)
• A function for returning the preferred datatype for a "discouraged datatype."

Whew. That's probably enough to give you the idea. Even with a package as basic as cc_types, you can come up with a lot of interesting ideas. That's not the issue, though. Am I building this package because I love to write code, the more the better? Well, yes, but that's an issue for my therapist. Therapy aside, the answer is, NO. I'm building cc_types so that I can construct OverloadCheck in the simplest, most maintainable and readable manner possible. From that perspective, it doesn't matter if I can come up with 10 or 20 or 50 interesting ideas. What matters is: Which of these pieces of functionality does OverloadCheck need? At this stage, I just don't know.

Should I simply construct all those programs? No way. This realization necessarily leads me to abandon a bottom-up process. Instead, I'll opt for a top-down approach. I'll start at the highest level of OverloadCheck (namely, the executable section of the overloadcheck.overloadings procedure) and work my way down. Instead of writing a program in one big blob of hundreds (or thousands) of lines of executable code, I'll break it up into smaller, modular pieces. Furthermore, I'll elaborate the logic needed in each level of detail before moving on. In the process, as you'll see, I will spec precisely what I need in cc_types, cc_names, cc_arguments, and all the other packages.

When I've established the functionality I need in a given package, I can then implement those and only those programs referenced within that package. I won't need to perform any extra work. If, later in the development cycle, I encounter a new need, I'll simply work out the specifications of a program to meet that need and then add that program to the appropriate package. And I don't need to be linear about this. I could write a layer of logic in the main overloadcheck.overloadings procedure and then bop over to cc_types to implement a new function (I may, for instance, have a brainstorm about how to implement that function and want to capture the inspiration before I lose it).

High-Level Flow of overloadcheck.overloadings

The first program I would like to implement in OverloadCheck is one that will analyze package overloadings for possible ambiguities. I want, in other words, to check the overloadings of a package. Because my package name already contains the word check, I'll call this procedure overloadings, as in overloadcheck.overloadings. (That sounds better to me than overloadcheck.check_overloadings.)

Before I start writing the code, how do I want to call the procedure? What parameters do I need to supply? Nothing more than the name of the package that contains overloadings I wish to analyze. Given that, here's a sample invocation of my procedure: overloadcheck.overloadings (package_in => l_name);

That's a start. I have the header (name and parameter list) for the program, so now I can begin to construct it. Once again it's time to go into brainstorming mode, but from a different perspective. Rather than come up with a list of all the possible programs I may be able to use, I'll think about the big pieces of logic I need to get the job done—and nothing more than that.

First, I need to validate the arguments and initialize data structures for use in my program. You almost always need to do some kind of initialization, right? Once I'm done initializing, it's time to analyze the overloadings for each distinct program name in the package. When I'm done with the analysis, I display the results on the screen. Is that all? At this stage of the game, Yes.

In pseudocode, I have something like this:

For each program name in the package
loop

   If that program is overloaded 
   then
      For each overloading of that program
      loop
         check against all other overloadings
      end loop for each overloading
   end if program is overloaded
end loop for each program
show the results of the analysis

That seems straightforward enough. Listing 1 shows the PL/SQL code. (For the remainder of this article, I'll skip the pseudocode and express my high-level logic directly in PL/SQL.)

As you can see, I have started to define elements of both the cc_smartargs and cc_report packages. Table 1 explains my thinking process, line by line.

I'm now done with the first level of specification of the overloadings procedure. I know a lot more about what I'll need from cc_smartargs than I did before, but I've kept my nose out of any of the implementation details of that package. For my next step, I have some choices. I can implement the next level of detail in overloadings (initialize and compare_with_others), or I can switch over to cc_smartargs and start looking at what it will take to provide all that information to overloadings. I think I'll stick with overloadings for a little while longer. I may discover more about requirements for cc_smartargs (and who knows what else?).

The Initialization Logic

What initialization steps are required for overloadings? Again, remaining at the highest level possible, I come up with the following steps:

• Make sure that the object named by package_in is actually a package. There's no point in analyzing overloadings for a standalone program or a view.
• Load all the argument information for this package.
• Initialize the reporting component. (I need to pass on the news as to whether I'm running this in test mode.)

Which leads me to this initialization procedure:

 1  PROCEDURE initialize
 2  IS
 3  BEGIN

 4     cc_smartargs.load_arguments (package_in);
 5
 6     cc_error.assert (
 7        cc_smartargs.ispackage
 8       ,cc_error.c_not_a_package
 9       ,package_in);
10
11     cc_report.initialize (package_in);
12* END initialize;

In line 4, I ask cc_smartargs to do the heavy lifting: load up all the argument information for this package into—well, into whatever I later decide will work best. I don't need to worry about that right now.

In lines 6 through 9, I identify my first error-related program: the assertion routine. I call this program to assert that a particular condition is true. If it's false, I'll stop the program from continuing. Assertion routines are a normal part of programming in other languages, such as Java and C, and should be more thoroughly adopted in the PL/SQL world. In this case, I want to make sure that package_in actually refers to a package, so I call a function in cc_smartargs to return that information; specify the error I'm reporting, if I don't have a package; and also provide the name of the nonpackage, which will appear in a message to the user.

I don't yet know what cc_error will do with all that information, but by taking the approach of passing key data points to a separate assertion program, I believe that I'm giving cc_error everything it needs to do its job without dictating how it should do that job. This will give me maximum flexibility later, when I get around to implementing the package.

You may be wondering why I would rely on cc_smartargs to tell me whether or not I have specified a package. How does that relate to "smart information" about arguments? To answer that question, we need to ask another: How can I most easily ascertain whether a program is a package? DBMS_UTILITY.NAME_RESOLVE can tell me this. And that program is going to be called by cc_names, which it seems to me is going to be called from cc_smartargs or cc_arguments in the process of loading up all the argument information. For now, I'm going to assume that I'll need to get the information from cc_smartargs.

Notice that I don't pass any parameters to cc_smartargs.is_package. What this means is that I'm assuming that cc_smartargs is going to store or cache information about the specified package in some sort of persistent data structure. Once I've called cc_smartargs.load_arguments, then all the information that cc_smartargs returns will relate to that package and its programs and arguments.

Finally (going back to the code of the initialization program), line 11 calls the report package's initialize program to indicate whether or not it should run in test mode. Once again, I don't really know what this test mode may mean inside cc_report and I may need to change it later, but for now, including this option seems like the right thing to do.

Comparing One Overloading to Others

The other program I need in order to finish overloadcheck.overloadings is compare_with_others. This should be a bit more interesting and challenging than initialize. As a reminder, here is the header of the program:

PROCEDURE compare_with_others (
   program_in IN all_arguments.object_name%TYPE
  ,check_this_ovld_in IN PLS_INTEGER)

What precisely does this program need to do? I need to see if the current overloading (Nth in the list for this program name) can be called in a way that will be indistinguishable from an invocation of a different overloading. So, I need to compare it to each of the N—1 other overloadings. This leads me right to the deeper, more interesting question, What logic do I need to perform this comparison? What conditions do I need to check for? Two fundamental factors come to mind:

First, if the two overloadings are of different types (one is a function and the other a procedure), then there's no possibility of ambiguity. The way they're called in the code is sufficiently different (a function is invoked as part of a statement and a procedure call is a standalone statement) to ensure that the compiler will not be confused.

Second, I may need to check various invocations of parameters in each overloading. Suppose, for example, that the parameter list of overloading N contains three arguments, the last of which has a default value. This program can then be invoked with either two or three values in the parameter list. And if overloading M's parameter list has one or more trailing arguments with defaults, well, then there are two or more invocations of valid invocations of that overloading.

Note: When I consider permutations of valid ways of calling a program, I assume that I'm working only with positional notation. Issues of ambiguity in overloading pretty much disappear when you use named notation (parameter_name => parameter_value).

So when I say "compare overloading N to all other overloadings," what I really mean (and need to do) is "compare all valid permutations of overloading N invocations to all the valid invocations of all other overloadings." My implementation of compare_with_others is in Listing 2.

The boundaries of the FOR loop ensure that I do not test any overloading against itself, nor do I repeat comparisons that I have done in previous iterations of the loop from which compare_with_others is called.

You may have already noticed that I have specified two more programs in OverloadCheck—same_program_types and compare_all_invocations—to handle the nontrivial aspects of compare_with_others. Is this beginning to seem like a Russian doll, in which you open up one doll only to find another inside it? I assure you that I'm not procrastinating or putting off the inevitable hard work. Instead, I'm working hard to keep things simple and transparent. This not only ensures that I avoid unnecessary work (and reworking of initially faulty code) but also minimizes the introduction of bugs, because I validate and correct my logic at every step.

Hoping that you are reassured, Listing 3 shows my implementation of same_program_types. Table 2 describes the implementation.

Now let's tackle compare_all_invocations and the other aspects of OverloadCheck logic required to identify ambiguous overloadings.

Algorithms for Identifying Ambiguity

I have found that I need to compare all valid invocations of each distinct pair of overloadings. compare_all_invocations does that (see Listing 4). Table 3 explains the logic behind it.