| General Information |
| 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
Read the warning to the right and then ignore this step. |
-- 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.
Do ignore this step! |
| 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 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'; |
|
