8 Using PL/Scope

Note:

Specifying Identifier Collection

By default, PL/Scope does not collect data for identifiers in the PL/SQL source program. To have PL/Scope collect data for all identifiers in the PL/SQL source program, including identifiers in package bodies, set the PL/SQL compilation parameter PLSCOPE_SETTINGS to 'IDENTIFIERS:ALL'.

PL/Scope stores the data that it collects in the SYSAUX tablespace. If the SYSAUX tablespace is unavailable, and you compile a program unit with PLSCOPE_SETTINGS='IDENTIFIERS:ALL', PL/Scope does not collect data for the compiled object. The compiler does not issue a warning, but it saves a warning in USER_ERRORS.

PL/Scope Identifier Data for STANDARD and DBMS_STANDARD

The packages STANDARD and DBMS_STANDARD declare and define base types, such as VARCHAR2 and NUMBER, and subprograms such as RAISE_APPLICATION_ERROR. If your database has PL/Scope identifier data for these packages, PL/Scope can track your usage of the identifiers that these packages create.

Do You Need STANDARD and DBMS_STANDARD Identifier Data?

You can use PL/Scope without STANDARD and DBMS_STANDARD identifier data. You need this data only if you must know where your code uses the base types or subprograms that these packages create—for example, if you want to know where your code uses the base type BINARY_INTEGER so that you can substitute PLS_INTEGER.

Does Your Database Have STANDARD and DBMS_STANDARD Identifier Data?

A newly created Oracle 11.1.0.7 database, or a database that was upgraded to 11.1.0.7 from 10.2, has PL/Scope identifier data for the packages STANDARD and DBMS_STANDARD. A database that was upgraded to 11.1.0.7 from 11.1.0.6 does not have this data.

To see if your database has this data, use the query in Example 8-1.

Example 8-1 shows what the query returns when the database has PL/Scope identifier data for STANDARD and DBMS_STANDARD.

SQL> SELECT UNIQUE OBJECT_NAME FROM ALL_IDENTIFIERS
 2 WHERE OBJECT_NAME IN ('STANDARD', 'DBMS_STANDARD')
 3 AND OWNER='SYS';
 
OBJECT_NAME
------------------------------
DBMS_STANDARD
STANDARD
 
2 rows selected.
 
SQL> 

If the query in Example 8-1 selects no rows, then the database does not have PL/Scope identifier data for the packages STANDARD and DBMS_STANDARD. To collect this data, a DBA must recompile the packages STANDARD and DBMS_STANDARD, as explained in Recompiling STANDARD and DBMS_STANDARD.

Recompiling STANDARD and DBMS_STANDARD

A DBA can use the following procedure to recompile the packages STANDARD and DBMS_STANDARD:

1. Connect to the database, shut it down, and then start it in UPGRADE mode:

   SQL> CONNECT / AS SYSDBA;
   SQL> SHUTDOWN IMMEDIATE;
   SQL> STARTUP PFILE=parameter_initialization_file UPGRADE;
   

2. Have PL/Scope collect data for all identifiers in the packages STANDARD and DBMS_STANDARD:

   SQL> ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL';
   

3. Invalidate and recompile the database:

   SQL> @?/rdbms/admin/utlirp.sql
   

   Now all PL/SQL objects in the database are invalid except STANDARD and DBMS_STANDARD, which were recompiled with PLSCOPE_SETTINGS='IDENTIFIERS:ALL'.

