Oracle PL/SCOPE
Version 12.1.0

General Information
Library Note This page has been updated for Oracle release 12cR1 and shown to be compatible in the Container DB.
Note: PL/Scope allow the browsing of PL/SQL source code in a manner similar to the way that Cscope (http://cscope.sourceforge.net) allows browsing of C source code. Users can search for and display definitions, declarations, assignments, and references.
Collection Identifiers
ASSOCIATIVE ARRAY DATATYPETIMESTAMP OPAQUE (for example AnyData)
CONSTANT DATATYPE OUTFORMAL OUT
CURSOR EXCEPTION PACKAGE
BFILE FORMAL PROCEDURE
DATATYPEBLOB FUNCTION RECORD
DATATYPEBOOLEAN INDEX TABLE REFCURSOR
DATATYPECHARACTER INFORMAL IN SUBTYPE
DATATYPECLOB ITERATOR (loop iterator) SYNONYM
DATATYPEDATE LABEL TRIGGER
DATATYPEINTERVAL LIBRARY UROWID
DATATYPENUMBER NESTED TABLE VARRAY
DATATYPETIME OBJECT VARIABLE
Data Dictionary Objects
ALL_IDENTIFIERS CDB_PLSQL_OBJECT_SETTINGS USER_IDENTIFIERS
ALL_PLSQL_OBJECT_SETTINGS DBA_IDENTIFIERS USER_PLSQL_OBJECT_SETTINGS
CDB_IDENTIFIERS DBAL_PLSQL_OBJECT_SETTINGS V$SYSAUX_OCCUPANTS
First Available 11.1.0.6
Usage Column Values
ASSIGNMENT An assignment can be made only to an identifier that can have a value, such as a VARIABLE
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 or executing or fetching a cursor identifier (a logical call to SQL)
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).
 
PL/SCOPE Demo
Step 1: Recompile the package STANDARD

Read the warning to the right and then ignore this step unless you think dropping a brick on your foot is lovely fun.
Oracle stated this in their docs at one time.

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.

so I tried it to see what would happen:

conn / as sysdba

ALTER SESSION SET plscope_settings='IDENTIFIERS:ALL';

ALTER PACKAGE standard COMPILE;

SELECT COUNT(*)
FROM dba_objects_ae
WHERE status = 'INVALID';


The end result was a totally compromised database with so many invalid objects that utlrp and UTL_RECOMP were useless. Do not try this with any database you are not prepared to drop.

Needless to say I reported the doc bug and the comment was removed. But just in case someone finds a copy of this idiocy in some obscure corner of the internet ... you have been warned.

Ignore this step! Not this warning!
Step 2: Create PL/SQL Objects CREATE TABLE sources_import (
sourceno  VARCHAR2(10),
sizeno    VARCHAR2(10),
status    VARCHAR2(10),
latitude  VARCHAR2(10),
longitude VARCHAR2(10),
testfor   VARCHAR2(15));

-- to download the file sources.txt [click here] and place it in a directory identified by the Oracle directory name CTEMP

CREATE OR REPLACE PROCEDURE blended AUTHID CURRENT_USER IS
 cFileName  CONSTANT VARCHAR2(30) := 'sources.txt';
 vLoc       VARCHAR2(20) := 'CTEMP';
 v_InHandle utl_file.file_type;
 vNewLine   VARCHAR2(100);
 vLineNo    PLS_INTEGER;
 col1       PLS_INTEGER;
 col2       PLS_INTEGER;
 col3       PLS_INTEGER;
 col4       PLS_INTEGER;
 col5       PLS_INTEGER;

 TYPE profarray IS TABLE OF sources_import%ROWTYPE
 INDEX BY BINARY_INTEGER;

 l_data profarray;
BEGIN
  v_InHandle := utl_file.fopen(vLoc, cFileName, 'r');
  vLineNo := 1;
  LOOP
    BEGIN
      utl_file.get_line(v_InHandle, vNewLine);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXIT;
    END;

    vNewLine := TRANSLATE(vNewLine, 'A''', 'A');
    col1 := INSTR(vNewLine, ',', 1,1);
    col2 := INSTR(vNewLine, ',', 1,2);
    col3 := INSTR(vNewLine, ',', 1,3);
    col4 := INSTR(vNewLine, ',', 1,4);
    col5 := INSTR(vNewLine, ',', 1,5);

    l_data(vLineNo).sourceno  := SUBSTR(vNewLine,1,col1-1);
    l_data(vLineNo).sizeno    := SUBSTR(vNewLine,col1+1,col2-col1-1);
    l_data(vLineNo).status    := SUBSTR(vNewLine,col2+1,col3-col2-1);
    l_data(vLineNo).latitude  := SUBSTR(vNewLine,col3+1,col4-col3-1);
    l_data(vLineNo).longitude := SUBSTR(vNewLine,col4+1,col5-col4-1);
    l_data(vLineNo).testfor   := SUBSTR(vNewLine,col5+1);

    vLineNo := vLineNo+1;
  END LOOP;
  utl_file.fclose(v_InHandle);

  FORALL i IN 1..l_data.COUNT
  INSERT INTO sources_import VALUES l_data(i);
  DELETE FROM sources_import WHERE sourceno = 'SOURCENO';
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END blended;
/
Step 3: Browse PL/SQL SELECT inst_id, space_usage_kbytes
FROM gv$sysaux_occupants
WHERE occupant_name='PL/SCOPE';

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%scope%';

ALTER SESSION SET plscope_settings='IDENTIFIERS:ALL';

ALTER PROCEDURE blended COMPILE;

desc user_plsql_object_settings

desc user_identifiers

set linesize 121

-- view user identifier metadata
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;

-- narrow the search with a WHERE clause
SELECT LPAD(' ', LEVEL*2, ' ') || name AS name, type, usage, usage_id,
line, col
FROM user_identifiers
WHERE name LIKE '%PROFARRAY%'
START WITH usage_context_id = 0
CONNECT BY PRIOR usage_id = usage_context_id;

-- verify PL/SCOPE collection
SELECT plscope_settings
FROM user_plsql_object_settings
WHERE name = 'BLENDED'
AND type = 'PROCEDURE';

-- find unique identifiers
SELECT name, type, signature
FROM user_identifiers
WHERE usage = 'DECLARATION'
ORDER BY 2,1;

-- find the local variables
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 = 'BLENDED'
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;

-- find all uses of a named variable
SELECT usage, usage_id, object_name, object_type
FROM user_identifiers
WHERE signature = '504D03BD16F3D15090E52D06F8BBAEBB'
ORDER BY object_type, usage_id;

-- from the declaration of a local identifier find its type
SELECT a.name, a.type, b.usage, b.signature
FROM user_identifiers a, user_identifiers b
WHERE a.usage = 'REFERENCE'
AND a.usage_context_id = b.usage_id
AND b.usage = 'DECLARATION'
AND a.signature = '504D03BD16F3D15090E52D06F8BBAEBB'
AND a.object_type = b.object_type
AND a.object_name = b.object_name
ORDER BY 4,1;

-- find where assignments occur
SELECT line, col, name, object_name, object_type
FROM user_identifiers
WHERE usage = 'ASSIGNMENT';

ALTER SESSION SET plscope_settings='IDENTIFIERS:NONE';

Related Topics
DBMS_HPROF

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved