16.4.2.1 Benefits of internal modularization

Once I have created all of these specialized variations on put_line , the body of the proc procedure is very short and simple. Why do I go to all this trouble? Why not simply issue calls to put_line with the appropriate combinations of strings? There are two very good reasons:

1. Each of these are called in other programs in the PLVgen package. If I did not encapsulate the logic inside a variation of put_line , I would be repeating my logic. This would make debugging and enhancement of the package difficult, if not impossible.

2. The calls to the put procedures now reflect the structure of a PL/SQL procedure.

Let's look at the second reason in detail. First, there is the program header:

put_line 
   (cor_start || proc_header (v_name, params_in), indent_in);

While it is true that I don't have a separate put_header program, this call to put_line uses two functions to encapsulate much of the header logic. Most importantly, the proc_header function returns the header string. This function is called in several different programs in PLVgen.

After the header of the function, I need the IS and BEGIN clauses. These are provided by the put_is_begin procedure:

put_is_begin (v_name, indent_in);

Then it is time for executable statements:

IF exec_in IS NOT NULL
THEN
   put_line 
      (RTRIM (exec_in, ';') || ';', 
       indent_in + v_incr_indent);
END IF;

Notice that I make sure there is a single semicolon at the end of the supplied string. That way, the user can leave it off and it won't make any difference (one example of "self-correcting" software; the smarter I make my code, the more likely and widely it is going to be used). I also indent this executable statement by an additional amount to offset it from the BEGIN keyword (a section delimiter).

I then terminate the procedure in three steps. First, I insert a call to PLVtrc.terminate if the trace is in use (again, notice the incremental indentation):

put_terminate (v_name, indent_in + v_incr_indent);

Then I put a WHEN OTHERS exception section (unless told not to):

IF incl_exc_in
THEN
   put_when_others (v_name, indent_in + v_incr_indent);
END IF;

Finally, it is time to issue the END statement to close the procedure:

put_end (v_name, indent_in);

By modularizing my code in this way, I am able to avoid superfluous and time-consuming inline documentation. The breakout of the modules explains much of what I am doing in the program. In addition, it is much easier to maintain and enhance this program. If I want to add code to the executable section, I make changes within that IF statement. If I need to enhance the way I terminate my generated program units, I will do so in put_terminate and/or put_end . The main body of the proc procedure can remain as it is.

Now that I have walked you through one of my high-level generators, let's dive down into the lowest level put program: put_line .

16.4.3.1 Flexibility of put_line

The following examples of calls to put_line give you an idea of its flexibility:

1. Display one blank line.

   put_line;

2. Display the word NULL; surrounded by blank lines.

   put_line ('NULL;', 0, c_both);

3. Indent a call to PLVtrc.startup by the standard incremental indentation and display a blank line after that program call.

   put_line ('PLVtrc.startup', v_incr_indent, c_after);

Notice that in the second example I had to include a value of 0 for the incremental indentation. After a while, I found this practice annoying. The zero value is really just filler -- a placeholder so I could specify the blank-line behavior without using named notation. To get around this artificial coding, I have also overloaded put_line as follows:

PROCEDURE put_line 
   (stg_in IN VARCHAR2, blanks_in IN VARCHAR2)
IS
BEGIN
   put_line (stg_in, 0, blanks_in);
END;

16.4.3.2 Output mechanism of put_line

Did you notice that put_line does not call DBMS_OUTPUT.PUT_LINE? It doesn't even call p.l , that ubiquitous displayer of output from a PL/SQL program. In fact, PLVgen relies on the PLV io.put_line to generate the code. Why did I bother with PLVio? What does the user have to gain from this extra layer of code? A tremendous amount of flexibility, namely with the ability to redirect the output of generated code.

What if I want to plug-and-play my code generator from within a GUI interface (e.g., Oracle Forms or PowerBuilder)? If I rely on DBMS_OUTPUT.PUT_LINE, this GUI tool would have to be able to read information from the DBMS_OUTPUT buffer and then manipulate that data. I don't know about PowerBuilder, but I have not been successful in reading the DBMS_OUTPUT buffer from Oracle Forms (if you want to try, check out the GET_LINE procedure in the DBMS_OUTPUT package).

With the PLVio package, I can redirect my output to a database table, PL/SQL table, or (with Release 2.3) an operating system file. To accomplish this I do not have to change PLVgen. I do not have to change any of the programs in which I have embedded calls to PLVgen elements. All I have to do is "flip a switch" by calling the settrg procedure of PLVio. For example, if I want to send my output to a PL/SQL table, I would issue this command before any calls to the PLVgen package:

PLVio.settrg (PLV.pstab);

Then all calls to PLVgen.put_line would add another row to the PL/SQL table defined in PLVio. The GUI environment can then extract the text from these rows and manipulate them within the GUI environment. You would not have to make a single change to the PLVgen package to accomplish this switch!

