#

| Chapter 18 PLVcase and PLVcat: Converting and Analyzing PL/SQL Code |

## 18.2 PLVcat: Cataloguing PL/SQL Source Code

The PLVcat package gives you a way to parse and store (in a table-based catalogue) information about the contents of PL/SQL package specifications. You will glean information from this catalogue that is currently unavailable from the Oracle Server data dictionary.

Before describing the different areas of functionality of PLVcat, let's look at the problem PLVcat is intended to solve.

### 18.2.1 Knowing What You've Got

One of the tremendous advantages of storing PL/SQL programs in the database is that they can be executed by anyone granted authority to those objects. You don't have to know where the source code resides on disk; link lists and paths will not badger you in the world of PL/SQL. Yet you do face other challenges when seeking to leverage stored code -- especially on an enterprise-wide basis. These obstacles include:

- Knowing what is available. How do you know what programs are stored where, what they are supposed to do, and how you are supposed to use them?

- Knowing where and how programs are being used. How do you measure the impact of changing a particular program? It is often very useful to be able to answer questions like which programs use this function? How, then, is the function used?

At the time I'm writing this book, I think that it is fair to say that the potential and functionality of PL/SQL have outstripped the features of development environments using PL/SQL. Developers using PL/SQL often work within a light fog, stumbling about for information on the programs they can use. As a result, code reuse remains a lofty objective, an agreed-upon principle rarely put into practice.

Oracle provides some ability to get answers to your questions about stored PL/SQL It provides a set of data dictionary views that you can access using the SQL language. These views include:

USER_SOURCE

  The source code of your stored programs. (There is also an ALL_SOURCE view, which contains the source code of all programs to which you have access.)

USER_DEPENDENCIES

  Information about dependencies between all kinds of objects stored in the database.

Having this kind of information in views is a wonderful feature of an active data dictionary. But you have to ask yourself two questions: how easy is it to get the data and how good is the data? Sure, you can use SQL to retrieve the information from the views, but that can get very time-consuming and cumbersome. Suppose you want to see a list of all of the programs defined in a package specification. You could view that specification using the `PLVvu.code` procedure. That can at times be an overwhelming volume of information -- and it doesn't lend itself easily to generating a report showing this information in an outline view.

In the case of USER_DEPENDENCIES, the problem is not access to data; the problem is with a lack of data. This view shows you which package another object relies on, but it does not "pierce the veil" of the package to show you which element inside that package is the cause of the dependency. In other words, if my `calc_totals` program makes the following call:

>     recalc.full_analysis;

then the USER_DEPENDENCIES view shows that `calc_totals` is dependent on `recalc`. It will not, however, inform you that `full_analysis` is the program of the `recalc` package that is called by `calc_totals`.

PL/Vision fixes these shortcomings with a set of programs that parses the contents of your PL/SQL code and then stores the results of that process in database tables. You can then write simple SQL statements against these tables to generate reports that provide a much greater granularity of detail about your PL/SQL programs.

These elements of the PLVcat package are explained in later sections.

### 18.2.2 The PLVcat Database Tables

The PLVcat programs generate information about your PL/SQL code and then deposit that information in one of two database tables: `PLVctlg` or `PLVrfrnc`. These tables are created when you install PL/Vision. The `PLVctlg` table contains the catalogue of the contents of packages (those elements defined in the specification). The `PLVrfrnc` table contains the references or dependencies generated by calls to the `ref` programs. These tables and how to interpret their contents are explained below.

#### 18.2.2.1 PLVctlg table

The structure of the `PLVctlg` table is:

>     CREATE TABLE PLVctlg
>        (owner VARCHAR2(100),
>         name1 VARCHAR2(100), /* Package name */
>         name2 VARCHAR2(100), /* Element name */
>         type VARCHAR2(100),  /* Same as in user_objects */
>         iname VARCHAR2(100), /* Name of object inside */
>         itype VARCHAR2(100), /* Type of object inside */
>         idatatype VARCHAR2(100),
>         overload INTEGER)

