16.2.2.1 Maximum content of the generated procedure

Let's look at an example of procedure generation to get a feel for the way you can use the different arguments: generate a procedure with all the default argument values and the full set of additional code elements turned on by the call to usemax (explained later in this chapter).

SQL> exec PLVgen.usemax
SQL> exec PLVgen.proc ('calc_totals');
    1    
    2    CREATE OR REPLACE PROCEDURE calc_totals
    3    /*
    4    || Program: calc_totals
    5    ||  Author: null
    6    ||    File: calc_totals.SQL
    7    || Created: May 29, 1996 13:36:20
    8    */
    9    /*HELP
   10    Add help text here...
   11    HELP*/
   12
   13    /*EXAMPLES
   14    Add help text here...
   15    EXAMPLES*/
   16
   17    IS
   18    BEGIN
   19       PLVtrc.startup ('calc_totals');
   20       PLVtrc.terminate;
   21
   22    EXCEPTION
   23       /* Call PLVexc in every handler. */
   24       WHEN OTHERS
   25       THEN
   26          PLVexc.rec_continue;
   27    END calc_totals;
   28    /

Let's go through this code line by line so that you can understand clearly the different elements of code that are generated for a procedure and function. I won't repeat this explanation (or the volume of code) in the following sections.

Line 2 contains the header for the procedure header, including the CREATE OR REPLACE syntax. As you will see in later examples, you can provide a parameter list as part of this header as well.

Lines 3 through 8 contain the standard program header provided by PLVgen. Notice that the author is automatically set to "Steven Feuerstein". I make sure of this setting by including the following statement in my login.sql script:

exec PLVgen.set_author ('Steven Feuerstein');

Lines 9 through 15 are the stubs for help text on two topics: general help and examples help. These comment blocks are used both for inline documentation of the package and also for online help text for users (through the PLVhlp package).

Lines 19 and 20 call PLVtrc startup and terminate procedures. These programs provide an execution trace that is of particular use in the PLVexc exception- handling package. Again, you do not have to include these programs in your own generated code, but it is an available option.

Lines 22 through 26 show the standard PL/Vision exception handler section. A single WHEN OTHERS clause calls the PLVexc.rec_continue program, which records the error and continues processing. You can replace this PLVexc handler with another one (such as PLVexc.rec_halt or "record and halt"); the point to recognize here is that the generator creates an exception handler as a starting point, which reinforces best practices.

Lines 27 and 28 bring the procedure to a close. Notice that PLVgen automatically supplies an END label. It also provides the forward slash, that in conjunction with the CREATE OR REPLACE syntax results in a script that can be used immediately in SQL*Plus to store and compile the procedure.

NOTE: In most of the examples of generated code in the rest of this chapter, I turn off the many comments and other optional elements of the source code. By doing this, you can focus more easily on how PLVgen constructs the required elements of the PL/SQL programs. Section 16.3, "Modifying PLVgen Behavior" explains how to use some or all ( PLVgen.usemax ) of the optional elements of the code listed previously.

16.2.2.2 A procedure with parameter list and executable code

The following SQL*Plus session generates a procedure using a minimum of additional features, but with a parameter list and a single line of code in the body of the procedure.

SQL> exec PLVgen.usemin
SQL> exec PLVgen.proc ('disptot', 'comp_in in integer', 'get_total;');

PROCEDURE disptot (comp_in IN INTEGER)
IS
BEGIN
   get_total;

EXCEPTION
   WHEN OTHERS
   THEN
      NULL;
END disptot;

Notice that the keywords IN and INTEGER are automatically uppercased by PLVgen. This package makes use of the PLVcase.string program to convert the case of the parameter list and the executable line of code.

16.2.2.3 Changing indentation of generated code

In this final example of the procedure generator, I make use of the indentation parameter to add three spaces to the default three blanks provided by PLVgen. I could then easily cut and paste this text into the body of a package or into the declaration section of a nested, anonymous block.

SQL> exec PLVgen.usemin
SQL> exec PLVgen.proc ('disptot',indent_in=> 3);
      PROCEDURE disptot
      IS
      BEGIN

      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END disptot;

Notice that I use named notation (the => symbol associating indent_in with the value 3) so that I can avoid specifying values for all the other arguments.

16.2.3.1 Function generator headers

