Advanced Oracle PL/SQL Programming with Packages

Advanced Oracle PL/SQL Programming with PackagesSearch this book
Previous: 16.3 Modifying PLVgen BehaviorChapter 16
PLVgen: Generating PL/SQL Programs
Next: 17. PLVhlp: Online Help for PL/SQL Programs
 

16.4 Implementing PLVgen

The PLVgen package is a large package. The body alone contains more than 20,000 characters spread over 1,390 lines of code. It is not, however, a terribly complicated package. Each of the "public" programs (the code generators) is, at heart, a sequence of commands to output various combinations of text.

The biggest challenge in constructing PLVgen was to minimize the amount of redundant code. There are many common lines of text, for example, between the template for a function and procedure. And my package allows you to generate many different kinds of functions and procedures. If I simply hard-coded the lines of text to be output for each different program unit, the PLVgen package would run into two problems:

  1. It would grow to monstrous proportions and eventually (soon?) hit the upper limit for code size in PL/SQL.

  2. It would be difficult to maintain and enhance. I would have to constantly cut and paste to build new generator procedures, since there would be little shared code. And what if I discovered a mistake in all of my different function generators? Or, even more likely, what if I want to add another toggle to fine-tune the look-and-feel of the generated code? I would have to make changes across many different program units in the package.

PLVgen is definitely one of those packages where a building-block approach was absolutely critical to a successful implementation. To explain PLVgen, I first review some of the best practices and coding styles I support through the package. Then I show the implementation of the high-level procedure generator (the proc procedure). Finally, I shift down to the lowest level of the package (the put_line procedure) and build my way up from there.

16.4.1 PLVgen Coding Guidelines

You will find implemented in PLVgen the following best practices and coding styles:

Keep these guidelines in mind as you examine the program units of PLVgen.

16.4.2 Implementing the Procedure Generator

This proc program generates a procedure as described in Section 16.2.2. Let's now go over the implementation of proc, as shown in Example 16.1.

Example 16.1: The Code for the proc Procedure

 1   PROCEDURE proc
 2      (name_in IN VARCHAR2,
 3       params_in IN VARCHAR2 := NULL, 
 4       exec_in IN VARCHAR2 := NULL,
 5       incl_exc_in IN BOOLEAN := TRUE,
 6       indent_in IN INTEGER := 0,
 7       blank_lines_in IN VARCHAR2 := c_before)
 8   IS
 9      v_name PLV.plsql_identifier%TYPE := LOWER (name_in);
10   BEGIN
11      initln;
12      put_line
13         (cor_start || proc_header (v_name, params_in), 
14          indent_in,
15          blank_lines_in);
16
17      put_header_cmnt (v_name, indent_in);
18
19      put_all_help (indent_in);
20
21      put_is_begin (v_name, indent_in);
22
23      IF exec_in IS NOT NULL
24      THEN
25         put_line 
26            (RTRIM (exec_in, ';') || ';', 
27             indent_in + v_incr_indent);
28      END IF;
29
30      put_terminate (v_name, indent_in + v_incr_indent);
31
32      IF incl_exc_in
33      THEN
34         put_when_others (v_name, indent_in + v_incr_indent);
35     END IF;
36
37     put_end (v_name, indent_in);
38   END;

The very first thing I do is declare a local variable, v_name, and set it to the lower-casing of the specified program name:

   v_name PLV.plsql_identifier%TYPE := LOWER (name_in);

This step enforces the style guide in which application-specific identifiers are entered in lowercase. It also takes advantage of the predefined datatype for PL/SQL identifier variables.

From this point onwards, the body of proc is composed of calls to a series of highly specialized procedures and functions defined in the body of the package. These programs are:

Procedure put_line (line 12)

Puts out a line of generated code.

Function cor_start (line 13)

Returns the CREATE OR REPLACE syntax if using_cor returns TRUE. Otherwise, returns NULL.

Function proc_header (line 13)

Returns a string with the header for the specified procedure. The header is all of the definition of the procedure up to the IS keyword.

Procedure put_header_cmnt (line 17)

Puts a program header (comment block with author, program name, etc.) after the procedure header.

Procedure put_all_help (line 19)

Puts in all current comment blocks for online help.

Procedure put_is_begin (line 21)

