CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

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';
 
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [271 users online]    © 2010 psoug.org