#

| Chapter 16 PLVgen: Generating 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:

- Use consistent indentation to reveal the logical flow of the program and delineate the different sections of the block structure.

- Include in all programs an exception section to handle abnormal behaviors. Taking this guideline to the next level of reusability, PLVgen offers programs (implemented through the PLVexc package) to call in the exception handlers.

- Code all reserved words in the PL/SQL language in uppercase. Use lowercase for all application-specific identifiers. Generally, this is accomplished with hard-coded literals and the use of UPPER and LOWER. This guideline presents more of a challenge when applied to complex expressions passed to PLVgen as default values.

- Employ a "template" approach to functions in which there is a single successful RETURN statement that returns a locally declared variable of the same datatype as the function.

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

16.3 Modifying PLVgen Behavior |  | 17. PLVhlp: Online Help for PL/SQL Programs

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