Puts the IS and BEGIN keywords in the procedure definition. This procedure also adds a call to the PLVtrc.startup procedure if using_trc returns TRUE.

Procedure put_terminate (line 30)

Puts a call to PLVtrc.terminate at the end of the procedure if using_trc returns TRUE.

Procedure put_when_others (line 34)

Puts an exception section with a WHEN OTHERS clause if the incl_exc_in argument is TRUE.

Procedure put_end (line 37)

Puts the END statement at the, well, the end of the procedure.

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 put_line Procedure

At the very core of the PLVgen package is the put_line procedure (see Example 16.2). This program is called by other private put procedures to output various combinations of text. It is also called directly from the high-level, public programs such as proc and toggle. The put_line procedure is the only way to generate code text from the PLVgen package.

Example 16.2: The put_line Procedure

PROCEDURE put_line 
   (stg_in IN VARCHAR2 := NULL, 
    incr_indent_in IN INTEGER := 0,
    blanks_in IN VARCHAR2 := c_none)
IS
BEGIN
   IF blanks_in IN (c_both, c_before)
   THEN
      PLVio.put_line (stg_with_ln);
   END IF;

   PLVio.put_line 
      (stg_with_ln (indent_stg (incr_indent_in) || stg_in));

   IF blanks_in IN (c_both, c_after)
   THEN
      PLVio.put_line (stg_with_ln);
   END IF;
END;

Since put_line is used in so many different ways, it must be flexible and, therefore, it must take several arguments. The header for put_line is as follows:

PROCEDURE put_line 
   (stg_in IN VARCHAR2 := NULL, 
    incr_indent_in IN INTEGER := 0,
    blanks_in IN VARCHAR2 := c_none)

The three arguments are described below:

stg_in

The string to be displayed. The default is NULL, which would produce a blank line.

incr_indent_in

The incremental indentation to be applied to the string. The default is 0, which means that the current level of indentation (set with a call to set_indent) is employed.

blanks_in

A string value which indicates the type of white space to generate around the line being displayed. The options are: c_none (no blanks), c_before (one line before), c_after (one line after), or c_both (one line before and after).

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 PLVio.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 PLVio.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 PLVio.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 Overloading in PLVgen

PLVgen takes advantage of the overloading feature of packages in a very interesting way to accomplish two important objectives:

  1. Make it as easy as possible to generate code, particularly functions.

  2. Minimize the volume of code required to implement a wide array of generators.

Let's take a close look at the overloading in PLVgen. As we've mentioned before, when you overload you define more than one program with the same name. These programs 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.

The func procedure, which generates functions, is overloaded seven times in PLVgen. The gas procedures, which generate get-and-set programs for a variable, is also overloaded seven times and in the same way. The techniques I employ in PLVgen to accomplish the overloading are quite interesting and informative. Let's examine the overloading in more detail for the func procedure and draw out some lessons.

First, let's take a look at the outcome of my overloaded func procedure. The following execution of func generates a numeric function called totals whose return value defaults to NULL.

SQL> exec PLVgen.func ('totals', 1);
   FUNCTION totals RETURN NUMBER
   IS
      retval NUMBER := NULL;
   BEGIN
      RETURN retval;

   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN NULL;
   END totals;

How do I know that a numeric function will be generated? More importantly, how does PLVgen know that it should create a function with a RETURN clause datatype of NUMBER -- and declare the retval variable to be of type NUMBER as well? It's got to be the overloading! The second argument passed to func was the value 1. Notice that this value does not appear anywhere in the generated function. It was simply used to direct the PL/SQL runtime engine to execute the appropriate func generator.

I could have entered any of the following calls to PLVgen.func and generated the very same function:

SQL> exec PLVgen.func ('totals', -16007.459);
SQL> exec PLVgen.func ('totals', INSTR ('abc', 'Q'));

In both of these cases, the second argument evaluates to a number. As a result, the following version of the func procedure would be executed:

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

Notice that the second argument has a datatype of NUMBER.

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.

Example 16.3: First Set of Overloaded Definitions for func

PROCEDURE func
   (name_in IN VARCHAR2,
    datadesc_in VARCHAR2, 
    defval_in IN VARCHAR2 := NULL,
    length_in IN INTEGER := c_def_length,
    incl_exc_in IN BOOLEAN := TRUE);

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

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

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

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.

