12 Using PL/Scope

PL/Scope is a compiler-driven tool that collects data about identifiers in PL/SQL source code at program-unit compilation time and makes it available in static data dictionary views. The collected data includes information about identifier types, usages (declaration, definition, reference, call, assignment) and the location of each usage in the source code.

PL/Scope lets you develop powerful and effective PL/Scope source code browsers that increase PL/SQL developer productivity by minimizing time spent browsing and understanding source code.

PL/Scope is intended for application developers, and is usually used in the environment of a development database.

Note:

PL/Scope cannot collect data for a PL/SQL unit whose source code is wrapped. For information about wrapping PL/SQL source code, see Oracle Database PL/SQL Language Reference.

Topics:

12.1 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'.

Note:

Collecting all identifiers might generate large amounts of data and slow compile time.

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.

See Also:

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

Topics:

12.2.1 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, to know where your code uses the base type BINARY_INTEGER, so that you can substitute PLS_INTEGER.

12.2.2 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 12-1.

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

Example 12-1 STANDARD and DBMS_STANDARD PL/Scope Identifier Data Available?

Query:

COLUMN OBJECT_NAME FORMAT A14

SELECT UNIQUE OBJECT_NAME
FROM ALL_IDENTIFIERS
WHERE OBJECT_NAME IN ('STANDARD', 'DBMS_STANDARD')
AND OWNER='SYS'
ORDER BY OBJECT_NAME;
 

Result:

OBJECT_NAME
--------------
DBMS_STANDARD
STANDARD
 
2 rows selected.

If the query in Example 12-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 Section 12.2.3.

12.2.3 Recompiling STANDARD and DBMS_STANDARD

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

Note:

This procedure invalidates and revalidates (by recompiling) every PL/SQL object in the database.
  1. Connect to the database, shut it down, and then start it in UPGRADE mode:

    CONNECT / AS SYSDBA;
    SHUTDOWN IMMEDIATE;
    STARTUP PFILE=parameter_initialization_file UPGRADE;
    
  2. Have PL/Scope collect data for all identifiers in the packages STANDARD and DBMS_STANDARD:

    ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL';
    
  3. Invalidate and recompile the database:

    @?/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 to be recompiled with PLSCOPE_SETTINGS='IDENTIFIERS:ALL', using a script similar to this.

    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 this script does, might generate large amounts of data and slow compile time:

    DECLARE
      TYPE ObjIDArray IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
      ObjIDs  ObjIDArray;
    BEGIN
      SELECT object_id BULK COLLECT INTO ObjIDs
      FROM ALL_OBJECTS
      WHERE object_type IN
        (SELECT DISTINCT TYPE
         FROM ALL_PLSQL_OBJECT_SETTINGS);
      FOR i IN 1..SQL%ROWCOUNT LOOP
        BEGIN
          DBMS_UTILITY.INVALIDATE(ObjIDs(i),
            'PLSCOPE_SETTINGS=IDENTIFIERS:ALL REUSE SETTINGS');
          NULL;
        END;
      END LOOP;
    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:

    SHUTDOWN IMMEDIATE;
    STARTUP PFILE=parameter_initialization_file;
    
  6. For any remaining invalid PL/SQL objects, do either of these:

    • 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 Section 12.2.4.

12.2.4 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 this procedure:

  1. Connect to the database as SYS:

    CONNECT / AS SYS;
    
  2. Run the script utlrp.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.

12.3 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 12-2 to display the amount of space that PL/Scope data is using.

Example 12-2 How Much Space is PL/Scope Data Using?

Query:

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

Result:

SPACE_USAGE_KBYTES
------------------
              1600
 
1 row selected.

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

12.4 Viewing PL/Scope Data

To view the data that PL/Scope has collected, you can use either:

12.4.1 Static Data Dictionary Views

The static data dictionary views *_IDENTIFIERS display information about PL/Scope identifiers, including their types and usages. For general information about these views, see Oracle Database Reference.

