16.3.1 Indentation in PLVgen

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:

PROCEDURE 

set_indent     (indent_in IN NUMBER,      incr_indent_in IN NUMBER := c_incr_indent);

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.

16.3.2 Setting the Code Author

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:

PROCEDURE 







set_author (author_in IN VARCHAR2); FUNCTION author RETURN VARCHAR2;

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.

16.3.3 Using the Program Header

You can decide if you want PLVgen to include a standard header in your generated program units (packages, functions, and procedures). The format for this header is:

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

You can toggle the header on or off. In addition, you can call a function to determine the current status of the "use program header" toggle. The headers for these three programs are:

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

16.3.4 Using the Program Trace

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.

16.3.5 Using the PLVexc Exception Handler

The PLVexc package provides a high-level, declarative approach to exception handling in PL/SQL programs. It is a powerful facility for consistent, high-quality handling of errors.

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

You can toggle the inclusion of PLVexc on or off. In addition, you can call a function to determine the current status of the "use program PLVexc" toggle. The headers for these three programs are:

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;

NOTE: If you want to use a different PLVexc handler, such as rec_halt , you need to cut and paste and then edit your generated code (you could, alternatively, change the name of the default handler, that is stored in c_PLVexc_handler in the PLVgen body).

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.

16.3.6 Generating Comments

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.

NOTE: The header and the help text stubs are not affected by the setting of this toggle. They are PL/SQL comments, but they act as specialized text within the PLVgen package and so are treated differently (with their own toggles).

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.

PLVgen gives you the ability to choose whether you want to generate a program with the CREATE OR REPLACE syntax. The three programs managing this toggle are listed here:

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!

This is not a recipe for usability. So in addition to providing each of the individual toggles, I provide two other "master switches" for setting the toggles:

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.