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:
It would grow to monstrous proportions and eventually (soon?) hit the upper limit for code size in PL/SQL.
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.
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.
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:
Puts out a line of generated code.
Returns the CREATE OR REPLACE syntax if using_cor returns TRUE. Otherwise, returns NULL.
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.
Puts a program header (comment block with author, program name, etc.) after the procedure header.
Puts in all current comment blocks for online help.
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.
Puts a call to PLVtrc.terminate at the end of the procedure if using_trc returns TRUE.
Puts an exception section with a WHEN OTHERS clause if the incl_exc_in argument is TRUE.
Puts the END statement at the, well, the end of the procedure.
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:
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.
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.
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:
The string to be displayed. The default is NULL, which would produce a blank line.
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.
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).
The following examples of calls to put_line give you an idea of its flexibility:
Display one blank line.
Display the word NULL; surrounded by blank lines.
put_line ('NULL;', 0, c_both);
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;
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:
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;
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.
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 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.
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.
PLVgen takes advantage of the overloading feature of packages in a very interesting way to accomplish two important objectives:
Make it as easy as possible to generate code, particularly functions.
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.
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 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.
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.
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.
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:
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;
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.
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:
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).
Returns the current date and time as a formatted string.
Returns a string that conforms to the guidelines used by PLVhlp to mark the beginning of a block of help text.
Returns a string that conforms to the guidelines used by PLVhlp to mark the ending of a block of help text.
Applies the UPPER-lower method to the parameter list of a program, and to the default value of a variable
Embeds the specified string inside single quotes.
Returns the string TRUE if a Boolean evaluates to TRUE. Otherwise returns the string FALSE.
Shows any help text defined in the PLVgen package to help developers understand and use this package.
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.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.