The `owner`, `name1`, `name2`, and `type` columns define the program unit for which elements have been catalogued. The `name2` column is always NULL in this version of PLVcat, since PL/Vision currently catalogues only package specifications.

The "inside" columns (`iname`, `itype`, and idatatype) indicate the element found in the program unit. The `idatatype` column is non-NULL if the element is a function or TYPE statement. The `overload` column contains the number of overloadings of a particular procedure or function name. All values are stored in uppercase.

Examples of how this table is filled from calling PLVcat modules are shown later in this chapter.

#### 18.2.2.2 PLVrfrnc table

The `PLVrfrnc` table contains information about the references made to external elements from within a PL/SQL program unit. The structure of this table is:

>     CREATE TABLE PLVrfrnc
>        (owner VARCHAR2(100),
>         name1 VARCHAR2(100),
>         name2 VARCHAR2(100),
>         type VARCHAR2(100),
>         reftype VARCHAR2 (100), /* Type of reference */
>         rowner VARCHAR2(100), /* Leave null if not specified */
>         rname1 VARCHAR2(100), /* Package name or stand alone */
>         rname2 VARCHAR2(100)  /* Null if not in package. */
>        )

The `owner`, `name1`, `name2`, and `type` columns define the program unit for which references have been analyzed. The `reftype`, `rowner`, `rname1`, and `rname2` columns define the object that is referenced inside the program unit. All values are stored in uppercase.

Examples of how this table is filled from calling PLVcat modules are shown later in this chapter.

### 18.2.3 Building a Catalogue

You can build a catalogue of your PL/SQL source code with the `module` and `modules` procedures. The `module` procedure catalogues a single program unit, while the `modules` procedure can handle wildcarded program names and automatically catalogue multiple program units, including all the stored code in a schema.

#### 18.2.3.1 Cataloguing a single module

To build a catalogue of a single PL/SQL program, you call the `module` procedure, whose header is:

>     PROCEDURE module (module_in IN VARCHAR2);

You provide the name of the program you want to catalogue (currently only package specifications are supported; any types provided to the left of the `:` are ignored). The `module` program automatically sets the PLVio source repository to the ALL_SOURCE data dictionary view. It parses the source code using the PLVprsps package, searching for the definitions of any of the following PL/SQL code elements:

- Procedure header

- Function header

- Cursor header

- TYPE statement

`PLVcat.module` does not, in other words, currently catalogue variables, constants, exceptions, or other program elements that might appear in a PL/SQL package.

Since the `module` procedure works only with package specifications at this time, you do not have to tell PLVcat the type of object you want to catalogue when you call `PLVcat.module`. You simply provide the name of the package and it automatically scans the specification. As an example, to catalogue the PLVio package I would execute the following command in SQL*Plus:

>     SQL> exec PLVcat.module ('PLVio');

When control is returned back to the SQL*Plus prompt, the rows will have been written to `PLVctlg` and will be available for reporting and analysis. If your package is large, it may take a minute or two to complete the catalogue. String parsing and manipulation in the PL/SQL language is not known to be lightning fast.

#### 18.2.3.2 Cataloguing multiple modules

The `PLVcat.module` procedure can only process a single package at a time; you cannot pass in wildcarded package names for multiple-program cataloguing in one call. The `PLVcat.modules` procedure offers this capability; its header is:

>     PROCEDURE modules (module_in IN VARCHAR2);

You can use `modules` to catalogue all the packages in your schema with this call:

>     SQL> exec PLVcat.modules ('%');

Or you can be more selective. The following call to `modules` will catalogue all packages in the PL/Vision library:

>     SQL> exec PLVcat.modules ('PLV%');

The case you use to specify the package names is not significant. All program names are stored in the data dictionary in uppercase. (All right, so if you surround your program name in double quotes you can actually create programs with names in mixed case in the data dictionary; if you do this, you deserve all the ensuing complexities!)

#### 18.2.3.3 Examining the catalogue

To see how the `PLVctlg` table is populated by calls to `PLVcat.module` and `PLVcat.modules`, consider the following simplified version of the PLVtmr package specification:

>     PACKAGE PLVtmr
>     IS
>        FUNCTION elapsed RETURN NUMBER;
>        PROCEDURE show_elapsed;
>     END PLVtmr;
>     /

