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.
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:
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:
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.
188.8.131.52 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.
184.108.40.206 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.
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.
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:
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.
220.127.116.11 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!)
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';
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.
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.
The results of these scans are deposited in the PLVrfrnc table.
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 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';
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
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.