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:
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.
Keep these guidelines in mind as you examine the program units of PLVgen.
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:
220.127.116.11 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:
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.
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.
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:
18.104.22.168 Flexibility of put_line
The following examples of calls to put_line give you an idea of its flexibility:
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;
22.214.171.124 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:
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!
126.96.36.199 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.
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;
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.
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:
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.
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.
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);
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:
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.
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.
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.
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);
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:
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:
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.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.