Advanced Oracle PL/SQL Programming with Packages

Advanced Oracle PL/SQL Programming with PackagesSearch this book
Previous: 16.1 Options for Best PracticesChapter 16
PLVgen: Generating PL/SQL Programs
Next: 16.3 Modifying PLVgen Behavior

16.2 Code Generated by PLVgen

With PLVgen, you can generate functions, procedures, packages, and variations on those. You have already seen examples of functions and procedures. The examples in this section demonstrate the other kinds of code generated from this package. You can also modify the contents of generated code by using the many different toggles.

16.2.1 Generating a Package

Don't get your hopes up too high. I can't generate a complete package for you. PLVgen can, on the other hand, generate a skeleton of the syntax required to build a package and also load it up with help text stubs and banners to break up the different elements of the package.

The header for the package generator is:

   PROCEDURE pkg (name_in IN VARCHAR2);

You provide the name of the package, the procedure produced, and the code for a package (specification and body). Consider the following two commands executed in SQL*Plus:

SQL> exec PLVgen.usemax
SQL> exec PLVgen.pkg ('PLVfile');

Notice that I call PLVgen.usemax before generating my code. PLVgen.usemax turns on all available toggles for code content, including the CREATE OR REPLACE syntax, line numbers, program header, and help text stubs. These individual toggles and usemax are explained later in the chapter. The code generated by these commands is shown below.

 2 /*
 3 || Program: PLVfile
 4 ||  Author: Steven Feuerstein
 5 ||    File: PLVfile.SQL
 6 || Created: May 30, 1996 13:34:59
 7 */
 8 /*HELP
 9 Add help text here...
10 HELP*/
13 Add help text here...
16 IS
17 /* Public Data Structures */
19 /* Public Programs */
21    PROCEDURE help (context_in IN VARCHAR2 := NULL);
23 END PLVfile;
24 /
27 IS
28 /* Private Data Structures */
30 /* Private Programs */
32 /* Public Programs */
34    PROCEDURE help (context_in IN VARCHAR2 := NULL)
35    IS
36    BEGIN
37 ('s:PLVfile', context_in);
38    END help;
39 END PLVfile;
40 /

As you can see, this generated package has all the syntax required to create package specifications and bodies (which is not, after all, very much syntax). It also contains a single procedure, help (lines 34 through 38), which relies on the PLVhlp package to provide online help about this package. It also creates stubs for the help text (lines 8 through 14), to remind you to add the text and make that information available (see Chapter 17, PLVhlp: Online Help for PL/SQL Programs, for more information on how PLVhlp works).

Finally, PLVgen.pkg provides banners in comments to delineate the different kinds of code one normally finds inside a package. These banners (lines 17, 19, 28, 30, and 32) help developers organize their code. This organization will, in turn, make it easier to develop, debug, and enhance the package.

16.2.2 Generating a Procedure

Once you have generated a package, you will most likely want to fill it up with procedures and functions. PLVgen provides the proc procedure to generate (as you might expect) procedures. Its header is:

   (name_in IN VARCHAR2,
    params_in IN VARCHAR2 := NULL, 
    exec_in IN VARCHAR2 := NULL,
    incl_exc_in IN BOOLEAN := TRUE,
    indent_in IN INTEGER := 0);

The arguments of the proc procedure are explained below:


The name of the procedure. The only required argument.


The list of parameters to be enclosed in parentheses after the procedure name. The default is NULL (no parameters). The syntax of this argument must be a valid parameter list as would appear in the procedure definition (minus the parentheses).


One or more executable statements to place in the body of the procedure. If you want to provide more than one statement, you should concatenate a CHR(10) or PLVchr.newline_char between the statements to place them on separate lines. The default is NULL (no executable statements).


Pass TRUE (the default) to include an exception section and initial handler, FALSE to skip the exception section. Why give the user a choice? Sometimes an exception handler is just plain unnecessary and little more than clutter.