4. (Optional) Invalidate any other PL/SQL objects that you want to recompile with PLSCOPE_SETTINGS='IDENTIFIERS:ALL', using a script similar to the following.

   Customize the query on lines 5 through 9 to invalidate only those objects for which you need PL/Scope identifier data. Collecting all identifiers for all objects, as the following script does, might generate large amounts of data and slow compile time.

   SQL> DECLARE
    2 TYPE ObjIDArray IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
    3 ObjIDs ObjIDArray;
    4 BEGIN
    5 SELECT object_id BULK COLLECT INTO ObjIDs
    6 FROM ALL_OBJECTS
    7 WHERE object_type IN
    8 (SELECT DISTINCT TYPE
    9 FROM ALL_PLSQL_OBJECT_SETTINGS);
    10 FOR i IN 1..SQL%ROWCOUNT LOOP
    11 BEGIN
    12 DBMS_UTILITY.INVALIDATE(ObjIDs(i),
    13 'PLSCOPE_SETTINGS=IDENTIFIERS:ALL REUSE SETTINGS');
    14 NULL;
    15 END;
    16 END LOOP;
    17 END;
   

   Notes:

   In the preceding script:

   • Do not substitute ObjIDs.LAST for SQL%ROWCOUNT, because ObjIDs attributes are dependent on a package that is locked by the anonymous block.

   • If your database is large, do not substitute a cursor FOR LOOP for the BULK COLLECT statement, or you will run out of resources.

5. Shut down the database, and then start it in NORMAL mode:

   SQL> SHUTDOWN IMMEDIATE;
   SQL> STARTUP PFILE=parameter_initialization_file;
   

6. For any remaining invalid PL/SQL objects, do one of the following:

   • Allow them to be recompiled automatically, as they are referenced.

     (This can be slow if there are complex dependencies.)

   • Run the script utlrp.sql to recompile the invalid PL/SQL objects, as explained in Running utlrp.sql to Recompile Invalid PL/SQL Objects.

Running utlrp.sql to Recompile Invalid PL/SQL Objects

If the database was restarted in NORMAL mode (step 5), then a DBA, or a user who has been granted the DBA role, can use the following procedure:

1. Connect to the database as SYS:

   SQL> CONNECT / AS SYS;
   

2. Run the script utlrp.sql:

   SQL> @?/rdbms/admin/utlrp.sql
   

   If the script gives you any instructions, follow them, and then run the script again.

   If the script terminates abnormally without giving any instructions, run it again.

How Much Space is PL/Scope Data Using?

PL/Scope stores its data in the SYSAUX tablespace. If you are logged on as SYSDBA, you can use the query in Example 8-2 to display the amount of space that PL/Scope data is using.

SQL> SELECT SPACE_USAGE_KBYTES FROM V$SYSAUX_OCCUPANTS
 2 WHERE OCCUPANT_NAME='PL/SCOPE';
 
SPACE_USAGE_KBYTES
------------------
 384
 
1 row selected.
 
SQL> 

For information about managing the SYSAUX tablespace, see Oracle Database Administrator's Guide .

Viewing PL/Scope Data

To view the data that PL/Scope has collected, you can use any of the following:

• Static Data Dictionary Views

• Demo Tool

• SQL Developer

ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL';
CREATE OR REPLACE PROCEDURE a (p1 IN BOOLEAN) IS
 v PLS_INTEGER;
BEGIN
 v := 42;
 DBMS_OUTPUT.PUT_LINE(v);
 RAISE_APPLICATION_ERROR (-20000, 'Bad');
EXCEPTION
 WHEN Program_Error THEN NULL;
END a;
/
CREATE OR REPLACE PROCEDURE b (p2 OUT PLS_INTEGER, p3 IN OUT VARCHAR2) IS
 n NUMBER;
 q BOOLEAN := TRUE;
BEGIN
 FOR j IN 1..5 LOOP
 a(q); a(TRUE); a(TRUE);
 IF j > 2 THEN
 GOTO z;
 END IF;
 END LOOP;
<<z>> DECLARE
 d CONSTANT CHAR(1) := 'X';
 BEGIN
 SELECT COUNT(*) INTO n FROM Dual WHERE Dummy = d;
 END z;
END b;
/
WITH v AS (
 SELECT Line,
 Col,
 INITCAP(NAME) Name,
 LOWER(TYPE) Type,
 LOWER(USAGE) Usage,
 USAGE_ID,
 USAGE_CONTEXT_ID
 FROM USER_IDENTIFIERS
 WHERE Object_Name = 'B'
 AND Object_Type = 'PROCEDURE'
)
SELECT RPAD(LPAD(' ', 2*(Level-1)) ||
 Name, 20, '.')||' '||
 RPAD(Type, 20)||
 RPAD(Usage, 20)
 IDENTIFIER_USAGE_CONTEXTS
 FROM v
 START WITH USAGE_CONTEXT_ID = 0
 CONNECT BY PRIOR USAGE_ID = USAGE_CONTEXT_ID
 ORDER SIBLINGS BY Line, Col
