Oracle PL/SCOPE

Search the Reference Library pages:

Oracle PL/SCOPE

Version 11.1

General

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.

First Available

11.1

Collected 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

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

Dependencies

gv$sysaux_occupants

user_plsql_object_settings

user_identifiers

PL/SCOPE Demo

Step 1: Recompile the package STANDARD

-- 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
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 to try this with any database you are not prepared to drop.

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

CREATE OR REPLACE PROCEDURE blended IS
vFileName CONSTANT VARCHAR2(30) := 'sources.txt';
vLoc       VARCHAR2(20) := 'CTEMP';
v_InHandle utl_file.file_type;
vNewLine   VARCHAR2(100);
vLineNo    PLS_INTEGER;
c1         PLS_INTEGER;
c2         PLS_INTEGER;
c3         PLS_INTEGER;
c4         PLS_INTEGER;
c5         PLS_INTEGER;

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

l_data profarray;

BEGIN
v_InHandle := utl_file.fopen(vLoc, vFileName, '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');
    c1 := INSTR(vNewLine, ',', 1,1);
    c2 := INSTR(vNewLine, ',', 1,2);
    c3 := INSTR(vNewLine, ',', 1,3);
    c4 := INSTR(vNewLine, ',', 1,4);
    c5 := INSTR(vNewLine, ',', 1,5);

    l_data(vLineNo).sourceno := SUBSTR(vNewLine,1,c1-1);
    l_data(vLineNo).sizeno    := SUBSTR(vNewLine,c1+1,c2-c1-1);
    l_data(vLineNo).status    := SUBSTR(vNewLine,c2+1,c3-c2-1);
    l_data(vLineNo).latitude := SUBSTR(vNewLine,c3+1,c4-c3-1);
    l_data(vLineNo).longitude := SUBSTR(vNewLine,c4+1,c5-c4-1);
    l_data(vLineNo).testfor   := SUBSTR(vNewLine,c5+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
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;

-- 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';

-----