PLVgen: PL/SQL Code Generator

The PLVgen (PL/Vision GENerator) package provides a set of procedure you can use to generate your own PL/SQL code. See PLVgen: Generating PL/SQL Programs for details.

Package constants

• c_indent CONSTANT INTEGER := 0;
• The default initial indentation of generated code.
• c_incr_indent CONSTANT INTEGER := 3;
• The default incremental indentation of generated code.
• c_literal CONSTANT CHAR(1) := '=";
• the character used to indicate that the default value for the string function is not to be evaluated before placing in the function definition.
• c_def_length constant integer := 100;
• the default length for a string function"s local variable.
• c_none CONSTANT VARCHAR2(1) := 'N';
• Indicates that no blank lines are to be placed before or after the current line of code.
• c_before CONSTANT VARCHAR2(1) := 'B';
• Indicates that a blank line is to be placed before the current line of code.
• c_after CONSTANT VARCHAR2(1) := 'A';
• Indicates that a blank line is to be placed after the current line of code.
• c_both CONSTANT VARCHAR2(2) := 'BA';
• Indicates that a blank line is to be placed both before and after the current line of code.

Setting the indentation

• PROCEDURE set_indent
• (indent_in IN NUMBER,
• incr_indent_in IN NUMBER := c_incr_indent);
• Sets the initial and incremental indentation.
• FUNCTION indent RETURN NUMBER;
• Returns the current value for initial indentation.
• FUNCTION incr_indent RETURN NUMBER;
• Returns the current value for incremental indentation.

Setting the author

• PROCEDURE set_author (author_in IN VARCHAR2);
• Assigns a value for the author string used in program headers.
• FUNCTION author RETURN VARCHAR2;
• Returns the current author string.

5.13.4 Toggles affecting generated code

PLVgen offers a large selection of toggles or on-off switches, which you can use to modify the content of code generated by this package. Each toggle has a "turn on" procedure, a "turn off" procedure, and a function returning the current state of the toggle (on or off).

• PROCEDURE usetrc;
• PROCEDURE nousetrc;
• FUNCTION using_trc RETURN BOOLEAN;
• Controls inclusion of the PLVtrc startup and terminate procedures.
• PROCEDURE useexc;
• PROCEDURE nouseexc;
• FUNCTION using_exc RETURN BOOLEAN;
• Controls inclusion of PLVexc exception handlers in exception sections of programs.
• PROCEDURE usehdr;
• PROCEDURE nousehdr;
• FUNCTION using_hdr RETURN BOOLEAN;
• Controls inclusion of program headers in packages, procedures, and functions.
• PROCEDURE usecmnt;
• PROCEDURE nousecmnt;
• FUNCTION using_cmnt RETURN BOOLEAN;
• Controls inclusion of comment lines in generated code.
• PROCEDURE usehlp;
• PROCEDURE nousehlp;
• FUNCTION using_hlp RETURN BOOLEAN;
• Controls inclusion of help text stubs and generation of the help procedure in packages.
• PROCEDURE usecor;
• PROCEDURE nousecor;
• FUNCTION using_cor RETURN BOOLEAN;
• Controls inclusion of code required to CREATE OR REPLACE program units.
• PROCEDURE useln;
• PROCEDURE nouseln;
• FUNCTION usingln RETURN BOOLEAN;
• Controls inclusion of line numbers in prefix of generated code.
• PROCEDURE usemin;
• Turns off all the above toggles.
• PROCEDURE usemax;
• Turns on all the above toggles.

Help generators

• PROCEDURE helpproc
• (prog_in IN VARCHAR2 := NULL, indent_in IN INTEGER := 0);
• Generates a procedure that gives main-topic help for the specified program unit.
• PROCEDURE helptext (context_in IN VARCHAR2 := PLVhlp.c_main);
• Generates a comment block in the correct format to be used as online help text with the PLVhlp package.

Generating a package

• PROCEDURE pkg (name_in IN VARCHAR2);
• Generates the skeleton structure for a package's specification and body.

Generating a procedure

• PROCEDURE proc
• (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,
• blank_lines_in IN VARCHAR2 := c_before);
• Generates a procedure of the specified name. You can also provide a parameter list and one or more executable lines. Finally, you can decide to include an exception section, indent the code, and perform blank-line processing.

5.13.8 Generating functions

A function has a RETURN datatype. PLVgen allows you to generate string, numeric, date, and Boolean functions. You can also supply literal and symbol default values. As a result, the func procedure is overloaded as shown:

• 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);
• Generates a string function (since the datatype for the datdesc_in parameter is VARCHAR2).
• PROCEDURE func
• (name_in IN VARCHAR2,
• datadesc_in datatype,
• defval_in IN datatype := NULL,
• incl_exc_in IN BOOLEAN := TRUE);
• Generates a function of the specified datatype, which is either NUMBER, DATE, or BOOLEAN. Notice that the default has the same datatype as the datadesc_in parameter. This is a default value that is evaluated as a literal.
• PROCEDURE func
• (name_in IN VARCHAR2,
• datadesc_in datatype,
• defval_in IN VARCHAR2,
• incl_exc_in IN BOOLEAN := TRUE);
• Generates a function of the specified datatype, which is either NUMBER, DATE, or BOOLEAN. Notice that the default in this version is a string. When you use this format, the default value is treated as an expression that is not evaluated.

Generating get-and-set routines

Get-and-set routines provide a programmatic layer of code around a private data structure. As a result, the get-and-sets or "gas" generators have associated with them a datatype. PLVgen allows you to generate string, numeric, date, and Boolean get-and-sets. You can also supply literal and symbol default values. As a result, the gas procedure is overloaded with the following flavors:

• PROCEDURE gas
• (name_in IN VARCHAR2,
• valtype_in VARCHAR2,
• defval_in IN VARCHAR2 := NULL,
• length_in IN INTEGER := c_def_length);
• Generates a string function (since the datatype for the datdesc_in parameter is VARCHAR2).
• PROCEDURE gas
• (name_in IN VARCHAR2,
• valtype_in datatype,
• defval_in IN datatype := NULL);
• Generates get-and-sets of the specified datatype, which is either NUMBER, DATE, or BOOLEAN. Notice that the default has the same datatype as the datadesc_in parameter. This is a default value that is evaluated as a literal.
• PROCEDURE gas
• (name_in IN VARCHAR2, valtype_in datatype,
• defval_in IN VARCHAR2);
• Generates get-and-sets of the specified datatype, which is either NUMBER, DATE, or BOOLEAN. Notice that the default in this version is a string. When you use this format, the default value is an expression that is not evaluated.
• PROCEDURE toggle (name_in IN VARCHAR2 := NULL);
• Generates a variation of get-and-set based on a Boolean toggle. If you do not give a name, turn_on and turn_off are used as the on-off procedure names.

Miscellaneous code generators

• 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);
• Generates a cursor declaration with the SQL statement formatted for maximum readability.
• PROCEDURE cfloop (table_in IN VARCHAR2);
• Generates a cursor FOR loop and framework for a cursor declaration.
• PROCEDURE recfnd (table_in IN VARCHAR2);
• Generates a function that returns TRUE if a record is found, FALSE otherwise.
• PROCEDURE timer (plsql_in IN VARCHAR2);
• Generates a function that returns TRUE if a record is found, FALSE otherwise.