Crafting Service Providers: Creating a Versatile Reporting Package
Installment 7 of Feuerstein's "Building a Code-Analysis Utility" series

by Steven Feuerstein

The reporting package for the OverloadCheck utility demonstrates useful techniques for creating efficient, easy-to-maintain code.

In the previous six articles in this series, I've covered all the aspects of OverloadCheck related to extracting and analyzing argument information, but I haven't yet touched on this small-but-crucial related aspect: reporting the results. In this article, I'll step through the process of creating the three key programs needed in OverloadCheck's reporting package, as well as some important utilities and functions called by these programs. In the process, I'll showcase techniques you're likely to find helpful in many situations, such as use of the dynamic WHERE clause to streamline code and encapsulation of necessary-but-unwieldy procedures (in this case, DBMS_UTILITY.NAME_RESOLVE and DBMS_OUTPUT.PUT_LINE) to make them much friendlier and more useful.

Let's start by reviewing what we know about the programs OverloadCheck will need in its reporting package.

Defining the Needed Reporting Programs

Creating an In-line Conditional Function In OverloadCheck's call to report an ambiguous overloading (cc_report.ambig_ovld), you may have noticed the calls to cc_util.ifelse. Why did I call this function? As it turns out, when an overloading has no arguments, I need to specify 0 as the position of the starting argument in my report. Otherwise, the position is 1. Now, I could have specified this position without a DECODE-like function, but then my code would have been something like the rather-long set of lines shown in Listing 4. I think all that typing is a terrible price to pay for the lack of an in-line conditional function in PL/SQL. So, I decided to give myself cc_util.ifelse as another little productivity gift. Again, you can easily come up with variations on this theme, as well as overloadings for different datatypes, to extend this function's usefulness. One cautionary note: saving yourself a few minutes of typing does not in and of itself justify using a function that increases the code density (the number of actions carried out per line of code). If the resulting code is difficult to understand or noticeably slower to execute, you should stick with the more verbose approach. In this case, what I end up with is more readable as well as more efficient.

In my top-down development of the OverloadCheck package in installments 3 and 6 of this series, I identified a need for some programs in the cc_report package to perform the following three functions:

• Initialize the reporting mechanism for the current analysis.
• Report an ambiguous overloading to the results table (this program is called in overloadcheck.check_for_similarity)
• Show the results of the OverloadCheck.overloadings analysis for the program specified by the user.

Now, I need to figure out how to implement these programs. How will I store the results that are reported with a call to cc_report.ambig_ovld? And, once I decide that, how shall I go about displaying the information?

Actually, I have already figured out a key aspect of cc_report that relates directly to the question of how to report an ambiguous overloading to the relevant results table, so let's address this area first.

Filling the Results Table

You might recall that, before I started doing any coding, I thought about how I would test OverloadCheck. I decided I wanted to use utPLSQL to run my tests automatically and comprehensively. I resolved that the best way to achieve automated testing was to write the results of my analysis to a database table and then compare the contents of that table to a control table using utAssert.eqtable. A row in the results table must contain all the information needed to identify an overloading — the two distinct programs having the same name as each other, and the list of parameters used for each — so it must have this structure:

 
CREATE TABLE cc_ambig_ovld_results (
   object_name  VARCHAR2 (100),
   package_name VARCHAR2 (100),
   owner        VARCHAR2 (100),
   overload1    VARCHAR2 (100),
   startarg1    INTEGER,
   endarg1      INTEGER,
   overload2    VARCHAR2 (100),	  
   startarg2    INTEGER,
   endarg2      INTEGER);

Once I have this table defined, most of the rest of the package falls into place. For example, when I need to report an ambiguous overloading, I simply perform an INSERT to supply the relevant values into this table, as shown in the cc_report.ambig.ovld procedure in Listing 1.

By defining this program as an autonomous transaction, I ensure that each report is immediately saved to the database. Consequently, I don't have to decide when and where to commit in the outermost layer, which is the overloadcheck package. Another benefit of this approach is that test results are immediately visible in other schemas.

Purging the Results Table

Now that we know the structure of the results table, let's look at some interesting aspects of the initialize procedure, cc_report.initialize. What does this procedure need to do? Clean out the results table so that it contains only the latest information for the specified package. At first glance, it seems as though we could get by with some very straightforward code, such as the following:

PROCEDURE initialize (package_in IN VARCHAR2)
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      DELETE cc_ambig_ovld_results
       WHERE owner = USER
         AND package_name = package_in;
      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         ROLLBACK;
   END;

In other words, get rid of all the rows in the results table for the specified package, owned by the current user. Set it up as an autonomous transaction, so the purge process is committed immediately, without affecting whatever else you have going in your session (note that this means I must commit or roll back the data on each exit point from the procedure).