The incremental indentation by which the entire procedure's code should be indented. The default is 0. 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');
    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*/
   13    /*EXAMPLES
   14    Add help text here...
   15    EXAMPLES*/
   17    IS
   18    BEGIN
   19       PLVtrc.startup ('calc_totals');
   20       PLVtrc.terminate;
   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. 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)

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

         WHEN OTHERS
      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 Generating a Function

PLVgen offers seven overloadings of the function generator. Why? Because a function has a RETURN datatype and I want my package to automatically generate functions of the requested type. PLVgen.func generates functions with the following datatypes: BOOLEAN, DATE, NUMBER, and VARCHAR2. Four datatypes and seven I leaving something out? Not at all; PLVgen supports four different datatype functions, but provides additional overloadings to handle two kinds of default values for the functions (covered later in this section).

The format of the function generated by PLVgen.func follows my guidelines for a "template" function, the generic structure of which is shown below:

FUNCTION func_name (...) RETURN datatype 
   /* Variable for RETURN */
   return_value datatype;
   <executable statements>
   /* Last line always: */
   RETURN return_value;
END func_name;

A brief recap of the function template: There is a single RETURN statement in the body of the program and it is the last line of code. There is a local variable (in this case, return_value) that is always the same datatype as the function itself and is RETURNed in that single, successful RETURN statement. There is an exception section that returns NULL if something goes wrong.

Rather than show the headers for all of the overloadings here and then again in the package specification section, I will show you the header for the string function generator and the headers for the Boolean function generators. All the other nonstring function generators are structured and used like the Boolean version. Function generator headers

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

   (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:

   (name_in IN VARCHAR2,
    datadesc_in BOOLEAN, 
    defval_in IN BOOLEAN := NULL,
    incl_exc_in IN BOOLEAN := TRUE);

   (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. 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');
      retval VARCHAR2(100) := NULL;
      RETURN retval;

         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);
      retval BOOLEAN := FALSE;
      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');
      retval BOOLEAN := sysdate < account.start_date;
      RETURN retval;

         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. 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);
      retval VARCHAR2(50) := 'ABC';

      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);
      retval VARCHAR2(50) := abc;

      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');
      retval VARCHAR2(100) := names.formal_address;
      RETURN retval;

         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 Generating Get-and-Set Routines

One of the most important of my best practices for package construction is to always hide package data behind a programmatic interface, otherwise known as get-and-set routines. Instead of declaring a variable directly in the specification, you would move that declaration to the body of the package and then build (a minimum of) two programs: a function to retrieve the current value of this variable and a procedure to change the value of the variable.

The gas (get-and-set) procedure of PLVgen generates the get-and-set code needed to hide variables of datatypes VARCHAR2, DATE, NUMBER, and Boolean. The overloading for the gas procedure is similar to that of the func procedure, which makes sense since PLVgen.gas does generate a function, as well as a variable declaration of the correct datatype. There are two versions of gas for every datatype except VARCHAR2. For this string datatype, there is only one overloading. The header for the string version of gas is shown below:

   (name_in IN VARCHAR2,
    valtype_in VARCHAR2, 
    defval_in IN VARCHAR2 := NULL,
    length_in IN INTEGER := c_def_length);

The header for the NUMBER version of gas is shown below. The headers for the other datatypes are exactly the same as for the NUMBER version, except for the datatype of the valtype_in and defval_in arguments.

   (name_in IN VARCHAR2,
    valtype_in NUMBER, 
    defval_in IN NUMBER := NULL);

   (name_in IN VARCHAR2,
    valtype_in NUMBER, 
    defval_in IN VARCHAR2);

Generally, the first argument provides the name of the variable. The second argument determines the type of the function and the variable being hidden inside the package body. The third argument provides a default value for the variable. When generating a string get-and-set, you can also provide 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 listing below gives you an idea of the kind of code that is generated by a call to the PLVgen.gas procedure. I call the PLVgen.useln to turn on line numbers to use as a reference (after turning off all other toggles).