After cataloguing this package with `PLVcat.module`, I will have two rows in the `PLVctlg` table as follows:

Owner | Name1 | Type | Iname | Itype | Idatatype | Overloading
--- | --- | --- | --- | --- | --- | ---
>     PLV
>     PLVtmr
>     PACKAGE
>     elapsed
>     FUNCTION
>     NUMBER
>     1
>     PLV
>     PLVtmr
>     PACKAGE
>     show_elapsed
>     PROCEDURE
>     NULL
>     1

To obtain a list of all elements in the PLVtmr package, therefore, I could execute a SQL statement like this:

>     SELECT iname
>       FROM PLVctlg
>      WHERE name1 = 'PLVTMR';

To obtain a list of all functions catalogued for the PLV user account, I could execute a SQL statement like this:

>     SELECT iname
>       FROM PLVctlg
>      WHERE owner = 'PLV'
>        AND itype = 'FUNCTION';

The script named `inctlg.sql` contains a more complex and useful SQL statement for viewing the contents of the catalogue. The code for this script is:

>     TTITLE 'Elements Catalogued in &1'
>     SET VERIFY OFF
>     SET PAGESIZE 66
>     SET LINESIZE 60
>     COLUMN element FORMAT A60
>     SELECT DECODE (idatatype, NULL, NULL, idatatype || ' ') ||
>            itype || ' ' || owner || '.' ||
>            name1 || '.' || iname  || ' ' ||
>            DECODE (overload, 1, NULL,
>                    '(' || TO_CHAR(overload) || ')') element
>       FROM PLVctlg
>      WHERE name1 like UPPER ('&1')
>      ORDER BY owner, type, name1, itype, iname;

This script accepts as a single parameter the name of the package whose catalogue you wish to view. Executing this script for the `p` package provides the following output:

>     SQL> @inctlg p
>     Sat Jun 01                                         page    1
>                    Elements Catalogued in p
>     ELEMENT
>     ------------------------------------------------------------
>     VARCHAR2 FUNCTION PLV.P.LINESEP
>     VARCHAR2 FUNCTION PLV.P.PREFIX
>     PROCEDURE PLV.P.L (7)
>     PROCEDURE PLV.P.SET_LINESEP
>     PROCEDURE PLV.P.SET_PREFIX
>     PROCECURE PLV.P.TURN_OFF
>     PROCECURE PLV.P.TURN_ON

Notice that I am informed that the `p.l` procedure is overloaded seven times.

#### 18.2.3.4 Cataloguing PL/Vision

You can generate the catalogue for PL/Vision packages by executing the `plvcat.sql` script, located in the plvision\use subdirectory.

The code for `plvcat.sql` is simply:

>     BEGIN
>        FOR objind IN 1 .. PLV.numobjects
>        LOOP
>           PLVcat.module (PLV.objects(objind));
>        END LOOP;
>     END;
>     /

This script takes advantage of the list of PL/Vision objects that are stored in the `PLV.objects` PL/SQL table. This PL/SQL table is created and assigned values in the initialization section of the PLV package. The `plvcat.sql` script generates 396 rows in the `PLVctlg` table.

### 18.2.4 Identifying References and Dependencies

The other major area of functionality in PLVcat is to identify the references made within a program unit to external program elements. Such a reference implies a dependency; this information can be very useful in maintaining code, analyzing reuse and impact, and so on. The Oracle7 Server does maintain some dependency information, but it is only the minimum data required by the database to validate the status of compiled code. I cannot, for example, find out from the USER_DEPENDENCIES data dictionary view how many programs use the PLVdyn.ddl procedure. The most I can determine is the set of programs that use something in PLVdyn -- and this is not enough to support adequately an enterprise-wide deployment of PL/SQL applications.

The PLVcat package offers three programs to generate dependency information right down to the name of the package element that was referenced. It even lets you catalogue references to builtin functions like SUBSTR and all the builtin package programs. The three procedures that perform this task are:

