SELECT ai.object_name
, ai.object_type
, ai.name variable_name
, ai.name context_name
FROM all_identifiers ai
WHERE ai.owner = USER AND
ai.TYPE = 'VARIABLE' AND
ai.usage = 'DECLARATION'
ORDER BY ai.object_name,
ai.object_type, ai.usage_id
USING USAGE IDS TO UNDERS TAND
IDENTIFIER HIERARCHY
A package may contain one or more subprograms; a subprogram may have one or more
parameters. You can use PL/Scope to expose
this hierarchy. Suppose, for example, you
have defined a package as shown in Listing 1.
You can then execute a hierarchical query,
specifying the usage_context_id column as
the parent of a row in the ALL_IDENTIFIERS
view, to see the hierarchy of identifiers
shown in Listing 2.
USING A SIGNATURE TO DIFFERENTIATE
BE T WEEN IDENTIFIERS
It’s always been possible to search for occurrences of strings in source code by querying
the contents of the ALL_SOURCE view. It is
also possible to use the same name for different elements in your code. You can, for
example, use the name of a subprogram for
the name of a variable within that subprogram. Here’s an example:
PROCEDURE plscope_demo_proc
IS
plscope_demo_proc NUMBER;
BEGIN
DECLARE
plscope_demo_proc EXCEPTION;
BEGIN
RAISE plscope_demo_proc;
END;
plscope_demo_proc := 1;
END plscope_demo_proc;
This is very confusing, but it is certainly
valid PL/SQL code. And it would be very
difficult to distinguish between usages of
the same name with a search through the
ALL_SOURCE view.
Code Listing 1: Defining the plscope_demo package
CREATE OR REPLACE PACKAGE plscope_demo
IS
PROCEDURE my_procedure (param1_in IN INTEGER
, param2 IN employees.last_name%TYPE
);
END plscope_demo;
/
CREATE OR REPLACE PACKAGE BODY plscope_demo
IS
PROCEDURE my_procedure (param1_in IN INTEGER
, param2 IN employees.last_name%TYPE
)
IS
c_no_such CONSTANT NUMBER := 100;
l_local_variable NUMBER;
BEGIN
IF param1_in > l_local_variable
THEN
DBMS_OUTPUT.put_line (param2);
ELSE
DBMS_OUTPUT.put_line (c_no_such);
END IF;
END my_procedure;
END plscope_demo;
/
Code Listing 2: Querying against ALL_IDENTIFIERS view to see the hierarchy of identifiers
WITH plscope_hierarchy
AS (SELECT line
, col
, name
, TYPE
, usage
, usage_id
, usage_context_id
FROM all_identifiers
WHERE owner = USER
AND object_name = 'PLSCOPE_DEMO'
AND object_type = 'PACKAGE BODY')
SELECT LPAD (' ', 3 (LEVEL - 1))
|| TYPE
|| ' '
|| name
|| ' ('
|| usage
|| ')'
identifier_hierarchy
FROM plscope_hierarchy
START WITH usage_context_id = 0
CONNECT BY PRIOR usage_id = usage_context_id
ORDER SIBLINGS BY line, col
PACKAGE PLSCOPE_DEMO (DEFINITION)