SQL> exec PLVgen.usemin
SQL> exec PLVgen.useln
SQL> exec PLVgen.gas ('pagesize', 1, 25);
    2    PROCEDURE set_pagesize (pagesize_in IN NUMBER);
    3    FUNCTION pagesize RETURN NUMBER;
    5    v_pagesize NUMBER := 25;
    7    PROCEDURE set_pagesize (pagesize_in IN NUMBER)
    8    IS
    9    BEGIN
   10       v_pagesize := pagesize_in;
   11    END set_pagesize;
   13    FUNCTION pagesize RETURN NUMBER
   14    IS
   15       retval NUMBER := v_pagesize;
   16    BEGIN
   17       RETURN retval;
   18    END pagesize;

Lines 2 and 3 contain the headers for the set and get programs. These should be cut and pasted into the package specification. Line 5 contains the declaration of the variable that is to be protected by the get-and-set programs. This declaration is placed in the body of the package, before any of the program definitions are listed. Lines 7 through 11 contain the definition of the set program. Lines 13 through 18 contain the definition of the get program. Both of these should be cut and pasted into the package body. Notice that even in the simple get function, the template approach is still followed. 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
      v_show_changes := TRUE;
   END show_changes;

   PROCEDURE noshow_changes
      v_show_changes := FALSE;
   END no_show_changes;

   FUNCTION show_changesing RETURN BOOLEAN
      retval BOOLEAN := v_show_changes;
      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:


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


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

   v_onoff BOOLEAN := TRUE;

   PROCEDURE turn_on
      v_onoff := TRUE;
   END turn_on;

   PROCEDURE turn_off
      v_onoff := FALSE;
   END turn_off;

      retval BOOLEAN := v_onoff;
      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 Generating Help Stubs

