8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

PL/Scope in Oracle Database 11g Release 1 (11.1)

PL/Scope is a tool that gathers information about user defined identifiers in PL/SQL code at compile time.

Most users will view the PL/Scope data via a PL/SQL IDE, such as SQL Developer.

Control PL/Scope Data Collection
Collect PL/Scope Data
Display PL/Scope Data

Control PL/Scope Data Collection

Collection of PL/Scope data is controlled by the PLSCOPE_SETTINGS parameter, which has a default setting of "IDENTIFIERS:NONE". Switch this value to "IDENTIFIERS:ALL" to enable collection.

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

Session altered.

SQL>

The data is stored in the SYSAUX tablespace, so the current space used for PL/Scope data can be displayed with the following query.

SELECT space_usage_kbytes
FROM   v$sysaux_occupants
WHERE  occupant_name = 'PL/SCOPE';

SPACE_USAGE_KBYTES
------------------
               384

1 row selected.

SQL>

It's probably better to collect identifier information for specific items of interest, rather than doing it for all code, as this will waste space in the SYSAUX tablespace.

Collect PL/Scope Data

Data collection is performed for all objects compiled in the session after the flag is set, so we must now create an object to gather some data.

CREATE OR REPLACE PROCEDURE test_plscope (p_in  IN  NUMBER) AS
  l_var NUMBER;
BEGIN
  l_var := p_in;
  
  l_var := l_var + 1;
END;
/

Display PL/Scope Data

The PL/Scope data is available from the {DBA|ALL|USER}_IDENTIFIERS views. The following query displays data gathered during the compilation of the test_plscope procedure.

COLUMN name FORMAT A30
SELECT LPAD(' ', level*2, ' ') || name AS name,
       type,
       usage,
       usage_id,
       line,
       col
FROM   user_identifiers
START WITH usage_context_id = 0
CONNECT BY PRIOR usage_id = usage_context_id;

NAME                           TYPE               USAGE         USAGE_ID       LINE        COL
------------------------------ ------------------ ----------- ---------- ---------- ----------
  TEST_PLSCOPE                 PROCEDURE          DECLARATION          1          1         11
    TEST_PLSCOPE               PROCEDURE          DEFINITION           2          1         11
      P_IN                     FORMAL IN          DECLARATION          3          1         25
      L_VAR                    VARIABLE           DECLARATION          4          2          3
      L_VAR                    VARIABLE           ASSIGNMENT           5          4          3
        P_IN                   FORMAL IN          REFERENCE            6          4         12
      L_VAR                    VARIABLE           ASSIGNMENT           7          6          3
        L_VAR                  VARIABLE           REFERENCE            8          6         12

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.