dba_identifiers lists all identifiers that are found in PL/SQL code. This data dictionary view comes in all four variants: user_identifiers, all_identifiers, dba_identifiers and cdb_identifiers.
In order for this view to be populated with the information about identifiers, the PL/SQL compilation parameter plscope_settings must be set to identifiers:all.
dba_identifiers only stores information about basic type names (variable). To determine the exact type of a variable, the information from usage_context_id must be used.
select
--
-- Identification of the identifier being described
--
idt.owner,
idt.name,
idt.type,
--
-- Identification of the PL/SQL object, table or view and the source line and
-- source column where the identifier is found:
--
idt.object_name src_name,
idt.object_type src_type,
idt.line src_line,
idt.col src_col ,
--
-- Identification of the PL/SQL object and location where
-- the identifier being described in the current record
-- is found:
--
idt.declared_object_name ,
idt.declared_object_type ,
idt.declared_owner ,
--
idt.usage,
idt.usage_context_id,
idt.usage_id,
--
idt.attribute,
idt.character_set,
idt.char_used,
--
idt.implicit,
idt.length,
idt.lower_range,
idt.null_constraint,
--
idt.origin_con_id,
idt.precision,
idt.precision2,
idt.scale,
idt.sql_builtin,
idt.upper_range,
--
-- Unique ID of the the «thing» being referred to
-- by the identifier:
--
idt.signature
from
dba_identifiers idt
where
idt.owner = user and
idt.usage = 'DEFINITION' and
idt.object_name = 'TQ84_PKG' and
idt.name = 'PROC_FOO'
;
Column USAGE
The value of the column USAGE is one of
REFERENCE
CALL
ASSIGNMENT
DECLARATION
DEFINITION
Columns DECLARED*
The three columns DECLARED_OWNER, DECLARED_OBJECT_NAME and DECLARED_OBJECT_TYPE identify the PL/SQL object (package, procedure etc.*), the table or the view that is being referred to by the identifier.
Column SIGNATURE
The value of SIGNATURE identifies the the «thing» which the identifer refers to. Hence, there is at most one record with usage = 'DECLARATION' and at most one record with usage = 'DEFINITION' for a given signature:
Both of the following statements should theoretically return a constant count(*) of 1. It turns out, that on my current system, there is one exception (because of a bug in Oracle?):
select count(*), signature from dba_identifiers where usage = 'DEFINITION' group by signature order by count(*) desc;
select count(*), signature from dba_identifiers where usage = 'DECLARATION' group by signature order by count(*) desc;
Column TYPE
The possible values of TYPE can be queried with the following statement:
select
sig.type,
case when count(case when def.signature is not null then 1 end) > 0 then 'x' end has_definition,
count(*) cnt,
count(case when def.signature is not null then 1 end) cnt_with_definition
from
dba_identifiers sig left join
dba_identifiers def on sig.signature = def.signature and def.usage = 'DEFINITION'
group by
sig.type
order by
type;
On my installation, the query returns the following types and indication if a type is associated with a definition:
ASSOCIATIVE ARRAY
BFILE DATATYPE
BLOB DATATYPE
BOOLEAN DATATYPE
CHARACTER DATATYPE
CLOB DATATYPE
COLUMN
CONSTANT
DATE DATATYPE
EXCEPTION
FORMAL IN
x
FORMAL IN OUT
FORMAL OUT
FUNCTION
x
INDEX TABLE
INTERVAL DATATYPE
ITERATOR
LABEL
NESTED TABLE
NUMBER DATATYPE
OBJECT
x
OBJECT ATTRIBUTE
OPAQUE
PACKAGE
x
PROCEDURE
x
RECORD
RECORD FIELD
RECORD ITERATOR
REFCURSOR
SEQUENCE
SUBTYPE
SYNONYM
TABLE
TIME DATATYPE
TIMESTAMP DATATYPE
TRIGGER
x
UROWID
VARIABLE
x
VARRAY
VIEW
USAGE_ID / USAGE_CONTEXT_ID
USAGE_ID specifes a «scope». Records in DBA_IDENTIFIERS that belong to that scope have a CONTEXT_USAGE_ID whose value corresponds to the USAGE_ID of that scope.
The combination of usage_id and usage_context_id is unique within an object, the following statement returns no records:
select
count(*),
owner,
object_name,
object_type,
usage_id,
usage_context_id
from
dba_identifiers
group by
owner,
object_name,
object_type,
usage_id,
usage_context_id
having
count(*) > 1;
Hierarchical query
The following hierarchical query starts at the top of a package (usage_context_id = 0) and recursively finds records (idt.*) that belong to this usage_context_id (idt.usage_context_id = rec.usage_id).
with rec (
usage_id,
name,
type,
usage,
owner,
object_name,
line,
col,
lvl
)
as (
select
usage_id,
name,
usage,
type,
owner,
object_name,
line,
col,
0
from
dba_identifiers
where
owner = 'SYS' and
object_name = 'STANDARD' and
usage_context_id = 0 -- No «scope» has value 0, selects «global» records
UNION ALL
select
idt.usage_id,
idt.name,
idt.usage,
idt.type,
idt.owner,
idt.object_name,
idt.line,
idt.col,
rec.lvl + 1
from
rec join
dba_identifiers idt on
idt.owner = rec.owner and
idt.object_name = rec.object_name and
idt.usage_context_id = rec.usage_id
)
search depth first by usage_id set "_"
select
lpad(' ', lvl * 2) || name name,
line,
col,
usage,
type
from
rec;