PL/Vision provides a mechanism for delivering online help for your PL/SQL programs (see Chapter 17). To take advantage of this mechanism you need to (a) put comments with an appropriate format in your source code, and (b) provide an easy way for developers to ask for that help. PLVgen allows you to generate the code and comments to handle both these tasks. 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;
   Add help text here...

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');
   Add help text here...

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 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. 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)
   BEGIN ('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;
   Add help text here...

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)
   BEGIN ('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

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 Generating a Cursor Declaration

Many of us have gotten pretty good about using consistent indentation and white space for our PL/SQL code. Yet when we come to writing a SQL statement, we abandon any pretense of formatting and disgorge an absolutely awful mish-mash of SQL text into our otherwise orderly procedural logic. I believe that a consistent, readable format for the SQL in our PL/SQL programs is even more important than a good format for the procedural part of the programs. To help you satisfy my whim, PLVgen provides the curdecl procedure, which generates the declaration statement for a cursor.

The header for curdecl is as follows:

PROCEDURE curdecl 
   (cur_in IN VARCHAR2,
    ind_in IN INTEGER := 0,
    table_in IN VARCHAR2 := NULL,
    collist_in IN VARCHAR2 := NULL,
    gen_rec_in IN BOOLEAN := TRUE);

where cur_in is the name of the cursor, ind_in is an additional amount of indentation, table_in is the name of the table (or list of tables), and collist_in is the list of columns in the SELECT list of the SQL statement. The gen_rec_in argument specifies whether or not you want a record declaration to be generated with the cursor.

This procedure implements several best practices and naming conventions for cursors: it automatically appends "_cur" to the end of the cursor name and "_rec" to the end of the record name. It formats the SQL statement to make it easier to read. It provides a record to go with the cursor as a discouragement against declaring individual variables into which data is then fetched.

The more you take advantage of these different arguments, the more likely it is that you will generate a cursor declaration that is very close to being ready for execution. Let's look at some examples. Cursor declaration examples

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

    SQL> exec PLVgen.curdecl('emp');
       CURSOR emp_cur
           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
          SELECT order_id, ship_date
            FROM order
           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.

16.2.7 Generating a "Record Found?" Function

The curdecl procedure generates a cursor declaration, which is certainly useful. Yet there are also many common kinds of code fragments and programs that use cursors in specific ways. One example is the "record found?" function. How many times have you written a function to return TRUE if the desired record exists, and FALSE otherwise?

PLVgen offers the recfnd procedure to generate a version of this "record found?" function that will save you many keystrokes if and when you need this functionality. The header for recfnd is, quite simply:

PROCEDURE recfnd (table_in IN VARCHAR2);

In other words, you provide the name of the table whose contents you wish to check, and recfnd does the rest of the work from there.

The following session generates a function that returns TRUE if the employee is found, and FALSE otherwise.

SQL> exec PLVgen.recfnd ('emp');
    1    FUNCTION empexists (empkey_in IN NUMBER) RETURN BOOLEAN
    2    IS
    3       CURSOR emp_cur
    4       IS
    5          SELECT 1
    6            FROM emp
    7           WHERE
    8             AND
    9           ORDER BY ;
   10       emp_rec emp_cur%ROWTYPE;
   11       retval BOOLEAN := FALSE;
   12    BEGIN
   13       OPEN emp_cur;
   14       FETCH emp_cur INTO emp_rec;
   15       retval := emp_cur%FOUND;
   16       CLOSE emp_cur;
   17       RETURN retval;
   19    EXCEPTION
   20       WHEN OTHERS
   21       THEN
   22          RETURN NULL;
   23    END empexists;

Notice that the procedure creates appropriate names for the function, cursor, and record from the table name -- and places that table name into the cursor's query. It follows the template approach for functions, so that you have an exception handler to return a NULL in case of problems and a single RETURN in the body of the function. Sure, you have to change the SELECT statement and the key might not be a NUMBER. Regardless, with a simple cut and paste operation, you have more than twenty lines of consistent, readable code.

16.2.8 Generating a Cursor FOR Loop

Another common programming construct for PL/SQL developers is the cursor FOR loop, so it will come as no surprise that PLVgen offers a procedure to generate this loop. The header for the cfloop procedure is:

PROCEDURE cfloop (table_in IN VARCHAR2);

You provide the name of the cursor and cfloop does the rest. The following SQL*Plus session demonstrates the kind of code generated, in this case for a cursor FOR loop based on the dept table.

SQL> exec PLVgen.cfloop('dept');
   CURSOR dept_cur
      SELECT *
        FROM dept
       ORDER BY ;

   << dept_cur >>
   FOR dept_rec IN dept_cur

   END LOOP dept_cur;

The cfloop procedure does several convenient things for us: it defaults the SELECT list to * or all columns. It creates a loop label, which is also applied to the END LOOP statement to improve readability. It avoids declaring the record used by the FOR loop -- an unnecessary and potentially hazardous step often taken by poorly informed PL/SQL programmers.

You cut and paste the cursor declaration into the declaration section; move the FOR loop itself into the body of the program; put some executable code in the body of the loop; and off you go.

16.2.9 Generating a Timer Script

The PLVtmr package offers an easy-to-use mechanism for timing PL/SQL code execution down to a hundredth of a second. I found that I often used this package to time the performance of the same line or lines of code executed more than once -- in other words, inside a loop. Rather than write these loops again and again, I added a procedure to PLVgen to generate this code for me.

The timer procedure's header is as follows:

PROCEDURE timer (plsql_in IN VARCHAR2);

You provide the line of PL/SQL code you want to execute and PLVgen provides a SQL*Plus script as shown below:

SQL> exec PLVgen.timer ('calc_totals;');
      PLVtmr.set_factor (&1);
      FOR rep IN 1 .. &1
      END LOOP;
      PLVtmr.show_elapsed ('MSG');

These lines of code should be placed in a file with the .sql extension. Change the text in the call to show_elapsed, add other lines of code to the body of the loop if desired. Upon execution, you provide the number of iterations (or, in PLVtmr terms, the factor), and the script executes (in this case) the calc_totals procedure that number of times. PLVtmr.show_elapsed then displays the total elapsed time and the per-execution time as well.

Previous: 16.1 Options for Best PracticesAdvanced Oracle PL/SQL Programming with PackagesNext: 16.3 Modifying PLVgen Behavior
16.1 Options for Best PracticesBook Index16.3 Modifying PLVgen Behavior

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