NOTE: If a PLVio target repository has not been selected by the time PLVgen is first called, the initialization section of the PLVgen package body automatically sets the target to "standard out," as shown below:

PACKAGE BODY PLVgen
IS
   /* All the package elements */
BEGIN
   /* If the target has not been set, use standard output. */
   IF PLVio.notrg
   THEN
      PLVio.settrg (PLV.stdout);
   END IF;
END;

16.4.3.3 Modularization inside put_line

The put_line procedure calls PLV io.put_line to send the line of generated code to the designated repository. What text is sent to PLVio.put_line ? The PLVgen.put_line procedure actually makes use of two other private functions, stg_with_ln and indent_stg , to construct the string for output.

The stg_with_ln function incorporates logic required to display a line number before the line of code. It hides the logic and private variables shown below:

FUNCTION stg_with_ln (stg_in IN VARCHAR2 := NULL)
RETURN VARCHAR2
IS
BEGIN
   IF usingln
   THEN
      v_currln := v_currln + 1;
      RETURN 
         (LPAD (TO_CHAR (v_currln), 5) || ' ' || stg_in);
   ELSE  
      RETURN stg_in;
   END IF;
END;

The indent_stg function encapsulates the logic required to properly indent the line of code according to the current indent setting (initial and incremental). The body of indent_stg is shown below:

FUNCTION indent_stg (incr_indent_in IN INTEGER := 0) 
   RETURN VARCHAR2
IS
BEGIN
   RETURN (RPAD (' ', v_indent + incr_indent_in));
END;

The put_line program is the only module in PLVgen that calls either stg_with_ln or indent_string . One could, therefore, argue that these modules represent an unnecessary layer of code. That may be the case for indent_stg . When I first wrote put_line , however, I did not realize that I would be so successful at funneling all output through put_line . I only knew that I did not want to embed the RPADding logic required for indentation right into put_line . It felt safer to me to hide that implementational detail behind a function. I applied the same reasoning to handling line numbers -- and that paid off immediately.

The put_line procedure of PLVgen does, in fact, call stg_with_ln three times in its short body. Since a blank line should have a line number as well, I needed to apply that logic in all three calls to PLV io.put_line . If I did not consolidate line number handling inside a separate function, I would have repeated the formula and code in put_line .

This instinct to hide code behind a procedure or function is one you should develop and then cultivate actively. You may in some cases end up writing a program or two that is only used once. The vast majority of your modules will, however, be reused and reused often. If you are sufficiently fanatical about modularization you eventually reach a critical mass of code: a strong development foundation that enables you to implement complex programs quickly and with few bugs.

16.4.3.4 Building on put_line

Once the put_line procedure was in place, I could create many other, more specialized put programs to handle different aspects of PL/SQL code. You have already seen this specialization in the proc procedure with such programs as put_terminate . There are, in fact, ten different put programs:

put_all_help
put_begin
put_comment
put_cor_end
put_end
put_header_cmnt
put_help
put_is_begin
put_terminate
put_when_others

Some of the programs, such as put_header_cmt shown below, make direct calls to put_line :

PROCEDURE put_header_cmnt 
   (name_in IN VARCHAR2,
    indent_in IN INTEGER := 0,
    file_in IN VARCHAR2 := NULL,
    author_in IN VARCHAR2 := v_author)
IS
BEGIN
   IF using_hdr
   THEN
      put_line ('/*', indent_in);
      put_line ('|| Program: ' || name_in, indent_in);
      put_line ('||  Author: ' || author_in, indent_in);
      put_line ('||    File: ' || file_in, indent_in);
      put_line ('|| Created: ' || PLV.now, indent_in);
      put_line ('*/');
   END IF;
END;

Other programs call both put_line and other put procedures, such as put_end :

PROCEDURE put_end 
   (prog_in IN VARCHAR2,
    indent_in IN INTEGER := 0,

    incl_term_in IN BOOLEAN := FALSE)
IS
BEGIN
   IF incl_term_in
   THEN
      put_terminate (prog_in, indent_in+v_incr_indent);
   END IF;
   put_line ('END ' || prog_in || ';', indent_in);
   put_cor_end;
END;

The conclusion to be drawn from all these layers of code is that you should always take fullest possible advantage of the opportunity to modularize. No user of PLVgen will ever know about all those different, private modules. But when it is time to add another code generator or enhance an existing program, those internal modules make it much easier to implement the changes.

Another moment when all of this internal modularization will come in handy is when I enhance PLVgen so that it can be used by other developers to build their own customized code generators. To accomplish this, I need to publicize many of my private modules by putting their headers in the PLVgen specification. Then you can more easily construct code generators that reflect your own coding standards and specific program units -- without modifying the base PLVgen package itself.

