The PLVgen package is a tool to improve the quality of life of developers. It can be used directly in an environment like SQL*Plus. It can also be utilized from within a GUI interface to make it easier to select options and combine different code fragments. Given the wide variety of circumstances under which PLVgen could be used, and the variety of coding styles used by developers, I face a big challenge in implementing PLVgen: make it flexible!
I cannot, for example, force anyone to use my PLVexc package to handle exceptions. An application team might not want to use the PLVtrc package to build in an execution trace. If I don't give users of PLVgen the option to use and ignore these different elements, I will not have very many users of the package. Consequently, I supply a variety of get-and-set programs in PLVgen to toggle various aspects of generate behavior. I even used early versions of PLVgen to generate get-and-set programs for the PLVgen package itself!
PLVgen offers a set of toggles to turn on or off the inclusion of various elements of code. It also provides a set of programs to modify the appearance of output, particularly as regards indentation. These programs are listed in Table 16.1 and are explained in the following sections.
There are two elements to indentation: the initial indentation and the next or incremental indentation. The initial value is the number of spaces inserted before any line of code is generated. The incremental indentation is the number of spaces indented for each successive indent (code within a loop, declarations within the declaration section, and so on). PLVgen provides a single set program for both of these values:
The default starting indentation is 0. The default incremental indentation is 3. Use the set_indent procedure to change either or both of these values.
PLVgen provides two functions to return the current indentation values. The indent function returns the current starting indentation value. The incr_indent function returns the current incremental indentation value. The headers for these functions are shown below:
FUNCTION indent RETURN NUMBER; FUNCTION incr_indent RETURN NUMBER;
When might you call set_indent ? If you are going to generate a function to stick inside a package, you want to set the starting indentation at 3 (or whatever your standard is) so that the resulting code is indented properly within the context of the package. This saves you editing time in which you insert spaces at the beginning of each line.
If you are generating a header for your code, you can set the name of the author placed in the header. The default for the author is NULL. You can call the set_author procedure to change the author. Call the author function to retrieve the current author name.
The headers for these two functions are shown below:
The following execution of set_author , for example, sets the name to "Steven Feuerstein".
SQL> exec PLVgen.set_author ('Steven Feuerstein');
To make certain that this author value is always set for me when generating code, I include the above command in my login.sql script. This file is executed automatically on startup of SQL*Plus and initializes my environment.
/* || Program: || Author: || File: || Created: */
The program name is usually taken from other inputs to the generator program. The author string is set through a call to PLV gen.author . The file name is constructed from the program string. The create date/time stamp is SYSDATE.
PROCEDURE usehdr; PROCEDURE nousehdr; FUNCTION using_hdr RETURN BOOLEAN;
You call usehdr to turn on use of the header and nousehdr to turn off the header. Call the using_hdr function if you want to know whether the header is being used. (This is included mostly as a courtesy and for completeness. Usually you simply turn the feature on or off and be done with it.)
The PLVtrc package offers the ability to maintain a trace of the programs that are currently on the execution stack of PL/SQL . PL/SQL itself provides this information with the DBMS_UTILITY.FORMAT_CALL_STACK function, but that stack does not show the names of programs within a package -- a serious drawback for package-centered PL/SQL code development. So you can call PLVtrc.startup to indicate that a particular program has started. And you call PLVtrc.terminate to signal that the program has ended. See Chapter 21, PLVlog and PLVtrc: Logging and Tracing , for more information on how to use PLVtrc.
The PLVgen package is "PLVtrc-aware." It automatically inserts calls to the startup and terminate programs of the trace facility if you turn on this feature. You can toggle the trace on or off. In addition, you can call a function to determine the current status of the "use program trace" toggle. The headers for these three programs are:
PROCEDURE usetrc; PROCEDURE nousetrc; FUNCTION using_trc RETURN BOOLEAN;
You call usetrc to turn on use of the trace and nousetrc to turn off the trace. Call the using_trc function if you want to know whether the trace is being used.
When you are using the program trace, a generated procedure has at a minimum the following body or execution section:
PROCEDURE calc_totals IS BEGIN PLVtrc.startup ('calc_totals'); PLVtrc.terminate; END calc_totals;
Notice that PLVgen automatically inserts the name of the current program unit to PLVtrc.startup ; you don't have to mess with this. You just insert all of your application-specific code between the startup and terminate lines. By using PLVgen, you can make the use of PLVtrc practical and comprehensive. This is especially worthwhile when you are going to take advantage of the high-level exception handlers provided by PLVexc.
The PLVgen package is also "PLVexc-aware." It automatically creates an exception section and provides a single WHEN OTHERS handler that calls a PLVexc handler procedure. There are two possible handlers that will be placed in the exception section. If you are using PLVtrc (you have called PLVgen.usetrc ), then the more abstract rec_continue procedure will be used. If you are not using PLVtrc, PLVgen inserts a call into the lower-level PLVexc.handle procedure (see Chapter 22, Exception Handling , for more information about the differences between these two programs).
PROCEDURE useexc; PROCEDURE nouseexc; FUNCTION using_exc RETURN BOOLEAN;
You call useexc to turn on use of the PLVexc and nouseexc to turn off the PLVexc. Call the using_exc function if you want to know if PLVexc is being used.
When you are using exception handling and the trace facility (enabled by a call to PLVgen.usemax or to both PLVgen.useexc and PLVgen.usetrc ), the exception section for a procedure looks like this:
EXCEPTION /* Call PLVexc in every handler. */ WHEN OTHERS THEN PLVexc.rec_continue; END calc_totals;
Notice the comment line before the exception handler. If you are going to use PLVtrc in conjunction with PLVexc, you must call a PLVexc exception handler procedure in every exception handler section.
If you have turned on exception handling in PLVgen, but have turned off use of PLVtrc, the exception section for your generated programs looks like this:
EXCEPTION WHEN OTHERS THEN PLVexc.handle (calc_totals, SQLCODE, PLVexc.rec_continue); END calc_totals;
When you are not using PLVexc-based exception handling (you have called PLVgen.nouseexc ), the exception section for a procedure looks like this:
EXCEPTION WHEN OTHERS THEN NULL; END calc_totals;
So even when you do not take advantage of PL/Vision-based exception handling, PLVgen still generates an exception section in your code. This is an important element of best practices for module construction and should almost never be compromised.
PLVgen includes a number of different kinds of comments in your generated code. You can toggle the generation of these comments on or off. In addition, you can call a function to determine the current status of the "use comments" toggle. The headers for these three programs are:
PROCEDURE usecmnt; PROCEDURE nousecmnt; FUNCTION using_cmnt RETURN BOOLEAN;
You call usecmnt to turn on use of the comment lines and nousecmnt to turn it off. Call the using_cmnt function if you want to know whether the comments are being used.
16.3.7 Generating Online Help Text Stubs
You can control the generation of online help text stubs (available through use of the PLVhlp package) with the "use help" toggle. You can toggle the generation of these stubs on or off. In addition, you can call a function to determine the current status of the "use help" toggle. The headers for these three programs are:
PROCEDURE usehlp; PROCEDURE nousehlp; FUNCTION using_hlp RETURN BOOLEAN;
You call usehlp to turn on use of the comment lines and nousehlp to turn it off. Call the using_hlp function if you want to know whether the comments are being used.
If you know that you are not going to use PLVhlp to make online help available to users, you can keep these extraneous comments out of your programs with a call to the PLVgen.nousehlp procedure. If you generate these stubs into your programs and then never use them, you will have absolutely no adverse impact on your program execution.
16.3.8 Generating Line Numbers
PLVgen lets you generate line numbers for your programs. This may not mean much to any of you, but it sure was important to me. I wanted to be able to point out specific lines of generated code for your attention, so I built this facility into the package itself.
You can toggle the generation of these line numbers on or off. In addition, you can call a function to determine the current status of the "use line numbers" toggle. The headers for these three programs are:
PROCEDURE useln; PROCEDURE nouseln; FUNCTION using_ln RETURN BOOLEAN;
You call useln to turn on use of the line numbers and nouseln to turn it off. Call the using_ln function if you want to know whether the line numbers are being generated.
16.3.9 Including CREATE OR REPLACE
When you are generating code to be compiled and stored in the database through SQL*Plus, you need to use the CREATE OR REPLACE syntax. In all other situations (generating code for inclusion in an Oracle Developer/2000 environment or to be pasted into a GUI development environment like Oracle Procedure Builder), you will not use CREATE OR REPLACE. That part of the syntax is done for you.
PROCEDURE usecor; PROCEDURE nousecor; FUNCTION using_cor RETURN BOOLEAN;
Call the usecor program to request that a CREATE OR REPLACE be put before the program unit name -- and a forward slash be added as the last line of the generated output. Call nousecor to ignore this DDL syntax. Finally, call using_cor to find out the current status of this toggle.
16.3.10 Setting Groups of Toggles
As you can see, there are many options when it comes to modifying the output and behavior of the PLVgen package. Without this flexibility, I doubt that anyone would ever find a package like PLVgen useful. On the other hand, there may be one thing worse than offering no options: offering too many options.
Suppose, for example, you wanted to turn on all of the different toggles available in PLVgen. You would then have to execute all of the following commands:
SQL> exec PLVgen.usehdr SQL> exec PLVgen.useexc SQL> exec PLVgen.usehlp SQL> exec PLVgen.usecor SQL> exec PLVgen.usetrc SQL> exec PLVgen.usecmt SQL> exec PLVgen.useln
There are at least two big problems with this: you would have to know about all of these options -- and the set of options will quite likely be expanding in the foreseeable future. You would also have to do an awful lot of typing -- you simply wouldn't bother!
PROCEDURE usemin; PROCEDURE usemax;
The usemin program turns off all the toggles; the usemax turns them all on. If you want to make sure that you generate line numbers, but otherwise have everything else turned off, you would execute these two commands before generating code:
SQL> exec PLVgen.usemin; SQL> exec PLVgen.useln;
As PLVgen is expanded to support new toggles and areas of flexibility, I expect that it will be necessary to create other programs to turn on or off various sets of toggles. These combinations will create "preferences" and further improve the usability of the package.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.