18. PLVcase and PLVcat: Converting and Analyzing PL/SQL Code

Contents:
PLVcase: Converting the Case of PL/SQL Programs
PLVcat: Cataloguing PL/SQL Source Code

PL/Vision provides two packages to convert and analyze PL/SQL source code: PLVcase and PLVcat. The PLVcase (PL/Vision CASE) package converts the case of PL/SQL source code so that it conforms to the UPPER-lower method (reserved words in uppercase, application-specific identifiers in lowercase). The PLVcat (PL/Vision CATalogue) package catalogues PL/SQL source code so that you can analyze the contents of your program for cross-references, dependencies, and so on. These two packages are explored in this chapter.

18.1 PLVcase: Converting the Case of PL/SQL Programs

The PLVcase conversion package converts the case of PL/SQL source code according to the UPPER-lower method: all reserved words are converted to upper-case, while all application-specific identifiers are converted to lowercase. The consistency of case in a program -- and, in particular, the UPPER-lower standard -- aids greatly in the ability of developers to read, understand, and maintain their (and others') code.

Consider the following package body definition:

create or replace package body testcase
is
   procedure save (string_in in varchar2)
   is
      n integer := dbms_sql.open_cursor;
   begin
      update PLV_output set program = string_in;
      if sql%rowcount = 0
      then
         insert into PLV_output values (string_in);
      end if;
      PLVcmt.perform_commit;
   end;
end testcase;
/

This program is consistently indented to reveal the logical flow of the save procedure defined in the testcase package. Yet it is still difficult to read. With all the text in lowercase, the syntax and logic blurs into an indistinguishable stream of characters.

Here is the same package body after being passed through PLVcase:

PACKAGE BODY testcase
IS
   PROCEDURE save (string_in IN VARCHAR2)
   IS
      n INTEGER := DBMS_SQL.OPEN_CURSOR;
   BEGIN
      UPDATE PLV_output SET program = string_in;
      IF SQL%ROWCOUNT = 0
      THEN
         INSERT INTO PLV_output VALUES (string_in);
      END IF;
      PLVcmt.perform_commit;
   END;
END testcase;

PLVcase is a general-purpose engine for case conversion. It relies on many different packages in PL/Vision to achieve a high degree of flexibility. For example, PLVcase can read and convert PL/SQL source code from the data dictionary, an operating system file (with PL/SQL Release 2.3), and even individual string variables. You can redirect PLVcase for both read and write by making the appropriate calls to the PLVio package.

PLVcase also relies heavily on the PLVtkn package. After all, if PLVcase is going to uppercase only keywords, it has to know which identifiers in a PL/SQL program are reserved words. This information is maintained in the PLV_token table, which in turn is made available through PLVtkn (see Chapter 10, PLVprs, PLVtkn, and PLVprsps: Parsing Strings).

The body of the PLVcase package is actually rather simple considering the complexity of its task. The following sections show how to use each of the different elements of the PLVcase package.

18.1.1 The Various Conversion Procedures

PLVcase offers a sequence of procedures to convert increasingly complex text. The token procedure converts a single token.

line: Converts the text found with a record of the PLVio.line_type structure.
string: Converts the text in a simple string.
module: Converts all the lines of code in the specified module.
modules: Converts multiple programs in a single pass.

As you would expect, line uses token, string uses line, module uses line, and modules uses module.

It is easy to use the conversion programs. The more complicated aspect of PLVcase arises in determining the source of the program (data dictionary view, file, string, etc.) and the target for the converted code. This is discussed later in the chapter.

18.1.1.1 Converting a single token

The token function can take two arguments as shown in the header:

FUNCTION token (token_in IN VARCHAR2, pkg_in IN VARCHAR2 := NULL)
RETURN VARCHAR2

The second argument is an optional name of a package. If pkg_in is provided, that package name is prefixed onto the token value and then that string is case-converted.

Here is an example of using PLVcase.token to convert a single identifier:

v_newtoken := PLVcase.token (v_oldtoken);

In this second example, I request conversion of a program from the DBMS_SQL package:

v_newtoken := PLVcase.token ('open_cursor', 'dbms_sql');

In this case, v_newstring is set to OPEN_CURSOR. In other words, the package name is not prefixed onto the token name.

18.1.1.2 Converting a string

To convert the case of all tokens in a string, use one of the PLVcase.string program units. The string program is overloaded as follows:

PROCEDURE string (string_inout IN OUT VARCHAR2);
FUNCTION string (string_in IN VARCHAR2) RETURN VARCHAR2;

I provide these two versions of string to support different applications of this functionality. In some cases you just want to pass a string to the PLVcase package for conversion. In this scenario, use the string procedure as follows:

PLVcase.string (v_header);

Under other circumstances, you may want to preserve the original string value (it might, for example, be an IN parameter), as well as generating a case-converted version of the string. In this scenario, use the string function as follows:

v_newstring := PLVcase.string (original_in);

Another use of PLVcase is illustrated by the PLVgen package. This package uses PLVcase to apply the proper case to any symbolic default values provided by the user. For example, I can call PLVgen.func to generate a function with a default return value that would have mixed cases as follows:

SQL> exec PLVgen.func ('tot_sales', 1, 'sales_to_date (sysdate)');

The function name, sales_to_date, should be in lowercase, while the argument to that function, SYSDATE, should be in uppercase. The construction of the initial return value inside the PLVgen package uses the PLVcase.string function to accomplish this effect:

retval := 
   v_name || ' ' || v_datatype || ' := ' ||
   PLVcase.string (v_defval) || ';';

18.1.1.3 Converting a line

PLVcase is "PLVio-aware." It allows you to convert the text contained in a line record defined in the PLVio package. The header for the line procedure is:

PROCEDURE line
   (line_in IN OUT PLVio.line_type,
    line_out IN OUT PLVio.line_type,
    found_out OUT BOOLEAN);

Notice that the original line record is left unchanged. Instead, the modified line text is deposited in an OUT record of the same line type. The found_out parameter is set to TRUE if at least one token is found in the line's text.

The PLVcase.line procedure makes use of PLVlex.get_next_token to parse out the next token in the line's text and then convert the case using PLVtkn. The PLVcase.string function, in turn, calls PLVcase.line. Finally, the PLVcase.string procedure calls the PLVcase.string function.

To give you a sense of the layering involved, here is the body of the PLVcase.string function:

FUNCTION string (string_in IN VARCHAR2)
   RETURN VARCHAR2
IS
   line1 PLVio.line_type;
   line2 PLVio.line_type;
   code_found BOOLEAN := FALSE;
BEGIN
   first_token := TRUE;
   PLVio.initline (line1, string_in, LENGTH (string_in), 1);
   PLVio.initline (line2);   
   line (line1, line2, code_found);
   IF code_found 
   THEN
      RETURN line2.text;
   ELSE
      RETURN NULL;
   END IF;
END;

18.1.1.4 Converting a module

The module procedure allows you to convert all the lines of code in a specified program unit with one procedure call. The header for module is as follows:

PROCEDURE module
   (module_in IN VARCHAR2, 
    cor_in IN VARCHAR2 := c_usecor,
    last_module_in IN BOOLEAN := TRUE);

The three arguments of module are explained below:

module_in: The name of the module. You can pass in an nonqualified name, such as total_sales. You can also pass in a string in the format type:name. Valid values for type are listed in the next section.
cor_in: Use this argument to pass in the constant PLVcase.c_usecor if you want PLVcase to automatically attach CREATE OR REPLACE syntax in the converted source code. You do this if you are converting source code that will be recompiled back into the database using SQL*Plus.
last_module_in: Pass in TRUE if this is the last module to be converted. The default is TRUE, since module is intended to convert a single program unit at a time. When last_module_in is TRUE, the PLVio-directed source is closed and all changes are saved. This argument is needed to allow PLVcase.modules to convert multiple program units.

The PLVcase package uses the PLVobj interface to the ALL_OBJECTS data dictionary view to identify the program or programs indicated by the user. PLVobj is flexible in interpreting your input. The following variations are allowed: name, schema.name, type:name, and type:schema.name.

PLVcase.module relies on the PLVio to determine the location or target repository for the converted source code. If you have previously executed this statement:

PLVio.settrg (PLV.pstab);

then the converted code will be placed in PLVio.target_table. If, on the other hand, you execute this statement:

PLVio.settrg (PLV.stdout);

then you see the converted code scrolled onto your screen when the conversion process is complete.

You can also convert the case of more than one module at a time by calling the modules procedure, whose header is:

PROCEDURE modules (module_spec_in IN VARCHAR2 := NULL);

The following examples using Plvcase.module and PLVcase.modules give you a sense of how you can apply this functionality in your own environment.

Convert a stored function named total_sales without CREATE OR REPLACE syntax as follows:
```
PLVcase.module ('f:total_sales', PLVcase.c_nousecor);
```
Convert the case of (a) a single function and (b) all the package specifications in the current schema:
```
SQL> exec PLVcase.module ('total_sales');
SQL> exec PLVcase.modules ('s:%');
```

In the call to PLVcase.modules I specify "s" or "specification" for the module type and "%" or "any and all" for the names of the modules.

18.1.2 A Script to Convert Programs

Most of the time when you use PLVcase on your source code, you will not simply execute a single call to a PLVcase procedure. PLVcase is too tightly integrated into and dependent on other packages in PL/Vision. For example, you first need to initialize the PLVio package so that PLVcase can find the original source code and properly write out the converted code.

I wrote the setcase.sql SQL*Plus script, shown in Example 18.1, to make it easier for developers to use PLVcase properly. This program prompts you for the module or modules you wish to convert. It then assumes that you want to read the source code from the ALL_SOURCE data dictionary -- and calls PLVio.asrc to "make it so." It also assumes that you want the converted source code simply CREATEd OR REPLACEd back into the database. It accomplishes this with the following call:

PLVio.settrg (PLV.dbtab);

This setting means that the converted code will be written to the PLV_source table. The setcase script then uses a SELECT on this table to write the new version of the source code to a command file called setcase.cmd. As a final step, setcase.sql executes the setcase.cmd file in SQL*Plus and the code is reinserted into the database.

Example 18.1: The setcase Script

SET ServerOutput ON
SET FEEDBACK OFF
SET VERIFY OFF
DELETE FROM PLV_source;
DECLARE
   modname VARCHAR2(100) := UPPER ('&1');
   modname2 VARCHAR2(100);
   modtype VARCHAR2(100);
   modschema VARCHAR2(100);
   modstring VARCHAR2(100);
   delim_loc INTEGER;
BEGIN
   IF INSTR (modname, '%') > 0 OR INSTR (modname, ':') = 0
   THEN
      /* Doing >1 module. */
      p.l ('=========================');
      p.l ('PL/Vision Case Conversion');
      p.l ('=========================');
      p.l ('Converting ' || modname || '...');
      PLVio.asrc;
      PLVio.settrg (PLV.dbtab);
      PLVcase.modules (modname);
   ELSE
      modname2 := modname;
      PLVobj.convobj (modname, modtype, modschema);
      modstring := modtype || ' ' || modname;
      p.l ('=========================');
      p.l ('PL/Vision Case Conversion');
      p.l ('=========================');
      p.l ('Converting ' || modstring || '..');
      PLVio.asrc;
      PLVio.settrg (PLV.dbtab);
      PLVcase.module (modname2);
   END IF;
END;
/
prompt Generating program creation script...
set pagesize 0
set linesize 120
set termout off
column text format a120
spool setcase.cmd
SELECT text
  FROM PLV_source
 ORDER BY 
DECODE (type, 
        'PACKAGE',   1, 'PACKAGE BODY', 2,
        'PROCEDURE', 3, 'FUNCTION',     4, 
        5),
   name, line
/
spool off
set pagesize 25
set termout on
set feedback on
start setcase.cmd

See the sections on the PLVobj and PLVio packages for more information about how you can alter the source and target repositories for the source code converted by PLVcase. You could, for instance, convert program units stored in Oracle Forms tables in the database.


17.5 Implementing PLVhlp		18.2 PLVcat: Cataloguing PL/SQL Source Code