/
IDENTIFIER_USAGE_CONTEXTS
-------------------------------------------------------------
B.................. procedure declaration
 B................. procedure definition
 P2.............. formal out declaration
 P3.............. formal in out declaration
 N............... variable declaration
 Q............... variable declaration
 Q............. variable assignment
 J............... iterator declaration
 A............. procedure call
 Q........... variable reference
 A............. procedure call
 A............. procedure call
 J............. iterator reference
 Z............. label reference
 Z............... label declaration
 D............. constant declaration
 D........... constant assignment
 N............. variable assignment
 D............. constant reference

CREATE OR REPLACE PROCEDURE p IS
 PROCEDURE p IS
 BEGIN
 DBMS_OUTPUT.PUT_LINE('Inner p');
 END p;
BEGIN
 DBMS_OUTPUT.PUT_LINE('Outer p');
 p();
END p;

Identifier Types that PL/Scope Collects

Table 8-1 shows the identifier types that PL/Scope collects, in alphabetical order. The identifier types in Table 8-1 appear in the TYPE column of the *_IDENTIFIER static data dictionary views, which are described in Oracle Database Reference.

Usages that PL/Scope Reports

Table 8-2 shows the usages that PL/Scope reports, in alphabetical order. The identifier types in Table 8-2 appear in the USAGE column of the *_IDENTIFIER static data dictionary views, which are described in Oracle Database Reference.

Sample PL/Scope Session

The sample PL/Scope session uses the following PL/SQL procedure, example.sql:

CREATE OR REPLACE PACKAGE PACK1 IS
 TYPE r1 is RECORD (rf1 VARCHAR2(10));
 FUNCTION F1(fp1 NUMBER) RETURN NUMBER;
 PROCEDURE P1(pp1 VARCHAR2);
END PACK1;
/
CREATE OR REPLACE PACKAGE BODY PACK1 IS
 FUNCTION F1(fp1 NUMBER) RETURN NUMBER IS
 a NUMBER := 10;
 BEGIN
 RETURN a;
 END F1;
 PROCEDURE P1(pp1 VARCHAR2) IS
 pr1 r1;
 BEGIN
 pr1.rf1 := pp1;
 END;
END PACK1;
/

In the following sample session, assume that you are logged in as HR.

1. Set the session parameter:

   SQL> ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL';
   

2. Compile the PL/SQL procedure example.sql:

   SQL> @example.sql
   

3. Verify that PL/Scope collected all identifiers for the package body:

   SQL> SELECT PLSCOPE_SETTINGS
    FROM USER_PLSQL_OBJECT_SETTINGS
    WHERE NAME='PACK1' AND TYPE='PACKAGE BODY'
   PLSCOPE_SETTINGS
   ----------------
   IDENTIFIERS:ALL
   

4. Display unique identifiers in HR by querying for all DECLARATION usages. For example, to see all unique identifiers with name like %1, use the following query:

   SQL> SELECT NAME, SIGNATURE, TYPE
    FROM USER_IDENTIFIERS
    WHERE NAME LIKE '%1' AND USAGE='DECLARATION'
    ORDER BY OBJECT_TYPE, USAGE_ID;
   

   NAME SIGNATURE TYPE
   ---------------------------------------------------------------
   PACK1 41820FA4D5EF6BE707895178D0C5C4EF PACKAGE
   R1 EEBB6849DEE31BC77BF186EBAE5D4E2D RECORD
   RF1 41D70040337349634A7F547BC83517C7 VARIABLE
   F1 EEFCF8352A41F4F264B4EF20D7F63A74 FUNCTION
   FP1 70648EC9E1C3C7FA10C0AE6415FAEC3B FORMAL IN
   P1 0BE2106B9EFA719D49AF60965EBD69AE PROCEDURE
   PP1 85B6C0F3BBA39185B00465082322444B FORMAL IN
   FP1 771368AE41084ADD477DE62A7B1D4278 FORMAL IN
   PP1 D98482491487F39B4CBC8B776130B739 FORMAL IN
   PR1 174C2528B929953F4FE2A43DEBA2B5D0 VARIABLE
   P1 3D1CA191D63523E40E25A72D89424324 FORMAL IN
   

   The *_IDENTIFIERS static data dictionary views display only basic type names; for example, the TYPE of a local variable or record field is VARIABLE. To determine the exact type of a VARIABLE, you must use its USAGE_CONTEXT_ID.