Topics:

12.4.1.1 Unique Keys

Each row of a *_IDENTIFIERS view represents a unique usage of an identifier in the PL/SQL unit. In each of these views, these are equivalent unique keys within a compilation unit:

  • LINE, COL, and USAGE

  • USAGE_ID

For the usages in the *_IDENTIFIERS views, see Section 12.6.

Note:

An identifier that is passed to a subprogram in IN OUT mode has two rows in *_IDENTIFIERS: a REFERENCE usage (corresponding to IN) and an ASSIGNMENT usage (corresponding to OUT).

12.4.1.2 Context

Context is useful for discovering relationships between usages. Except for top-level schema object declarations and definitions, every usage of an identifier happens within the context of another usage. For example:

  • A local variable declaration happens within the context of a top-level procedure declaration.

  • If an identifier is declared as a variable, such as x VARCHAR2(10), the USAGE_CONTEXT_ID of the VARCHAR2 type reference contains the USAGE_ID of the x declaration, allowing you to associate the variable declaration with its type.

In other words, USAGE_CONTEXT_ID is a reflexive foreign key to USAGE_ID, as Example 12-3 shows.

Example 12-3 USAGE_CONTEXT_ID and USAGE_ID

ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL';

CREATE OR REPLACE PROCEDURE a (p1 IN BOOLEAN) AUTHID DEFINER 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
)  AUTHID DEFINER
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
/

Result:

IDENTIFIER_USAGE_CONTEXTS
-------------------------------------------------------------
B................... procedure           declaration
  B................. procedure           definition
    P2.............. formal out          declaration
      Pls_Integer... subtype             reference
    P3.............. formal in out       declaration
      Varchar2...... character datatype  reference
    N............... variable            declaration
      Number........ number datatype     reference
    Q............... variable            declaration
      Q............. variable            assignment
      Boolean....... boolean datatype    reference
    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
        Char........ subtype             reference
      N............. variable            assignment
      D............. constant            reference
 
24 rows selected.

12.4.1.3 Signature

The signature of an identifier is unique, within and across program units. That is, the signature distinguishes the identifier from other identifiers with the same name, whether they are defined in the same program unit or different program units.

For the program unit in Example 12-4, which has two identifiers named p, the static data dictionary view USER_IDENTIFIERS has several rows in which NAME is p, but in these rows, SIGNATURE varies. The rows associated with the outer procedure p have one signature, and the rows associated with the inner procedure p have another signature. If program unit q calls procedure p, the USER_IDENTIFIERS view for q has a row in which NAME is p and SIGNATURE is the signature of the outer procedure p.

Example 12-4 Program Unit with Two Identifiers Named p

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

12.4.2 Demo Tool

$ORACLE_HOME/plsql/demo/plscopedemo.sql is an HTML-based demo implemented as a PL/SQL web application using the PL/SQL Web Toolkit.

12.4.3 SQL Developer

PL/Scope is a feature of SQL Developer. For information about using PL/Scope from SQL Developer, see the SQL Developer online help or documentation.

12.5 Identifier Types that PL/Scope Collects

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

Note:

Identifiers declared in compilation units that were not compiled with PLSCOPE_SETTINGS='IDENTIFIERS:ALL' do not appear in *_IDENTIFIER static data dictionary views.

Table 12-1 Identifier Types that PL/Scope Collects

TYPE Column Value Comment

ASSOCIATIVE ARRAY

 

CONSTANT

 

CURSOR

 

BFILE DATATYPEBLOB DATATYPEBOOLEAN DATATYPECHARACTER DATATYPECLOB DATATYPEDATE DATATYPEINTERVAL DATATYPENUMBER DATATYPETIME DATATYPETIMESTAMP DATATYPE

Each DATATYPE is a base type declared in package STANDARD.

EXCEPTION

 

