Skip Headers
Oracle® Database Advanced Application Developer's Guide
11g Release 1 (11.1)

Part Number B28424-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

8 Using PL/Scope

PL/Scope is a compiler-driven tool that collects data about user-defined identifiers from 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, assigment) and the location of each usage in the source code.

PL/Scope enables the development of powerful and effective PL/Scope source code browsers that increase PL/SQL developer productivity by minimizing time spent browsing and understanding source code.

Note:

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

Topics:

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'

Note:

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

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

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:

Unique Keys

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

  • LINE, COL, and USAGE

  • USAGE_ID

For the usages in the *_IDENTIFIERS views, see "Usages that PL/Scope Reports".

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

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 8-1 shows.

Example 8-1 USAGE_CONTEXT_ID and USAGE_ID

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

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 8-2, 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 8-2 Program Unit with Two Identifiers Named p

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;

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. For more information about PL/SQL Web Applications, see "Implementing PL/SQL Web Applications".

SQL Developer

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

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.

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 8-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. In order to collect and view these identifiers, package STANDARD must be compiled with PLSCOPE_SETTINGS='IDENTIFIERS:ALL'.

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.


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.

Table 8-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 argument 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 new call stack; that is:

  • A call to a FUNCTION or PROCEDURE

  • Executing 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 new 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 datatype.

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 the following 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 argument 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).


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