#

| Chapter 18 |

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

1. Convert a stored function named `total_sales` without CREATE OR REPLACE syntax as follows:

   >     PLVcase.module ('f:total_sales', PLVcase.c_nousecor);

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

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