5. Find all local variables:

   SQL> SELECT a.NAME variable_name,
    b.NAME context_name,
    a.SIGNATURE
    FROM USER_IDENTIFIERS a, USER_IDENTIFIERS b
    WHERE a.USAGE_CONTEXT_ID = b.USAGE_ID
    AND a.TYPE = 'VARIABLE'
    AND a.USAGE = 'DECLARATION'
    AND a.OBJECT_NAME = 'PACK1'
    AND a.OBJECT_NAME = b.OBJECT_NAME
    AND a.OBJECT_TYPE = b.OBJECT_TYPE
    AND (b.TYPE = 'FUNCTION' or b.TYPE = 'PROCEDURE')
    ORDER BY a.OBJECT_TYPE, a.USAGE_ID;
   VARIABLE_NAME CONTEXT_NAME SIGNATURE
   ---------------------------------------------------------------
   A F1 2268998957D20FACD63493B7A77BC55B
   PR1 P1 174C2528B929953F4FE2A43DEBA2B5D0
   

6. Find all usages performed on the local variable A:

   SQL> SELECT USAGE, USAGE_ID, OBJECT_NAME, OBJECT_TYPE
    FROM USER_IDENTIFIERS
    WHERE SIGNATURE='2268998957D20FACD63493B7A77BC55B'
    ORDER BY OBJECT_TYPE, USAGE_ID;
   USAGE USAGE_ID OBJECT_NAME OBJECT_TYPE
   ------------------------------------------------------
   DECLARATION 4 PACK1 PACKAGE BODY
   ASSIGNMENT 5 PACK1 PACKAGE BODY
   REFERENCE 6 PACK1 PACKAGE BODY
   

   The usages performed on the local identifier A are the identifier declaration (USAGE_ID 6), an assignment (USAGE_ID 8), and a reference (USAGE_ID 9).

7. From the declaration of the local identifier A, find its type:

   SQL> SELECT a.NAME, a.TYPE
    FROM USER_IDENTIFIERS a, USER_IDENTIFIERS b
    WHERE a.USAGE = 'REFERENCE'
    AND a.USAGE_CONTEXT_ID = b.USAGE_ID
    AND b.USAGE = 'DECLARATION'
    AND b.SIGNATURE = '2268998957D20FACD63493B7A77BC55B'
    AND a.OBJECT_TYPE = b.OBJECT_TYPE
    AND a.OBJECT_NAME = b.OBJECT_NAME;
   NAME TYPE
   --------------------------------
   NUMBER DATATYPE STANDARD
   

   Note:

   This query produces the output shown only if your database has PL/Scope identifier data for the packages STANDARD and DBMS_STANDARD. For more information, see PL/Scope Identifier Data for STANDARD and DBMS_STANDARD.

8. Find out where the assignment to local identifier A occurred:

   SQL> SELECT LINE, COL, OBJECT_NAME, OBJECT_TYPE
    FROM USER_IDENTIFIERS
    WHERE SIGNATURE='666CEC3A2180DF4013CEBE330A8CE747'
    AND USAGE='ASSIGNMENT';
   LINE COL OBJECT_NAME OBJECT_TYPE
   ------------------------------------------------
   3 7 PACK1 PACKAGE BODY