FORMAL INFORMAL IN OUTFORMAL OUT

 

FUNCTION

 

INDEX TABLE

 

ITERATOR

An iterator is the index of a FOR loop.

LABEL

A label declaration also acts as a context.

LIBRARY

 

NESTED TABLE

 

OBJECT

 

OPAQUE

Examples of internal opaque types are ANYDATA and XMLType.

PACKAGE

 

PROCEDURE

 

RECORD

 

REFCURSOR

 

SUBTYPE

 

SYNONYM

PL/Scope does not resolve the base object name of a synonym. To find the base object name of a synonym, query *_SYNONYMS.

TRIGGER

 

UROWID

 

VARRAY

 

VARIABLE

Can be object attribute, local variable, package variable, or record field.


12.6 Usages that PL/Scope Reports

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

Table 12-2 Usages that PL/Scope Reports

USAGE Column Value Description

ASSIGNMENT

An assignment can be made only to an identifier that can have a value, such as a VARIABLE. Examples of assignments are:

  • Using an identifier to the left of an assignment operator

  • Using an identifier in the INTO clause of a FETCH statement

  • Passing an identifier to a subprogram by reference (OUT mode)

  • Using an identifier as the bind variable in the USING clause of an EXECUTE IMMEDIATE statement in either OUT or IN OUT mode

An identifier that is passed to a subprogram in IN OUT mode has both a REFERENCE usage (corresponding to IN) and an ASSIGNMENT usage (corresponding to OUT).

CALL

In the context of PL/Scope, a CALL is an operation that pushes a call onto the call stack; that is:

  • A call to a FUNCTION or PROCEDURE

  • Running or fetching a cursor identifier (a logical call to SQL)

A GOTO statement or raise of an exception is not a CALL, because neither pushes a call onto the call stack.

DECLARATION

A DECLARATION tells the compiler that an identifier exists, and each identifier has exactly one DECLARATION. Each DECLARATION can have an associated data type.

For a loop index declaration, LINE and COL (in *_IDENTIFIERS views) are the line and column of the FOR clause that implicitly declares the loop index.

For a label declaration, LINE and COL are the line and column on which the label appears (and is implicitly declared) within the delimiters << and >>.

DEFINITION

A DEFINITION tells the compiler how to implement or use a previously declared identifier.

Each of these types of identifiers has a DEFINITION:

  • EXCEPTION (can have multiple definitions)

  • FUNCTION

  • OBJECT

  • PACKAGE

  • PROCEDURE

  • TRIGGER

For a top-level identifier only, the DEFINITION and DECLARATION are in the same place.

REFERENCE

A REFERENCE uses an identifier without changing its value. Examples of references are:

  • Raising an exception identifier

  • Using a type identifier in the declaration of a variable or formal parameter

  • Using a variable identifier whose type contains fields to access a field. For example, in myrecordvar.myfield := 1, a reference is made to myrecordvar, and an assignment is made to myfield.

  • Using a cursor for any purpose except FETCH

  • Passing an identifier to a subprogram by value (IN mode)

  • Using an identifier as the bind variable in the USING clause of an EXECUTE IMMEDIATE statement in either IN or IN OUT mode

An identifier that is passed to a subprogram in IN OUT mode has both a REFERENCE usage (corresponding to IN) and an ASSIGNMENT usage (corresponding to OUT).


