8 Using PL/Scope

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, use the following SQL statement:

ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL'

PLSCOPE_SETTINGS='IDENTIFIERS:ALL' affects only the PL/SQL code compiled after you specify it. If you compile a PL/SQL program unit with PLSCOPE_SETTINGS='IDENTIFIERS:NONE' (the default), PL/Scope does not collect its identifiers, and drops any identifiers that it previously collected for that unit. To have PL/Scope collect its identifiers, recompile the program unit with PLSCOPE_SETTINGS='IDENTIFIERS:ALL'. To see the value that IDENTIFIERS had when a compilation unit was compiled, see the static data dictionary view *_PLSQL_OBJECT_SETTINGS.

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.

How Much Space is PL/Scope Data Using?

Because PL/Scope stores its data in the SYSAUX tablespace, you can use the following query to display the amount of space that the data is using:

SELECT SPACE_USAGE_KBYTES FROM V$SYSAUX_OCCUPANTS
  WHERE OCCUPANT_NAME='PL/SCOPE';

For information about managing the SYSAUX tablespace and monitoring its occupants, 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

CONNECT USR/password
ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL';
/
CREATE 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 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 this output only if package STANDARD was compiled with PLSCOPE_SETTINGS='IDENTIFIERS:ALL'. By default, this query returns no identifier data. Please see the 11gR1 release notes for more information on how to compile package STANDARD for PL/Scope.

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