>     PROCEDURE refnonkw (module_in IN VARCHAR2);
>     PROCEDURE refbi (module_in IN VARCHAR2);
>     PROCEDURE refall (module_in IN VARCHAR2);

In all three cases, you pass in the name of the individual module for which you want references generated.

`refnonkw`

  Identifies references to all non-keyword identifiers (application-specific elements).

`refbi`

  Identifies references to all kinds of builtins.

`refall`

  Identifies references to both non-keyword identifiers and builtins by calling `refnonkw` and `refbi`. Note that in the current implementation of `refall`, two passes are made against the specified module to parse the code.

The results of these scans are deposited in the `PLVrfrnc` table.

#### 18.2.4.1 Examining the references

Let's look at an example of how this table is populated from the source code. Consider the `testcase` package:

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

After cataloguing all builtins in this package with the `PLVcat.refbi`, I will have four rows in the `PLVrfrnc` table as follows:

Owner | Name1 | Name2 | Type | Rowner | Rname1 | Rname2
--- | --- | --- | --- | --- | --- | ---
>     PLV
>     testcase
>     NULL
>     PACKAGE BODY
>     SYS
>     DBMS_SQL
>     OPEN_CURSOR
>     PLV
>     testcase
>     NULL
>     PACKAGE BODY
>     SYS
>     INSERT
>     NULL
>     PLV
>     testcase
>     NULL
>     PACKAGE BODY
>     SYS
>     ROWCOUNT
>     NULL
>     PLV
>     testcase
>     NULL
>     PACKAGE BODY
>     SYS
>     UPDATE
>     NULL

After extracting all non-keywords in this package with the `PLVcat.refnonkw`, I will have a single row in the `PLVrfrnc` table as follows:

Owner | Name1 | Name2 | Type | Rowner | Rname1 | Rname2
--- | --- | --- | --- | --- | --- | ---
>     PLV
>     testcase
>     NULL
>     PACKAGE BODY
>     PLV
>     PLVCMT
>     PERFORM_COMMIT

Notice that PLVcat does not currently store references to non-PL/SQL objects, such as the `PLV_output` table. The reason is that it uses DBMS_UTILITY.NAME_RESOLVE to locate the code and this builtin does not work with non-PL/SQL objects.

To see a list of all program units that call the SUBSTR builtin function, you could execute this query:

>     SELECT owner || '.' || name1 program
>       FROM PLVrfrnc
>      WHERE rname1 = 'SUBSTR';

To see a list of all program units that call the `open_and_parse` function of the PLVdyn package, you could execute this query:

>     SELECT owner || '.' || name1 program
>       FROM PLVrfrnc
>      WHERE rname1 = 'PLVdyn'
>        AND rname2 = 'OPEN_AND_PARSE';

The script named `inctlg.sql` contains a more complex and useful SQL statement for viewing the contents of the catalogue. The code for this script is:

>     TTITLE 'Elements Referenced by &1'
>     SET VERIFY OFF
>     SET PAGESIZE 66
>     SET LINESIZE 60
>     COLUMN element FORMAT A60
>     SELECT owner || '.' ||
>            name1 || ' CONTAINS ' || rname1  ||
>            DECODE (rname2, NULL, NULL, '.' || rname2) element
>       FROM PLVrfrnc
>      WHERE name1 like UPPER ('&1')
>      ORDER BY owner, type, name1, rname1, rname2;

This script accepts as a single parameter the name of the program whose references you wish to view. Executing this script for the `testcase` package body provides the following output:

>     SQL> start inrfrnc testcase
>     Sat Jun 01                                         page    1
>                   Elements Referenced by testcase
>     ELEMENT
>     ------------------------------------------------------------
>     PLV.testcase CONTAINS DBMS_SQL.OPEN_CURSOR
>     PLV.testcase CONTAINS INSERT
>     PLV.testcase CONTAINS INSTR
>     PLV.testcase CONTAINS ROWCOUNT
>     PLV.testcase CONTAINS UPDATE
>     PLV.testcase CONTAINS PLVcmt.PERFORM_COMMIT

18.1 PLVcase: Converting the Case of PL/SQL Programs |  | V. Plug-and-Play Packages

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