12.7 Sample PL/Scope Session

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

  1. Set the session parameter:

    ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL';
    
  2. Create this package:

    CREATE OR REPLACE PACKAGE PACK1 AUTHID DEFINER 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;
    /
    
  3. Verify that PL/Scope collected all identifiers for the package body:

    SELECT PLSCOPE_SETTINGS
    FROM USER_PLSQL_OBJECT_SETTINGS
    WHERE NAME='PACK1' AND TYPE='PACKAGE BODY'
    

    Result:

    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 these SQL*Plus formatting commands and this query:

    COLUMN NAME FORMAT A6
    COLUMN SIGNATURE FORMAT A32
    COLUMN TYPE FORMAT A9
    
    SELECT NAME, SIGNATURE, TYPE
    FROM USER_IDENTIFIERS
    WHERE NAME LIKE '%1' AND USAGE='DECLARATION'
    ORDER BY OBJECT_TYPE, USAGE_ID;
    

    Result is similar to:

    NAME   SIGNATURE                        TYPE
    ------ -------------------------------- ---------
    PACK1  41820FA4D5EF6BE707895178D0C5C4EF PACKAGE
    R1     EEBB6849DEE31BC77BF186EBAE5D4E2D RECORD
    RF1    41D70040337349634A7F547BC83517C7 VARIABLE
    F1     D51E825FF334817C977174423E3D0765 FUNCTION
    FP1    CAC3474C112DBEC67AB926978D9A16C1 FORMAL IN
    P1     B7C0576BA4D00C33A65CC0C64CADAB89 PROCEDURE
    PP1    6B74CF95A5B7377A735925DFAA280266 FORMAL IN
    FP1    98EB63B8A4AFEB5EF94D50A20165D6CC FORMAL IN
    PP1    62D8463A314BE1F996794723402278CF FORMAL IN
    PR1    BDB1CB26C97562CCC20CD1F32D341D7C VARIABLE
     
    10 rows selected.
    

    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:

    COLUMN VARIABLE_NAME FORMAT A13
    COLUMN CONTEXT_NAME FORMAT A12
    
    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;
    

    Result is similar to:

    VARIABLE_NAME CONTEXT_NAME SIGNATURE
    ------------- ------------ --------------------------------
    A             F1           1691C6B3C951FCAA2CBEEB47F85CF128
    PR1           P1           BDB1CB26C97562CCC20CD1F32D341D7C
     
    2 rows selected.
    
  6. Find all usages performed on the local variable A:

    COLUMN USAGE FORMAT A11
    COLUMN USAGE_ID FORMAT 999
    COLUMN OBJECT_NAME FORMAT A11
    COLUMN OBJECT_TYPE FORMAT A12
    
    SELECT USAGE, USAGE_ID, OBJECT_NAME, OBJECT_TYPE
    FROM USER_IDENTIFIERS
    WHERE SIGNATURE='1691C6B3C951FCAA2CBEEB47F85CF128'  -- signature of A
    ORDER BY OBJECT_TYPE, USAGE_ID;
    

    Result:

    USAGE       USAGE_ID OBJECT_NAME OBJECT_TYPE
    ----------- -------- ----------- ------------
    DECLARATION        6 PACK1       PACKAGE BODY
    ASSIGNMENT         8 PACK1       PACKAGE BODY
    REFERENCE          9 PACK1       PACKAGE BODY
     
    3 rows selected.
    

    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:

    COLUMN NAME FORMAT A6
    COLUMN TYPE FORMAT A15
    
    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 = 'D51E825FF334817C977174423E3D0765'  -- signature of F1
    AND a.OBJECT_TYPE = b.OBJECT_TYPE
    AND a.OBJECT_NAME = b.OBJECT_NAME;
    

    Result:

    NAME   TYPE
    ------ ---------------
    NUMBER NUMBER DATATYPE
     
    1 row selected.
    

    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 Section 12.2.
  8. Find out where the assignment to local identifier A occurred:

    SELECT LINE, COL, OBJECT_NAME, OBJECT_TYPE
    FROM USER_IDENTIFIERS
    WHERE SIGNATURE='1691C6B3C951FCAA2CBEEB47F85CF128'  -- signature of A
    AND USAGE='ASSIGNMENT';
    

    Result:

          LINE        COL OBJECT_NAME OBJECT_TYPE
    ---------- ---------- ----------- ------------
             3          5 PACK1       PACKAGE BODY
     
    1 row selected.