Here is the header for the program that generates a string or VARCHAR2 function:

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

Here are the headers for the two programs that generate Boolean functions:

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 BOOLEAN, 
    defval_in IN VARCHAR2,
    incl_exc_in IN BOOLEAN := TRUE);

In all of these programs, the name_in is the name of the function, and datadesc_in is an expression or value that is the same datatype as the function you want to generate. The defval_in argument provides a default value for the RETURN statement of the function. Notice that this is the only parameter that is different for the two overloaded Boolean function generators. In one case, the default value is a Boolean. In the second, the default value is a string. This distinction is explained below.

When generating a string function, you can also provide (through the length_in argument) the length of the VARCHAR2 variable to be declared. The default is provided by the constant c_def_length and is set to 100.

The incl_exc_in argument indicates whether or not an exception section should be included in the function. The default is "Yes! Include an exception section!" (and that is a very important thing to do in functions).

Now I will explain how to use these and the other function generators. The only information you have to provide to a function generator is the name and the datatype of the function.

16.2.3.2 Generated function examples

In this first example, I generate a string function named full_name simply by providing a string -- any string -- as the second argument.

SQL> exec PLVgen.func('full_name','a');
   FUNCTION full_name RETURN VARCHAR2
   IS
      retval VARCHAR2(100) := NULL;
   BEGIN
      RETURN retval;

   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN NULL;
   END full_name;

NOTE: See Section 16.2.2, "Generating a Procedure" for a more complete description of the elements of code that can be included in a generated function.

Notice that this function conforms to the template structure described earlier. Based on the datatype of the second argument, PLVgen has automatically determined that the RETURN datatype should be VARCHAR2. It has also declared a local variable with matching datatype and appropriate default value.

In the next call to PLVgen.func , I generate a Boolean function, this time setting the default value to FALSE and rejecting the use of an exception section.

SQL> exec PLVgen.func ('valid_account', true, false, FALSE);
   FUNCTION valid_account RETURN BOOLEAN
   IS
      retval BOOLEAN := FALSE;
   BEGIN
      RETURN retval;
   END valid_account;

Now I will generate this same function, but instead of providing a literal default value, I pass in a string that contains an expression (and include an exception section):

SQL> exec PLVgen.func
             ('valid_account', true, 'sysdate < account.start_date');
   FUNCTION valid_account RETURN BOOLEAN
   IS
      retval BOOLEAN := sysdate < account.start_date;
   BEGIN
      RETURN retval;

   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN NULL;
   END valid_account;

Since the second argument to PLVgen.func was a Boolean and the third a string, the PL/SQL runtime engine executed the version of func that interprets the default value not as a literal, but as an expression.

And that is the reason for the overloading of the two different Boolean function generators. When the default value is a Boolean, that value is applied directly (as a TRUE, FALSE, or NULL) to the local variable declaration. If the default is a string, that string becomes the unevaluated expression used as the default value for the local variable.

The function generators for dates and numbers are overloaded and work in the same way as the Boolean procedures do.

16.2.3.3 Default values for string functions

PLVgen provides two distinct overloadings for Boolean, date, and number function generators to handle literals and expressions for default values. This same approach is not possible for the procedure that generates string functions. The way PLVgen.func supports expressions for default values is that they are passed as strings. But since the default value for a string function is already a string, another overloaded version with a default value of datatype VARCHAR2 to handle string expressions would simply not work.

A different approach is required for the string function generator. There is just one version of func to generate string functions, but the following special rule applies to the default value argument, namely: if the first character of the default value string is an equal sign ( = ), then the string is to be interpreted as an expression. In this case, the value in the string is not evaluated, but simply placed in the default value area for the local variable of the function.

Let's look at a couple of examples. In this first call to PLVgen.func , I generate a VARCHAR2 function with a default return value of ABC. Notice that since this is a string function, I also specify the maximize length of the return value and request that the function not have an exception section.

SQL> exec PLVgen.func ('full_name', 'a', 'ABC', 50, FALSE);
   FUNCTION full_name RETURN VARCHAR2
   IS
      retval VARCHAR2(50) := 'ABC';
   BEGIN

      RETURN retval;
   END full_name;

In this next call to PLVgen.func , I change the default value to prefix the "ABC" with the equal sign. In the resulting declaration of a local variable, the default value is set to the PL/SQL identifier named abc, and not to a literal with that value.