That was easy — and woefully inadequate in all sorts of ways, as the following questions show:

• What if the package is owned by another schema? Then, this DELETE will not find any rows.
• What if the package is specified as OWNER.PACKAGE? Once again, this DELETE will not find any rows.
• What if the package name is provided in upper case letters? This is another case where this DELETE will not find any rows.
• What if I'm analyzing the possible ambiguity, not of an entire package, but of a specific program within a package? In this case, I would specify PACKAGE.PROGRAM — and, again, this DELETE will not find any rows

Oh, and there's one other potential pitfall that is not at all obvious at this point: when I report on the results of my analysis, I will need to query the appropriate rows from the results table. I will, in other words, have to repeat the same WHERE clause used in the query. If it's a complicated WHERE clause (which the previous questions would lead me to believe it will be), I would rather not have to repeat it in my code. With repetition, I increase the chance of bugs and make the logic more difficult to maintain and upgrade.

As usual, life is more complicated than it seems at first glance. How can I implement the initialize program to resolve both the name-related issues and the WHERE-clause dilemma?

Resolving Names with Ease

Clearly, there's no point in my trying to deal with all the complexities of resolving the name of the package (or program within the package) when Oracle's DBMS_UTILITY.NAME_RESOLVE procedure can handle all of that for me. However, anyone familiar with this procedure knows that its interface is rather awkward and obscure. To be kind both to myself and to those who will maintain my code in the future, I decided to encapsulate DBMS_UTILITY.NAME_RESOLVE in a nice, user-friendly wrapper — a package called cc_names (see the sidebar, "Making Utilities Friendlier"). As a result, I can simply call the cc_names package to make sure the user-specified package is correctly identified before I delete the rows in my results table.

Making WHERE Clauses Dynamic

While cc_names will simplify my name-resolution issues, I'm still going to have a pretty complex WHERE clause that I'd rather not specify more than once. Fortunately, there's a nice, simple way for me to avoid writing that complicated WHERE clause in two places in my code: switching from static to dynamic SQL. With dynamic SQL, I can specify the WHERE clause as a string and simply concatenate it to each SQL statement as needed.

First, I'll define the WHERE clause. In order to handle the two possible scenarios (requesting analysis for an entire package or for a single program name within a package), I declare the following string in the cc_report package:

c_where_clause  CONSTANT VARCHAR2 (1000)
   := 'WHERE owner = :owner
         AND (  (    package_name = :package
                 AND (   object_name = :object
                      OR :object IS NULL
                     )
                 )
             OR (    package_name IS NULL
                 AND :package IS NULL
                 AND object_name = :object
                )
            )';

I can then reconstruct my initialize procedure as shown in Listing 2. As you look over this procedure, you can see how the strategies we've just discussed help simplify it. On line 4, I rely on cc_names.for_program to resolve the reference to the package name, returning a record with the individual elements of the name. On lines 6-14, I construct and execute the DELETE statement, concatenating my previously-defined WHERE clause into the statement to keep it nice and concise.

You may also be noticing something odd in my long list of bind variables: it includes several repetitions. Why do I have to include the same value more than once, especially if the names of the placeholder in the c_where_clause value are repeated?

Well, because that's the way NDS works. When executing a dynamic SQL string (DDL, DML, or a query), you must provide a value in the USING clause for each placeholder, even if the name of the placeholder happens to be repeated. The associations are made exclusively on a positional basis. If, on the other hand, you execute a dynamic PL/SQL block (a string that starts with 'DECLARE...' or 'BEGIN...'), then NDS associates placeholders with bind variables by the name of the placeholder, and you do not include the same value more than once.

So, my preference is to opt for a dynamic PL/SQL approach with the same bind variables. By doing so, I can simplify the USING clause of the DELETE statement in Listing 2 to contain only a distinct set of values, as follows:

BEGIN
   EXECUTE IMMEDIATE 
      'BEGIN run_my_proc (
               :owner,:package,:object,
               :object,:package,:object);'
      USING l_names.owner
           ,l_names.package_name
           ,l_names.object_name;
END;

Showing the Results

Now that we've looked at the details of the table cc_ambig_ovld_results — seeing how rows are filled with reports of a package's ambiguous overloadings and purged prior to collection of new results — let's step back and look at how results from this table are retrieved and displayed.

As you may recall from the previous articles in this series, OverloadCheck.overloadings is the high-level program that analyzes package overloadings for possible ambiguities. After looping through all the programs in the package to check their overloadings, the OverloadCheck.overloadings program calls the cc_reports.show_ambig_ovld_results procedure to populate the results table (cc_ambig_ovld_results ) and show the results.

