Advanced Oracle PL/SQL Programming with Packages

Advanced Oracle PL/SQL Programming with PackagesSearch this book
Previous: 18.1 PLVcase: Converting the Case of PL/SQL ProgramsChapter 18
PLVcase and PLVcat: Converting and Analyzing PL/SQL Code
Next: V. Plug-and-Play Packages
 

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:

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


Previous: 18.1 PLVcase: Converting the Case of PL/SQL ProgramsAdvanced Oracle PL/SQL Programming with PackagesNext: V. Plug-and-Play Packages
18.1 PLVcase: Converting the Case of PL/SQL ProgramsBook IndexV. Plug-and-Play Packages

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference