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

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

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

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