#

| Chapter 16 PLVgen: Generating PL/SQL Programs |

## 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
Behavior | Programs | Description
--- | --- | ---
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.
Add CREATE OR REPLACE | `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:

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

16.2 Code Generated by PLVgen |  | 16.4 Implementing PLVgen

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