Advanced Oracle PL/SQL Programming with Packages

Advanced Oracle PL/SQL Programming with PackagesSearch this book
Previous: 16.2 Code Generated by PLVgenChapter 16
PLVgen: Generating PL/SQL Programs
Next: 16.4 Implementing PLVgen

16.3 Modifying PLVgen Behavior

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.

Table 16.1: Programs to Modify PLVgen Behavior and Output




Set author

set_author author

Sets and retrieves the current string used as the author in a program header. The default is NULL.

Set indentation

set_indent indent incr_indent

Sets and retrieves the two types of indentation: initial and incremental. The default for the indent is 0 and for the incremental is 3.

Use program trace

usetrc nousetrc

Inserts calls to PLVtrc.startup and PLVtrc.terminate in generated procedures and functions. Default is OFF.

Use exception handling

useexc nouseexc

Inserts a WHEN OTHERS exception handler that calls the exception handler PLVexc.rec_continue, a generic program that records the error and then continues execution. You can then add other handlers as well. Default is OFF.

Use program header

usehdr nousehdr

Places a header (within a comment block) for procedures and functions. This header uses the author name set with a call to set_author. Default is OFF.

Use comments

usecmnt nousecmnt

Places comments inside the generated code. Examples of comments include banners for the different components of a package and descriptions of the components of get-and-set routines. Default is ON.

Use online help

usehlp nousehlp

Puts stubs for help text in programs and also creates a procedure in your generated package to provide help for that package. Default is ON.


usecor nousecor

Adds the syntax necessary to CREATE OR REPLACE the generated procedure, function, or package. Default is OFF.

Use line numbers

useln nouseln

Displays the line number next to the source code.

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:


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

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 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 nousehdr;

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 nousetrc;

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
   PLVtrc.startup ('calc_totals');
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 nouseexc;

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:

   /* Call PLVexc in every handler. */
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:

      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:

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;

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 nousehlp;

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 nouseln;

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 nousecor;

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:


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.

Previous: 16.2 Code Generated by PLVgenAdvanced Oracle PL/SQL Programming with PackagesNext: 16.4 Implementing PLVgen
16.2 Code Generated by PLVgenBook Index16.4 Implementing PLVgen

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference
This HTML Help has been published using the chm2web software.