Following my preferred (and highly recommended) approach of keeping executable sections short and very readable, here is the main block of show_ambig_ovld_results:

   BEGIN -- main show_ambig_ovld_results
      initialize_display;
      retrieve_results;
      display_results;
   END show_ambig_ovld_results;

Let's zoom in on the retrieve_results procedure, shown in Listing 3. This procedure provides an excellent example of how enhancements to the PL/SQL language allow us to reduce the amount of code we write, while increasing readability and performance. Prior to Oracle8i, I would have to write a cursor FOR loop, populating my collection one row at a time. And prior to Oracle9i, I could not use BULK COLLECT with a dynamic SQL string. In the brave new world of Oracle9i Release 2, I simply define my collection in the declaration section of the show_ambig_ovld_results procedure as follows:

TYPE results_tt IS TABLE OF cc_ambig_ovld_results%ROWTYPE
   INDEX BY BINARY_INTEGER;
l_ambig_ovld_results   results_tt;

Taking a closer look at the retrieve_results procedure (see Listing 3), we can see how this collection and the use of dynamic SQL make the procedure both efficient and readable. In lines 5-7, I construct my SQL query using the dynamic WHERE clause. In line 8, I fill up the collection with all the appropriate results rows. In lines 9-14, I carefully supply a value for each placeholder in the SQL string, as I explained earlier.

Once I've populated the collection, I need little more than a numeric FOR loop, along with formatted DBMS_OUTPUT.PUT_LINE statements, to scan through each of the results and send them to the screen. To help me handle those DBMS_OUTPUT.PUT_LINE statements, I opt to repeat the strategy that served me so well earlier in dealing with name resolution: creating a user-friendly wrapper around the Oracle procedure I need to use. To find out more about cc_util.pl, my encapsulation of DBMS_OUTPUT.PUT_LINE, see the sidebar, "Making Utilities Friendlier."

Finishing Up OverloadCheck

In looking at some key points about OverloadCheck's reporting package, cc_report — and the other tightly-focused, low-level packages it calls, cc_names and cc_util — we've explored some useful strategies for implementing such packages in a streamlined, easy-to-maintain manner. We've seen how you can use the dynamic WHERE clause to simplify code and make it more bug-proof. We've also looked at some ways you can improve both your productivity and your code by creating more-useful versions of existing functions (such as DECODE) and procedures (such as DBMS_UTILITY.NAME_RESOLVE and DBMS_OUTPUT.PUT_LINE). We haven't had room to look at every implementation detail of the packages we've discussed, but you can easily check these out on your own by downloading the OverloadCheck package and perusing it at your leisure.

In the next and final installment of this series, we'll go through the process of refactoring OverloadCheck to strengthen its internal structure and make it maximally easy to maintain. Look for it soon on OTN!

Steven Feuerstein ( steven@stevenfeuerstein.com) is one of the world's leading experts on the Oracle PL/SQL language. He is the author or co-author of nine books on PL/SQL, including Oracle PL/SQL Programming, 3rd Edition and Oracle PL/SQL Best Practices (O'Reilly; http://oracle.oreilly.com/). Steven is a senior technology advisor for Quest Software, has been developing software since 1980, and worked for Oracle from 1987 to 1992. He is also Coordinator of the Refuser Solidarity Network, an organization that builds support for the Israeli refuser movement .

Roadmap for Building a Code-Analysis Utility Steven Feuerstein, Oracle PS/SQL language expert, shows each step in the process of creating OverloadCheck—a utility that checks the quality of PL/SQL code. In a series of eight articles, he takes you from the beginning of the process (defining the problem he wants his utility to solve), to researching, designing, and refining this new creation until it truly works. As you watch the quality-assurance utility come to life, you'll also get exposure to some of PL/SQL's newest features and see how using best practices can benefit any coding project. Article 1: Building a Code-Analysis Utility and Doing It Right the First Time The first step shows what's involved in defining the goal of this utility, how to perform required analysis, and how to translate the results of that analysis into a useful form for developers. Article 2. Getting Started, Starting with Testing Although it would be tempting to start writing code at this stage in the process, the author shows the importance of waiting while he decides how to test his code. Article 3. Creating a High-Level Design In this article, see how to conceive a basic but workable architecture, while avoiding over designing. Article 4. Implementing OverloadCheck: The Construction Phase While showing how to get started writing code, Feuerstein demonstrates how to ensure code readability and how to minimize the number of bugs. Article 5. Adding Smarts to the Argument Information This article tackles some of the most complex logic involved in analyzing potential overloading ambiguities. It also shows how important it is to hide the complexity of such structures as multilevel and string-indexed collections behind procedures and functions. Article 6. Crafting 'Service Providers': Packages with Focused Functionality Learn by example how to identify the distinct services that relatively small units offer to higher-level packages, and how to consolidate everything related to those services. Article 7. Crafting Service Providers: Creating a Versatile Reporting Package Find out how to streamline code with the dynamic WHERE clause and how to put a user-friendly wrapper around unwieldy-but-useful procedures (such as DBMS_UTILITY.NAME_RESOLVE and DBMS_OUTPUT.PUT_LINE) as you explore the creation of OverloadCheck's reporting package. Article 8. Getting it Right the Second Time In the final stage, Feuerstein uses a test engine and his predefined test cases to do some testing. He also shows how to use the process of refactoring to fix problems and improve the inner workings of the program.