SQL> exec PLVgen.func ('full_name', 'a', '=ABC', 50, FALSE);
   FUNCTION full_name RETURN VARCHAR2
   IS
      retval VARCHAR2(50) := abc;
   BEGIN

      RETURN retval;
   END full_name;

Let's look at an example that involves a more real world use of this expression default value. In the following call to PLVgen.func , I create a string function that sets the default value to a package-based constant.

SQL>  exec PLVgen.func ('full_name', 'a', '=Names.formal_address');
   FUNCTION full_name RETURN VARCHAR2
   IS
      retval VARCHAR2(100) := names.formal_address;
   BEGIN
      RETURN retval;

   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN NULL;
   END full_name;

If you ever want to generate a string function with a nonliteral default value, don't forget to prefix the default value string with an equal sign.

16.2.4.1 Generating a Boolean get-and-set

The naming scheme and structure for programs generated for a Boolean get-and-set is a bit different from that for strings, dates, and numbers. When building a get-and-set around a Boolean variable, you can and should take into account the fact that a user can only set the variable to one of three values (TRUE, FALSE, and NULL). In fact, in many cases, a NULL value is not allowed. If PLVgen.gas used the same syntax for Booleans as it does for numbers (see above listing), then the set program would look like this:

PROCEDURE set_show_changes (show_changes_in IN BOOLEAN);

and this program would be used as follows:

PKG_NAME.set_show_changes (TRUE);
PKG_NAME.set_show_changes (FALSE);

While there is certainly nothing wrong with this style, it strikes me as a cumbersome interface. A much cleaner, more natural style would allow me to call programs like this:

PKG_NAME.show_changes; -- Set variable to TRUE.
PKG_NAME.noshow_changes; -- Set variable to FALSE.

This is the very approach taken with the Boolean gas procedure. The full set of generated code (specification, variable declaration, and body code) is shown below for a show_changes variable:

SQL> exec PLVgen.gas ('show_changes', true, true);
   PROCEDURE show_changes;
   PROCEDURE noshow_changes;
   FUNCTION show_changesing RETURN BOOLEAN;

   v_show_changes BOOLEAN := TRUE;

   PROCEDURE show_changes
   IS
   BEGIN
      v_show_changes := TRUE;
   END show_changes;

   PROCEDURE noshow_changes
   IS
   BEGIN
      v_show_changes := FALSE;
   END no_show_changes;

   FUNCTION show_changesing RETURN BOOLEAN
   IS
      retval BOOLEAN := v_show_changes;
   BEGIN
      RETURN retval;
   END show_changesing;

Notice the third program, a function, in the get-and-set routines for a Boolean. This function is the get program and it returns the current value of the Boolean variable. I use the "ing" structure for the name of this function, as in: "Am I currently showing the changes?" PLVgen automatically appends an "ing" to the name of the variable passed in the call to the gas procedure. It is smart enough to convert some formats to a readable name. For example, if you request a get-and-set for the use_lines variable, PLVgen.gas generates a function with this header:

FUNCTION using_lines RETURN BOOLEAN;

and if you use PLVgen.gas with a variable name propose , it generates a function whose header is:

FUNCTION proposing RETURN BOOLEAN;

but PLVgen does not pretend to handle all nuances of the infinitely nuanced English language. As a result, variable names like show_changes result in a function named show_changesing and you will just have to rename it yourself!

16.2.4.2 Generating a toggle

A toggle is an on-off switch; it is actually a special case of the Boolean get-and-set routines. The toggle procedure generates the code required to implement this switch in PL/SQL .

The header for toggle is:

PROCEDURE toggle (name_in IN VARCHAR2 := NULL);

where name_in is the name of the toggle. If you do not specify a toggle name, the following code is generated:

SQL> exec PLVgen.usemin
SQL> exec PLVgen.useln
SQL> exec PLVgen.toggle
   PROCEDURE turn_on;
   PROCEDURE turn_off;
   FUNCTION turned_on RETURN BOOLEAN;

   v_onoff BOOLEAN := TRUE;

   PROCEDURE turn_on
   IS
   BEGIN
      v_onoff := TRUE;
   END turn_on;

   PROCEDURE turn_off
   IS
   BEGIN
      v_onoff := FALSE;
   END turn_off;

   FUNCTION turned_on RETURN BOOLEAN
   IS
      retval BOOLEAN := v_onoff;
   BEGIN
      RETURN retval;
   END turned_on;