Example 16.4: Last Three Overloadings of Func


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

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

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

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.

16.4.5 Applying the Toggles

With all of these toggles modifying the look-and-feel of the generated code, it is extremely important to find a way to apply the toggles without cluttering up the code. I accomplish this mostly through the use of those same specialized put programs discussed earlier.

Consider the put_comment program. This procedure accepts as input a string, any incremental indentation, and also a specifier for surrounding blank lines (the same three arguments as put_line itself). put_comment simply surrounds the string with the comment markers, /* and */, and then passes this commented string to put_line.

The following statement shows an example of a call to put_comment that outputs the string /* Public Modules */ indented three spaces past the default with a blank line both before and after the comment.

put_comment ('Public Modules', 3, c_both);

Yet if the user has executed either of the following lines:

SQL> exec PLVgen.usemin
SQL> exec PLVgen.nousecmnt

then I do not want put_comment to display anything. There are two different solutions to this situation:

  1. Nest every call to put_comment inside an IF statement like this:

    IF using_cmnt
    THEN
       put_comment ('Public Modules', 3, c_both);
    END IF;
  2. Put the IF statement inside the put_comment procedure. This approach is shown below:

    PROCEDURE put_comment 
       (stg_in IN VARCHAR2 := NULL, 
        incr_indent_in IN INTEGER := 0,      
        blanks_in IN VARCHAR2 := c_none)
    IS
    BEGIN
       IF using_cmnt
       THEN
          put_line (comment (stg_in), incr_indent_in, blanks_in);
       ELSIF blanks_in != c_none
       THEN
          put_line (NULL, incr_indent_in, c_none);
       END IF;
    END;

    I recommend the second approach. By hiding the IF statement inside put_comment, the code in each program that calls put_comment is tighter and cleaner. In addition, I do not have to remember the name of the function that tells me whether or not to use comments each time I call put_comment. Instead, I code it once inside put_comment and make the toggle transparent in my code.

    You see this same approach used throughout PLVgen. Again and again you find that each toggle is checked and applied as close as possible to the put_line the toggle is supposed to affect.

16.4.6 Leveraging PL/Vision in PLVgen

You may have noticed several references in PLVgen programs to modules from other PL/Vision packages, such as PLVio. In fact, PLVgen takes advantage of the following programs in the PL/Vision library:

PLVio.put_line

Called by PLVgen.put_line, this program sends a line of output to the current default target for the PLVio package. If this target has not been set before using this package, the initialization section of the package sets the target to be standard output (display to terminal).

PLV.now

Returns the current date and time as a formatted string.

PLVhlp.comment_start

Returns a string that conforms to the guidelines used by PLVhlp to mark the beginning of a block of help text.

PLVhlp.comment_end

Returns a string that conforms to the guidelines used by PLVhlp to mark the ending of a block of help text.

PLVcase.string

Applies the UPPER-lower method to the parameter list of a program, and to the default value of a variable

PLVchr.quoted1

Embeds the specified string inside single quotes.

PLV.boolstg

Returns the string TRUE if a Boolean evaluates to TRUE. Otherwise returns the string FALSE.

PLVhlp.show

Shows any help text defined in the PLVgen package to help developers understand and use this package.

PLVio.settrg

Sets the target for calls to PLVio.put_line to standard output if that target has not already been set in the current session.

In most of the uses listed above, the PL/Vision modules play modest roles. They mostly serve to encapsulate logic which, while uncomplicated, should not have to be known outside of the package. Two of the programs, PLVio.put_line and PLVcase.string, offer major added-value to the PLVgen package. I have already examined how PLVio.put_line is used in the Plvgen.put_line procedure to enhance the flexibility of the code generator to write code out to different repositories. The usage of PLVcase.string increases the elegance of the code generator and its ability to support best practices, in this case the automatic upper-casing of reserved words.


Previous: 16.3 Modifying PLVgen BehaviorAdvanced Oracle PL/SQL Programming with PackagesNext: 17. PLVhlp: Online Help for PL/SQL Programs
16.3 Modifying PLVgen BehaviorBook Index17. PLVhlp: Online Help for PL/SQL Programs

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference
This HTML Help has been published using the chm2web software.