Table 1. Programs Needed in OverloadCheck's Reporting Package

Program Function	Program Header
Initialize the reporting mechanism for the current analysis	cc_report.initialize (package_in);
Report an ambiguous overloading to the results table	cc_report.ambig_ovld (cc_smartargs.owner_name                     ,cc_smartargs.package_name                      ,program_in                      ,overload1_in                      ,cc_util.ifelse (l_numargs1 = 0, 0, 1)                      ,lastarg1_in                      ,overload2_in                      ,cc_util.ifelse (l_numargs2 = 0, 0, 1)                      ,lastarg2_in                      );
Show the OverloadCheck.overloadings analysis results	cc_report.show_ambig_ovld_results (Package_in);

Making Utilities Friendlier Earlier in this series, you may have noticed that I identified a couple of utilities OverloadCheck would use to encapsulate access to the Oracle procedures DBMS_UTILITY.NAME_RESOLVE and DBMS_OUTPUT.PUT_LINE. Why, you may wonder, do we need to encapsulate these procedures at all? Why not just use them directly? Because they're a pain to use, that's why. Let's take them one at a time... Encapsulating DBMS_UTILITY.NAME_RESOLVE The cc_names package (which I referenced in installments 3 and 6 in this series) does little more than encapsulate access to the DBMS_UTILITY.NAME_RESOLVE procedure. Nonetheless, it's a package well worth creating, in my view. Basically, I'd rather not have to figure out how to use DBMS_UTILITY.NAME_RESOLVE every time I need it. DBMS_UTILITY.NAME_RESOLVE offers quite an awkward interface. You pass it the name of a code object and it returns information about that object in six different OUT parameters, some of which you may not even need to examine. And then you need to interpret that information. For example, what is the value of the "part 1 type" that indicates a package? Rather than have to remember any of that, I learn it once and then stuff my newfound wisdom into its own package, cc_names. Then, I need to expose that wisdom in a neat package, so I define a record TYPE to hold all the name-related information: TYPE names_rt IS RECORD ( object_name VARCHAR2 (200), package_name VARCHAR2 (200), owner VARCHAR2 (200), ispackage BOOLEAN ); My encapsulation, a function called cc_names (shown in Listing 5), is simple and direct. Now when I need to call NAME_RESOLVE, I let cc_names do all the work. All I need to do is write something like this: DECLARE name_info cc_names.names_rt; BEGIN name_info := cc_names.for_program ('MYPKG.MYPROC'); ... END; Encapsulating DBMS_OUTPUT.PUT_LINE Anyone who has kept up with my rantings — er, I mean writings — over the years knows that I have a love-hate relationship with DBMS_OUTPUT. I have to use it, I love what it does for me, but why does it have to be such a bear to use? Among my complaints about DBMS_OUTPUT are: Inadequate overloadings. You can pass it a string, number or date—and that's it. What about Booleans, collections, and XMLTypes? Why couldn't it be really useful and offer a built-in display mechanism for object types and file IDs, much as Java does? Ridiculous limitations. You can't display a string of more than 255 characters. You can't output more than 1,000,000 characters before hitting an overflow. The default buffer size in SQL*Plus is only 2000 characters. From who or what is Oracle protecting me? Inefficient naming. I have to type 20 characters just to say "show me." Just think: if Oracle had only shrunk the name of this utility down to, say, 10 characters, the PL/SQL developer community would have saved approximately 2.5 million hours of development time in the last dozen years. Delayed output. You can't see any output until the program has finished executing. This delay is positively excruciating when you're dealing with a long-running application (especially when you're also confronting the limited buffer size of DBMS_OUTPUT). See what I mean about ranting? What's a developer to do? I simply refuse to use DBMS_OUTPUT.PUT_LINE — but I need its functionality. I resolve this seeming contradiction by building a program on top of DBMS_OUTPUT.PUT_LINE that corrects as many of these nuisances as possible. And that's what cc_util.pl (shown in Listing 6) does for me. I can pass it arbitrarily long strings; it will automatically detect carriage returns and break up the output preserving those line breaks; and it will display Boolean values. I strongly encourage you to follow this same approach in your own applications. You'll probably come up with application-specific variants, all of which will improve your productivity and reduce your frustration level.