16.4.4.1 Implementing the func procedures

Examine the set of four overloaded definitions of func in Example 16.3 . The version shown above is the only one which has a string as the first argument and a number as the second argument. As a result, the PL/SQL engine executes the code for that procedure, which follows:

PROCEDURE func
   (name_in IN VARCHAR2,
    datadesc_in NUMBER, 
    defval_in IN NUMBER,
    incl_exc_in IN BOOLEAN := TRUE)
IS
BEGIN
   ifunc 
      (name_in,
       c_number, 
       NVL (TO_CHAR (defval_in), 'NULL'), 
       NULL,
       incl_exc_in);
END;

Lo and behold, the entire body of the func procedure is nothing more than a call to ifunc , which is the internal version of the func procedure. In fact, every single one of the other seven overloaded versions of func also does nothing more than call ifunc . Here, for example, is the body of the version of func used to generate a date function:

PROCEDURE func
   (name_in IN VARCHAR2,
    datadesc_in DATE, 
    defval_in IN DATE,
    incl_exc_in IN BOOLEAN := TRUE)
IS
BEGIN
   ifunc 
      (name_in,
       c_date, 
       NVL (TO_CHAR (defval_in), 'NULL'), 
       NULL,
       incl_exc_in);
END;

There is, in fact, only one difference between the bodies of these procedures.

16.4.4.2 Translating data to constant

In the number version, the second argument passed to ifunc is a constant: c_number . In the date version, I pass c_date in the second position. What I have done is convert the datatype of the second argument in func (the datadesc_in parameter) into a string that indicates the type of function to generate. In this way I am able to implement all of the different function generators with a single procedure ( ifunc ), greatly reducing the size of PLVgen and making it easy for me to maintain and enhance all of the function generators at once.

Here are the definitions of the datatype constants:

c_varchar2 CONSTANT VARCHAR2(8) := 'VARCHAR2';
c_date CONSTANT VARCHAR2(8) := 'DATE';
c_boolean CONSTANT VARCHAR2(8) := 'BOOLEAN';
c_number CONSTANT VARCHAR2(8) := 'NUMBER';

Notice that another aspect of calling ifunc is that I convert the default value into a string. Furthermore, if the default value is NULL, I pass a string NULL. Again, this conversion process allows me to implement all of the function generators with a single procedure that has the following header:

PROCEDURE ifunc
   (name_in IN VARCHAR2,
    datadesc_in VARCHAR2, 
    defval_in IN VARCHAR2,
    length_in IN INTEGER,
    incl_exc_in IN BOOLEAN := TRUE)

The arguments of ifunc are the same as those for func , with the following differences:

1. The datadesc_in and defval_in arguments are always and only VARCHAR2. All datatype differences have at this point been converted to constants and merged in the bodies of the func procedures.

2. The fourth argument, length_in , is required only for VARCHAR2 functions so it didn't appear in the headers for number and date versions of func (it is present, on the other hand, in the string version -- see Example 16.3 ). The calls to ifunc in those procedures simply pass NULL for the length argument.

The body of ifunc closely parallels that of the proc procedure. Differences reflect the special structure of a function: the RETURN clause and RETURN statements, the declaration of a local "return value" variable (necessary to conform to my coding standards). I will not go over this implementation here, since the focus is on overloading. I direct your attention, however, to the way that the IS and BEGIN keyword are put separately in a function, since I declare a local variable in between, using the var_declare private function.

16.4.4.3 Overloading for unevaluated default values

So far I have examined overloadings of func for each of VARCHAR2, NUMBER, DATE, and BOOLEAN datatypes in which the default value passed in is of the same datatype as the function. These versions of func allow me to specify a default value which is evaluated and then placed in the local variable declaration.

Suppose I want to create a date function that contains as a default value the first day of 1996. I would call func as follows:

SQL> exec PLVgen.func ('day_offset', SYSDATE, TRUNC (SYSDATE, 'YYYY'));
   FUNCTION day_offset RETURN DATE
   IS
      retval DATE := '01-JAN-96';
   BEGIN
      RETURN retval;

   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN NULL;
   END day_offset;

In this situation, the expression TRUNC (SYSDATE, 'YYYY') was evaluated by ifunc . The resulting value was then placed after the assignment operator in the declaration. That works just fine. What if, on the other hand, I don't want the default value to be the first day of 1996? What if, instead, I want the default to be the first day of the current year -- whatever that might be? I wouldn't want the default value evaluated. Rather, it should be treated as a literal -- a string, in fact -- and passed on to the assignment without parsing and evaluation.

In this scenario, my call to func would look like this:

SQL> exec PLVgen.func
             ('day_offset', SYSDATE, 'TRUNC (SYSDATE, ''YYYY'')');