You have generated, in other words, a generic toggle to turn something off or turn it on. You can also generate more specific toggles by providing a toggle name. If you pass a non-NULL toggle name, the get-and-set code matches those lines of source code generated by a call to PLVgen.gas for a Boolean variable.

16.2.5.1 Generating help text stubs

The helptext procedure generates a comment stub of the correct format so that the PLVhlp package can find and display the information. The header for helptext is:

PROCEDURE helptext 
   (context_in IN VARCHAR2 := PLVhlp.c_main);

where context_in is the context or keyword that indicates the topic of the help text. The default value for this context is the main help topic constant from PLVhlp: HELP.

You can call helptext directly; it is also called by the various program unit generators to include stubs for help text in generated code.

To create a stub of help text for the main topic, you would do the following:

SQL> exec PLVgen.helptext;
   /*HELP
   Add help text here...
   HELP*/

To create a stub of help text for a different topic, such as KNOWN PROBLEMS, you would execute the following command:

SQL> exec PLVgen.helptext ('known problems');
   /*KNOWN PROBLEMS
   Add help text here...
   KNOWN PROBLEMS*/

You can then cut and paste these stubs anywhere in your program definition, add some meaningful help text, and CREATE OR REPLACE the program. Users of the program can then access this text through the PLVhlp.show procedure. To make it easier to get at this information, however, you will most likely generate and include a special help procedure in your program unit (if it is a package, anyway). You do this with the PLVgen.helpproc program.

16.2.5.2 Generating a help procedure

The helpproc procedure generates a program that provides help for a given program unit. The header for helpproc is:

PROCEDURE helpproc
   (prog_in IN VARCHAR2 := NULL, indent_in IN INTEGER := 0);

where prog_in is the name of the program and indent_in is an optional additional indentation. By default the generated code already has an indentation of three spaces. The following SQL*Plus session generates a program to show help text from the specification of the PLVio package.

SQL> exec PLVgen.helpproc ('s:PLVio');
   PROCEDURE help (context_in IN VARCHAR2 := NULL)
   IS
   BEGIN
      PLVhlp.show ('s:PLVio', context_in);
   END help;

Let's see how you would use helpproc to provide help to your users. Suppose you have built a package named emp_maint . You want to allow developers to ask for general help on the employee maintenance tasks available. So you first execute PLVgen.helptext to create a stub help block:

SQL> exec PLVgen.helptext;
   /*HELP
   Add help text here...
   HELP*/

You cut and paste this text into the specification of emp_maint and add some real text. Then you generate a help program as follows:

SQL> exec PLVgen.helpproc ('s:emp_maint');
   PROCEDURE help (context_in IN VARCHAR2 := NULL)
   IS
   BEGIN
      PLVhlp.show ('s:emp_maint', context_in);
   END help;

You then cut and paste this program into the body of the package. You must also cut only the header (first line) of the procedure and paste that into the specification of the package. Once the code is recompiled, a user of emp_maint can enter the following command to see the associated help text:

SQL> exec emp_maint.help

If, by the way, you used PLVgen.pkg to generate the first version of the emp_maint package, it would come with the help program and help text stubs already in place.

16.2.6.1 Cursor declaration examples

1. Generate a cursor declaration relying on all default parameter values.

   SQL> exec PLVgen.curdecl('emp');
      CURSOR emp_cur
      IS
         SELECT
           FROM
          WHERE
            AND
          ORDER BY ;
      emp_rec emp_cur%ROWTYPE;

2. Generate a cursor against the order table to select the order_id and ship_date . Do not generate a record declaration since I am going to use this cursor in a FOR loop.

   SQL> exec PLVgen.curdecl ('orders', 0, 'order', 'order_id, ship_date', FALSE);
      CURSOR orders_cur
      IS
         SELECT order_id, ship_date
           FROM order
          WHERE
            AND
          ORDER BY ;

   The backbone of an SQL SELECT statement generated by curdecl supplies all the main clauses. You could greatly expand upon the PLVgen package to produce all manner of SQL statements.