and the resulting declaration of the return value variable in the generated function would look like:

IS
   retval DATE := TRUNC (SYSDATE, 'YYYY');
BEGIN

This kind of default value is surely going to be a common occurrence when generating code in the real world. So if PLVgen is going to be truly useful, it needs to be able to handle this variation.

16.4.4.4 The final overloading frontier

Fortunately, the flexibility provided by overloading lets me get the job done in a straightforward manner. Did you notice that the last call to func (passing the string version of TRUNC (SYSDATE, 'YYYY')) contains a sequence of arguments not supported by the overloadings of func shown in Example 16.3 (I pass string-date-string instead of string-date-date). To handle this combination, I need to create another overloading of func , one that accepts a string default value. This version of func is shown below:

PROCEDURE func
   (name_in IN VARCHAR2,
    datadesc_in DATE, 
    defval_in IN VARCHAR2,
    incl_exc_in IN BOOLEAN := TRUE);

Notice that in this case I do not provide a default value of NULL for the defval_in parameter. If I did so, then I would have an ambiguous overloading. A call to func that only supplied the first two parameter values would be syntactically valid, but would generate the following runtime error:

PLS-00307: too many declarations of 'FUNC' match this call

The PL/SQL engine would not know which of the two versions of func to execute (in both cases only the first arguments are required and they are string-date in both versions). By leaving off a default for the defval_in parameter, I force a user to provide three values, the third of which is a string, thereby ensuring that any valid execution of func identifies uniquely one of the func overloadings.

Example 16.4 shows the additional overloadings for DATE, NUMBER, and BOOLEAN. Together with the versions shown in Example 16.3 , I have now presented and explain the full set of overloadings (seven) for the func procedure. The same number of and rationale for overloadings is, by the way, applicable to the PLVgen.gas procedures.

16.4.4.5 Special casing unevaluated string values

I have presented all of the overloadings, but have not yet finished explaining the full range of functionality available with the func procedures. Have you wondered why I have only (!) seven overloadings of func ? Why don't I offer another overloading to support nonevaluated default values for string functions, for a total of eight overloadings? That would provide a symmetry one might expect in the PLVgen code.

Contrary to first impressions, however, I cannot overload two different versions of the VARCHAR2 function generator as I did for the other datatypes. Recall that the third argument of the original VARCHAR2 func is already a string. An overloading that followed the same approach would simply be a duplicate. You can see my dilemma in the two calls to func shown below:

SQL> exec PLVgen.func ('full_name', 'A', 'SMITH, SALLY');
SQL> exec PLVgen.func ('full_name', 'A', 'LPAD (last_name_in)');

These two uses of func look very different to you and me; it is easy to see how and why they should be treated differently in the generated code. To the PL/SQL engine, however, there is no distinction. As a result, I needed to come up with a way to tell my package when it had an expression that should not be evaluated.

The approach I took was to set the following rule: if you want the default value to be passed untouched to the generated function, prefix your default value with a = . With this convention, I would change the last example of a call to PLVgen.func to:

SQL> exec PLVgen.func ('full_name', 'A', '=LPAD (last_name_in)');

This special case is recognized and handled in the var_declare function. This function is called within ifunc to define a local variable to RETURN from the function, as shown below:

put_line 
   ('v_' || 
    var_declare (v_name, datadesc_in, defval_in, length_in));

Inside var_declare , the following IF statement is then executed:

IF SUBSTR (defval_in, 1, 1) = c_literal AND
   LENGTH (defval_in) > 1
THEN
   v_defval := SUBSTR (defval_in, 2);
ELSIF datadesc_in IN (c_varchar2, c_date)
THEN
   v_defval := PLVchr.quoted1 (defval_in);
END IF;

Translation: if the first character is an equal sign and there is more to the default than simply an equal sign, set the default value to the expression following the equal sign. Otherwise, if the datatype of the variable is a string or a date, embed the default value in single quotes. The default value is then concatenated into the variable declaration statement.

The multiple versions of func and gas in PLVgen offer several interesting lessons in package-based overloading. First and most importantly, overloading provides a smooth and easy to use interface. The user of PLVgen only has to remember func in order to generate a function, regardless of the datatype (within the range of supported datatypes, of course). This is much simpler than remembering different names, such as string_func and date_func .

Second, from the implementational view, PLVgen shows how to merge all those different public func procedures into a single, internal ifunc procedure. By converting user-entered values to constants that are recognized by the package, I can keep the code required to implement all these variations down to an absolute minimum.

Finally, the steps taken to allow for nonevaluated defaults for the VARCHAR2 function illustrate the kind of creative thinking (or is it just a workaround kludge?) in which you must sometimes engage in order to surmount obstacles in PL/SQL development.