Steven Feuerstein has an excellent article on "Use PL/Scope to analyze your PL/SQL". The PL/Scope functionality became available in Oracle11g. Its basic function is to populate the DBA_IDENTIFIERS table with all PL/SQL identifiers found while compiling database objects. Steven's post goes on to show an "Identifier Hierarchy" of variables, parameters, and other components of a particular package body.
Many years after Steven's post, I began supporting some rather complex PL/SQL in Oracle11g. I found a great tool called ClearDB Documenter 4 that would (among other things) display a "PL/SQL Call Tree". This was extremely handy for understanding the relationships between the many packages on the system. However, I wanted a way to get the PL/SQL Call Tree in an SQL report that I could further manipulate. I was able to coax the needed information from DBA_IDENTIFIERS, but it wasn't easy.
At the heart of the problem is a lack of documentation on the contents of DBA_IDENTIFIERS. After some reverse-engineering, I developed these guidelines for the contents of DBA_IDENTIFIERS.
- OWNER, OBJECT_NAME, and OBJECT_TYPE are unique database objects.
- USAGE_ID is a unique location and usage within a unique database object.
- USAGE_CONTEXT_ID points to a USAGE_ID within a unique database object.
- USAGE_CONTEXT_ID = 0 for top level database object "DECLARATION".
- USAGE_ID with "CALL" identifies the calling program location.
- SIGNATURE with "CALL" identifies the called program name and type.
- SIGNATURE with "DECLARATION" is globally unique.
- SIGNATURE with "DEFINITION" can be redefined and may not exist for all objects.
- SIGNATURE is the same for package specification and body.
- DBA_IDENTIFIERS is indexed on OBJ# and SIGNATURE.
- OBJECT_NAME matches NAME and OBJECT_TYPE matches TYPE for functions and procedures that are not in packages.
- "DECLARATION" and "DEFINITION" for synonyms and triggers match OBJECT_NAME with NAME and OBJECT_TYPE with TYPE.
- "DECLARATION" for packages match OBJECT_NAME with NAME and OBJECT_TYPE with TYPE
- "DEFINITION" for packages match OBJECT_NAME with NAME and OBJECT_TYPE = "PACKAGE BODY" and TYPE = "PACKAGE"
UPDATE (28-Mar-2016): Added this section to recompile all source.
Recompile All Source
(Run this as system)ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL';
begin
for buff in (
select username from dba_users
-- where nvl(oracle_maintained,'N' = 'N') -- for 12c
and ( owner not in ('ANONYMOUS', 'APPQOSSYS', 'CTXSYS', 'DBSNMP', 'DIP',
'FLOWS_FILES', 'HR', 'MDSYS', 'ORACLE_OCM',
'ORDS_METADATA', 'ORDS_PUBLIC_USER', 'OUTLN',
'SCOTT', 'SYS', 'SYSTEM', 'XDB', 'XS$NULL')
or owner not like 'APEX\_' escape '\'); -- for 11gXE
) loop
DBMS_UTILITY.compile_schema (
schema => buff.username,
compile_all => TRUE,
reuse_settings => FALSE);
for trig in (
select object_name from dba_objects
where object_type = 'TRIGGER'
and owner = buff.username )
loop
begin
execute immediate 'ALTER TRIGGER ' || buff.username ||
'.' || trig.object_name || ' COMPILE';
exception when others then null;
end;
end loop;
end loop;
end;
/
UPDATE (28-Mar-2016): Added this function to compute un-named PL/SQL block start line in trigger source.
Create Trigger Offset Function
UPDATE (29-Dec-2016): Added this grant for the function below.
(Run this as sys): grant select on dba_source to system with grant option;
create FUNCTION temp_trigger_offset
(dout_name_in in varchar2
,dout_type_in in varchar2
,dout_owner_in in varchar2)
return number
--AUTHID DEFINER
AUTHID CURRENT_USER
IS
BEGIN
if nvl(dout_type_in,'BOGUS') <> 'TRIGGER' then
--dbms_output.put_line('TEMP_TRIGGER_OFFSET: Not a TRIGGER: ' || dout_type_in);
return 0;
end if;
for buff in (
select line, text from dba_source
where name = dout_name_in
and type = dout_type_in
and owner = dout_owner_in
order by line )
loop
if regexp_instr(buff.text,
'(^declare$' ||
'|^declare[[:space:]]' ||
'|[[:space:]]declare$' ||
'|[[:space:]]declare[[:space:]])', 1, 1, 0, 'i') <> 0
OR
regexp_instr(buff.text,
'(^begin$' ||
'|^begin[[:space:]]' ||
'|[[:space:]]begin$' ||
'|[[:space:]]begin[[:space:]])', 1, 1, 0, 'i') <> 0
then
--dbms_output.put_line('TEMP_TRIGGER_OFFSET: ' || buff.line - 1');
return buff.line - 1;
end if;
end loop;
--dbms_output.put_line('TEMP_TRIGGER_OFFSET: Did not find DECLARE or BEGIN');
return 0;
END temp_trigger_offset;
/
UPDATE (13-Jan-2016): In order to capture all cross-schema references, run these statements as SYSTEM or another DBA user. Tables are created with the prefix "TEMP_" in the name. There is a CLEANUP at the bottom.
UPDATE (28-Mar-2016): Added SYSAUX tablespaces, TEMP_DBA_SOURCE table, and table/column comments.
Copy Some Tables
Make a copy of DBA_IDENTIFIERS and index it for speed. (Run this as system)create table temp_dba_identifiers
tablespace SYSAUX
as
select * from dba_identifiers;
create unique index temp_dba_identifiers$uk1
on temp_dba_identifiers (owner, object_name, object_type, usage_id)
tablespace SYSAUX;
create index temp_dba_identifiers$ix1
on temp_dba_identifiers (signature)
tablespace SYSAUX;
comment on table temp_dba_identifiers is
'Static, indexed version of information about the identifiers in all stored objects in the database';
comment on column temp_dba_identifiers.OWNER is
'Required owner of the identifier';
comment on column temp_dba_identifiers.NAME is
'Name of the identifier';
comment on column temp_dba_identifiers.SIGNATURE is
'Signature of the identifier';
comment on column temp_dba_identifiers.TYPE is
'Type of the identifier';
comment on column temp_dba_identifiers.OBJECT_NAME is
'Required name of the object where the identifier action occurred';
comment on column temp_dba_identifiers.OBJECT_TYPE is
'Type of the object where the identifier action occurred';
comment on column temp_dba_identifiers.USAGE is
'Type of the identifier usage: DECLARATION, DEFINITION, CALL, REFERENCE, ASSIGNMENT';
comment on column temp_dba_identifiers.USAGE_ID is
'Unique key for the identifier usage within the object';
comment on column temp_dba_identifiers.LINE is
'Line number of the identifier action';
comment on column temp_dba_identifiers.COL is
'Column number of the identifier action';
comment on column temp_dba_identifiers.USAGE_CONTEXT_ID is
'Context USAGE_ID of the identifier usage';
--comment on column temp_dba_identifiers.ORIGIN_CON_ID is
-- 'The ID of the container where the data originates. Possible values include: 0: This value is used for rows in non-CDBs. This value is not used for CDBs. n: This value is used for rows containing data that originate in the container with container ID n (n = 1 if the row originates in root)'; -- Oracle 12c
Make a copy of DBA_SOURCE and index it for speed.
create table temp_dba_source
tablespace SYSAUX
as
select * from dba_source;
create index temp_dba_source$ix1
on temp_dba_source (owner, name, type, line)
tablespace SYSAUX;
comment on table temp_dba_source is
'Static, indexed version of text source descriptions of all stored objects in the database';
comment on column temp_dba_source.OWNER is
'Required owner of the object';
comment on column temp_dba_source.NAME is
'Required name of the object';
comment on column temp_dba_source.TYPE is
'Type of object: FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY';
comment on column temp_dba_source.LINE is
'Required line number of this line of source';
comment on column temp_dba_source.TEXT is
'Text source of the stored object';
--comment on column temp_dba_source.ORIGIN_CON_ID is
-- 'The ID of the container where the data originates. Possible values include: 0: This value is used for rows in non-CDBs. This value is not used for CDBs. n: This value is used for rows containing data that originate in the container with container ID n (n = 1 if the row originates in root)'; -- Oracle 12c
Make a copy of DBA_DEPENDENCIES and index it for speed.
create table temp_dba_dependencies
tablespace SYSAUX
as
select * from dba_dependencies
where referenced_type in ('TABLE','VIEW','SYNONYM','MATERIALIZED VIEW')
-- and owner not in (select username from dba_users
-- where oracle_maintained = 'Y' ); -- for 12c
and ( owner not in ('ANONYMOUS', 'APPQOSSYS', 'CTXSYS', 'DBSNMP', 'DIP',
'FLOWS_FILES', 'HR', 'MDSYS', 'ORACLE_OCM',
'ORDS_METADATA', 'ORDS_PUBLIC_USER', 'OUTLN',
'SCOTT', 'SYS', 'SYSTEM', 'XDB', 'XS$NULL')
or owner not like 'APEX\_' escape '\'); -- for 11gXE
create index temp_dba_dependencies$ix1
on temp_dba_dependencies (owner, name, type)
tablespace SYSAUX;
create index temp_dba_dependencies$ix2
on temp_dba_dependencies (referenced_owner, referenced_name, referenced_type)
tablespace SYSAUX;
comment on table temp_dba_dependencies is
'Static, indexed version of all dependencies between selected objects (TABLE,VIEW,SYNONYM,MATERIALIZED VIEW) in the database. This view does not display the SCHEMAID column';
comment on column temp_dba_dependencies.OWNER is
'Required owner of the object';
comment on column temp_dba_dependencies.NAME is
'Name of the object';
comment on column temp_dba_dependencies.TYPE is
'Type of the object';
comment on column temp_dba_dependencies.REFERENCED_OWNER is
'Owner of the referenced object (remote owner if remote object)';
comment on column temp_dba_dependencies.REFERENCED_NAME is
'Name of the referenced object';
comment on column temp_dba_dependencies.REFERENCED_TYPE is
'Type of the referenced object';
comment on column temp_dba_dependencies.REFERENCED_LINK_NAME is
'Name of the link to the parent object (if remote)';
comment on column temp_dba_dependencies.DEPENDENCY_TYPE is
'Indicates whether the dependency is a REF dependency (REF) or not (HARD)';
Main Call Tree Table
Create the main call tree table- SRC - identifiers are all about this, the source code.
- TGT - The target of the SRC usage, like a reference or a call
- CTX - The current context of the source code, what SRC this SRC is under
- PAR - The parent context is a function, procedure, or package this CTX is under
tablespace SYSAUX
as
select tgt.owner tgt_object_owner
,tgt.object_type tgt_object_type
,tgt.object_name tgt_object_name
,tgt.name tgt_name
,tgt.type tgt_type
,src.owner src_object_owner
,src.object_type src_object_type
,src.object_name src_object_name
,src.usage par_usage -- BOGUS placed holder data
,src.name par_name -- BOGUS placed holder data
,src.type par_type -- BOGUS placed holder data
,ctx.usage ctx_usage
,ctx.name ctx_name
,ctx.type ctx_type
,src.usage src_usage
,src.name src_name
,src.type src_type
,case when src.object_type = 'TRIGGER'
then src.line + temp_trigger_offset
(src.object_name
,src.object_type
,src.owner )
else src.line end src_line
,src.col src_col
,dbas.text src_text
,tgt.signature tgt_signature
,ctx.signature par_signature
,ctx.signature ctx_signature
,src.signature src_signature
,src.usage_id src_usage_id
from temp_dba_identifiers src
left join temp_dba_identifiers tgt on (tgt.signature = src.signature)
and tgt.usage = 'DECLARATION'
left join temp_dba_identifiers ctx on (ctx.owner = src.owner
and ctx.object_name = src.object_name
and ctx.object_type = src.object_type
and ctx.usage_id = src.usage_context_id)
left join temp_dba_source dbas on (dbas.owner = src.owner
and dbas.type = src.object_type
and dbas.name = src.object_name
and dbas.line = src.line);
create index temp_ident_assocs$ix1
on temp_ident_assocs (tgt_object_name, tgt_object_type, tgt_object_owner)
tablespace SYSAUX;
create index temp_ident_assocs$ix2
on temp_ident_assocs (src_object_owner, src_object_name, src_object_type, src_line)
tablespace SYSAUX;
create index temp_ident_assocs$ix3
on temp_ident_assocs (src_object_name, src_object_type, src_object_owner, src_usage_id)
tablespace SYSAUX;
create index temp_ident_assocs$ix4
on temp_ident_assocs (tgt_signature, src_usage)
tablespace SYSAUX;
create index temp_ident_assocs$ix5
on temp_ident_assocs (src_signature)
tablespace SYSAUX;
create index temp_ident_assocs$ix6
on temp_ident_assocs (ctx_signature)
tablespace SYSAUX;
comment on column temp_ident_assocs.tgt_object_owner is
'Target database object owner that is being called or referenced';
comment on column temp_ident_assocs.tgt_object_type is
'Target database object type that is being called or referenced';
comment on column temp_ident_assocs.tgt_object_name is
'Target database object name that is being called or referenced';
comment on column temp_ident_assocs.tgt_name is
'Target element name (procedure, function, etc.) being called or referenced';
comment on column temp_ident_assocs.tgt_type is
'Target element type (procedure, function, etc.) being called or referenced';
comment on column temp_ident_assocs.src_object_owner is
'Database object owner that is the source of the call or reference';
comment on column temp_ident_assocs.src_object_type is
'Database object type that is the source of the call or reference';
comment on column temp_ident_assocs.src_object_name is
'Database object name that is the source of the call or reference';
comment on column temp_ident_assocs.par_usage is
'Parent context usage (call, reference, etc) of the source of the call or reference';
comment on column temp_ident_assocs.par_name is
'Parent context name of the source of the call or reference';
comment on column temp_ident_assocs.par_type is
'Parent context type of the source of the call or reference';
comment on column temp_ident_assocs.ctx_usage is
'Context usage (call, reference, etc) of the source of the call or reference';
comment on column temp_ident_assocs.ctx_name is
'Context name of the source of the call or reference';
comment on column temp_ident_assocs.ctx_type is
'Context type of source of the call or reference';
comment on column temp_ident_assocs.src_usage is
'Usage (call, reference, etc) of the source of the call or reference';
comment on column temp_ident_assocs.src_name is
'Name of the source of the call or reference';
comment on column temp_ident_assocs.src_type is
'Type of the source of the call or reference';
comment on column temp_ident_assocs.src_line is
'Database object line number of the source of the call or reference';
comment on column temp_ident_assocs.src_col is
'Database object column number of the source of the call or reference';
comment on column temp_ident_assocs.src_text is
'Text of the source of the call or reference';
comment on column temp_ident_assocs.tgt_signature is
'Target signature that is being called or referenced';
comment on column temp_ident_assocs.par_signature is
'Parent context signature of the source of the call or reference';
comment on column temp_ident_assocs.ctx_signature is
'Context signature of the source of the call or reference';
comment on column temp_ident_assocs.src_signature is
'Signature of the source of the call or reference';
comment on column temp_ident_assocs.src_usage_id is
'Unique location and usage within a database object';
Populate as much parent context as possible from the same source line (fast)
update temp_ident_assocs dia
set (par_usage, par_name, par_type, par_signature) = (
select ctx.ctx_usage, ctx.ctx_name, ctx.ctx_type, ctx.ctx_signature
from temp_ident_assocs ctx
where ctx.ctx_type in ('FUNCTION','PACKAGE','PROCEDURE','SYNONYM','TRIGGER')
and ctx.ctx_usage in ('DECLARATION','DEFINITION')
and ctx.src_object_owner = dia.src_object_owner
and ctx.src_object_type = dia.src_object_type
and ctx.src_object_name = dia.src_object_name
and ctx.src_line = dia.src_line
and ctx.src_usage_id = (
select max(mc.src_usage_id) from temp_ident_assocs mc
where mc.ctx_type in ('FUNCTION','PACKAGE','PROCEDURE','SYNONYM','TRIGGER')
and mc.ctx_usage in ('DECLARATION','DEFINITION')
and mc.src_object_owner = dia.src_object_owner
and mc.src_object_type = dia.src_object_type
and mc.src_object_name = dia.src_object_name
and mc.src_line = dia.src_line
and mc.src_usage_id <= dia.src_usage_id ) );
Populate all other parent context from previous source lines (slow)
update temp_ident_assocs dia
set (par_usage, par_name, par_type, par_signature) = (
select ctx.ctx_usage, ctx.ctx_name, ctx.ctx_type, ctx.ctx_signature
from temp_ident_assocs ctx
where ctx.ctx_type in ('FUNCTION','PACKAGE','PROCEDURE','SYNONYM','TRIGGER')
and ctx.ctx_usage in ('DECLARATION','DEFINITION')
and ctx.src_object_owner = dia.src_object_owner
and ctx.src_object_type = dia.src_object_type
and ctx.src_object_name = dia.src_object_name
and ctx.src_usage_id = (
select max(mc.src_usage_id) from temp_ident_assocs mc
where mc.ctx_type in ('FUNCTION','PACKAGE','PROCEDURE','SYNONYM','TRIGGER')
and mc.ctx_usage in ('DECLARATION','DEFINITION')
and mc.src_object_owner = dia.src_object_owner
and mc.src_object_type = dia.src_object_type
and mc.src_object_name = dia.src_object_name
and mc.src_usage_id <= dia.src_usage_id ) )
where par_signature is null;
Add the missing context for declarations and definitions
update temp_ident_assocs
set ctx_usage = src_usage
,ctx_name = src_name
,ctx_type = src_type
,ctx_signature = src_signature
,par_usage = src_usage
,par_name = src_name
,par_type = src_type
,par_signature = src_signature
where src_usage in ('DECLARATION','DEFINITION')
and ctx_signature is null
and tgt_signature = src_signature;
This appears to be a bug in PLSCOPE because some context_usage_id's don't exist. So, we will fix that.
update temp_ident_assocs dia
set ctx_usage = par_usage
,ctx_name = par_name
,ctx_type = par_type
,ctx_signature = par_signature
where ctx_signature is null
and par_signature is not null;
Create the last index on temp_ident_assocs
create index temp_ident_assocs$ix7
on temp_ident_assocs (par_signature, src_usage);
Call Tree Query
Done! Now to run a PL/SQL Call Tree Query. Pick a function or procedure in a package that calls other functions and/or procedures. Run the following SQL with ":OWNER" as the package owner, ":PKG_NAME" as the package name, and ":PROC_NAME" as the function or procedure name. (Updated SQL 04-Jun-2015).with par as (
select * from temp_ident_assocs
where src_object_owner = upper(:OWNER)
and src_object_name = upper(:PKG_NAME)
and src_name = upper(:PROC_NAME)
and src_usage = 'DECLARATION'
), nodes as (
select tgt_signature
,par_signature
,src_object_owner calling_owner
,src_object_name || '.' ||
par_name calling_name
,par_type calling_type
,tgt_object_owner called_owner
,tgt_object_name || '.' ||
tgt_name called_name
,tgt_type called_type
,src_line
,src_text
from temp_ident_assocs
where src_usage = 'CALL'
and tgt_type != 'CURSOR'
and tgt_object_owner != 'SYS'
)
--select * from par;
--select * from nodes;
select calling_owner
,LPAD('-| ', (Level-1)*3, '-|-') || calling_name calling_name
,src_line
,called_type
,called_name
,called_owner
from nodes
START WITH par_signature in (select src_signature from par)
CONNECT BY NOCYCLE par_signature = PRIOR tgt_signature
order siblings by src_line;
Reverse Call Tree Query
We can also run a reverse call tree query. Pick a function or procedure in a package that is called by other functions and/or procedures. Again, set the ":OWNER" as the package owner, ":PKG_NAME" as the package name, and ":PROC_NAME" as the function or procedure name. (Updated SQL 04-Jun-2015)with tgt as (
select * from temp_ident_assocs
where src_object_owner = upper(:OWNER)
and src_object_name = upper(:PKG_NAME)
and src_name = upper(:PROC_NAME)
and src_usage = 'DECLARATION'
), nodes as (
select tgt_signature
,par_signature
,src_object_owner calling_owner
,src_object_name || '.' ||
par_name calling_name
,par_type calling_type
,tgt_object_owner called_owner
,tgt_object_name || '.' ||
tgt_name called_name
,tgt_type called_type
,src_line
,src_text
from temp_ident_assocs
where src_usage = 'CALL'
and tgt_type != 'CURSOR'
and tgt_object_owner != 'SYS'
)
--select * from tgt;
--select * from nodes;
select rownum
,called_owner
,substr(SYS_CONNECT_BY_PATH(called_name, ' <- '),4) || ' <-'
called_path
,calling_name
,calling_type
,calling_owner
,src_line
,src_text
from nodes
START WITH tgt_signature in (select src_signature from tgt)
CONNECT BY NOCYCLE tgt_signature = PRIOR par_signature
order siblings by src_line;
BONUS: Procedure/Function Lines in a Package
UPDATE (28-Mar-2016): Modified and renamed this table to use line/column position instead of line.Ever wondered how to determine which DBA_SOURCE lines belong to a procedure/function in a package? This table does that...
create table temp_proc_pos
tablespace SYSAUX
as
select src_object_owner object_owner
,src_object_name object_name
,src_object_type object_type
,par_name proc_name
,par_type proc_type
,src_signature proc_signature
,src_line beg_line
,src_line end_line
,src_line beg_pos
,src_line end_pos
from temp_ident_assocs
where 0 = 1;
DECLARE
cursor main is
select src_object_owner, src_object_name, src_object_type,
par_name, par_type, par_signature, src_line, src_col
from temp_ident_assocs
where src_usage in ('DECLARATION','DEFINITION')
and src_object_type not in ('PACKAGE','TYPE','SYNONYM')
order by src_object_owner, src_object_name, src_object_type,
src_line, src_col;
type main_nt_type is table of main%ROWTYPE;
prev main%ROWTYPE;
curr main%ROWTYPE;
cnt number;
recs number;
function get_last_oline return number is
oline number;
begin
select max(line) into oline from temp_dba_source
where owner = prev.src_object_owner
and name = prev.src_object_name
and type = prev.src_object_type;
return oline;
end get_last_oline;
function get_last_ocol (in_last_oline in number) return number is
ocol number;
begin
select length(text) into ocol from temp_dba_source
where owner = prev.src_object_owner
and name = prev.src_object_name
and type = prev.src_object_type
and line = in_last_oline;
return ocol;
end get_last_ocol;
procedure ins_rec (in_end_line in number, in_end_col in number) is
lv_beg_pos number;
lv_end_pos number;
lv_end_line number;
lv_end_col number;
begin
IF in_end_col = 1 THEN
lv_end_line := in_end_line - 1;
lv_end_col := get_last_ocol(lv_end_line);
ELSE
lv_end_line := in_end_line;
lv_end_col := in_end_col - 1;
END IF;
lv_beg_pos := prev.src_line + prev.src_col/1000000;
lv_end_pos := lv_end_line + lv_end_col/1000000;
if lv_beg_pos < lv_end_pos then
insert into temp_proc_pos
(object_owner
,object_name
,object_type
,proc_name
,proc_type
,proc_signature
,beg_line
,end_line
,beg_pos
,end_pos)
values
(prev.src_object_owner
,prev.src_object_name
,prev.src_object_type
,prev.par_name
,prev.par_type
,prev.par_signature
,prev.src_line
,lv_end_line
,lv_beg_pos
,lv_end_pos);
recs := recs + 1;
end if;
end ins_rec;
BEGIN
recs := 0;
cnt := 0;
open main;
fetch main into curr;
prev := curr;
WHILE main%FOUND LOOP
IF prev.src_object_owner != curr.src_object_owner OR
prev.src_object_name != curr.src_object_name OR
prev.src_object_type != curr.src_object_type
THEN
ins_rec(get_last_oline, get_last_ocol(get_last_oline)+1);
prev := curr;
ELSIF prev.par_signature != curr.par_signature
THEN
ins_rec(curr.src_line, curr.src_col);
prev := curr;
END IF;
cnt := cnt + 1;
fetch main into curr;
END LOOP;
if prev.src_object_name is not null then
ins_rec(get_last_oline, get_last_ocol(get_last_oline)+1);
end if;
close main;
dbms_output.put_line('cnt = '||cnt||', recs = '||recs);
END;
/
create index temp_proc_pos$ix1
on temp_proc_pos(object_owner, object_name, object_type, beg_pos)
tablespace SYSAUX;
comment on table temp_proc_pos is
'Static, indexed version of procedure and function locations within source code in the database.';
comment on column temp_proc_pos.object_owner is
'Database source object owner';
comment on column temp_proc_pos.object_type is
'Database source object type';
comment on column temp_proc_pos.object_name is
'Database source object name';
comment on column temp_proc_pos.proc_name is
'Procedure or function name';
comment on column temp_proc_pos.proc_type is
'Procedure or function type';
comment on column temp_proc_pos.proc_signature is
'Procedure or function signature from ALL_IDENTIFIERS';
comment on column temp_proc_pos.beg_line is
'Beginning line number in the source';
comment on column temp_proc_pos.end_line is
'Ending line number in the source';
comment on column temp_proc_pos.beg_pos is
'Beginning position in the source. Position is line number + (column position/1000000)';
comment on column temp_proc_pos.end_pos is
'Ending position in the source. Position is line number + (column position/1000000)';
DOUBLE BONUS: Tables used by functions and procedures
UPDATE (31-Mar-2016): Modified data in this table to use line/column position instead of line.How about matching database tables with functions and procedures? The following script creates a table that does, almost. It uses a simple string match, so it may return extra source lines that don't reference a database table. The source text is included to determine if it is not a table.
create table temp_referenced_tabs
tablespace SYSAUX
as
select proc.object_owner object_owner
,proc.object_name object_name
,proc.object_type object_type
,proc.proc_name proc_name
,proc.proc_type proc_type
,proc.proc_signature proc_signature
,proc.object_owner referenced_owner
,proc.object_name referenced_name
,proc.object_type referenced_type
,src.line line
,src.line col
,src.text text
from temp_proc_pos proc cross join temp_dba_source src
where 1 = 0;
DECLARE
fnd_col number;
BEGIN
for procs in (
select * from temp_proc_pos
order by object_owner, object_name, object_type, beg_pos)
loop
for deps in (
select * from temp_dba_dependencies dep
where dep.owner = procs.object_owner
and dep.name = procs.object_name
and dep.type = procs.object_type )
loop
for srcs in (
select * from temp_dba_source src
where src.owner = procs.object_owner
and src.name = procs.object_name
and src.type = procs.object_type
and src.text is not null
and src.line between trunc(procs.beg_pos)
and trunc(procs.end_pos) )
loop
fnd_col :=
regexp_instr
(srcs.text
,'[[:space:],]'||deps.referenced_name||'[[:space:],]' || '|' ||
'[[:space:],]'||deps.referenced_name||'$' || '|' ||
'^'||deps.referenced_name||'[[:space:],]' || '|' ||
'^'||deps.referenced_name||'$'
,1,1,0,'i');
if fnd_col > 0 AND
(srcs.line + fnd_col/1000000) between procs.beg_pos
and procs.end_pos
then
insert into temp_referenced_tabs
(object_owner
,object_name
,object_type
,proc_name
,proc_type
,proc_signature
,referenced_owner
,referenced_name
,referenced_type
,line
,col
,text)
values
(procs.object_owner
,procs.object_name
,procs.object_type
,procs.proc_name
,procs.proc_type
,procs.proc_signature
,deps.referenced_owner
,deps.referenced_name
,deps.referenced_type
,srcs.line
,fnd_col
,srcs.text);
end if;
end loop;
end loop;
commit;
end loop;
END;
/
create index temp_referenced_tabs$ix1
on temp_referenced_tabs (object_name, object_type, object_owner)
tablespace SYSAUX;
create index temp_referenced_tabs$ix2
on temp_referenced_tabs (referenced_name, referenced_type, referenced_owner)
tablespace SYSAUX;
comment on table temp_referenced_tabs is
'Static, indexed version of procedure and function references to table-like objects in the database.';
comment on column temp_referenced_tabs.object_owner is
'Database source object owner';
comment on column temp_referenced_tabs.object_type is
'Database source object type';
comment on column temp_referenced_tabs.object_name is
'Database source object name';
comment on column temp_referenced_tabs.proc_name is
'Procedure or function name';
comment on column temp_referenced_tabs.proc_type is
'Procedure or function type';
comment on column temp_referenced_tabs.proc_signature is
'Procedure or function signature from ALL_IDENTIFIERS';
comment on column temp_referenced_tabs.referenced_owner is
'Database source object owner of the object being directly referenced';
comment on column temp_referenced_tabs.referenced_type is
'Database source object type of the object being directly referenced';
comment on column temp_referenced_tabs.referenced_name is
'Database source object name of the object being directly referenced';
comment on column temp_referenced_tabs.line is
'Line number in the source';
comment on column temp_referenced_tabs.col is
'Column number in the source';
comment on column temp_referenced_tabs.text is
'Text of the source';
Cleanup
drop table temp_referenced_tabs;drop table temp_proc_pos;
drop table temp_ident_assocs;
drop table temp_dba_dependencies;
drop table temp_dba_source;
drop table temp_dba_identifiers;
drop function temp_trigger_offset;
Olympic 10 pointer last step?
ReplyDeletehttp://rwijk.blogspot.ch/2008/10/dbadependencycolumns.html
My apologies to "coffeespoons". I had not been checking my SPAM folder. I am not sure what "Olympic 10" references, but the link provided includes more information from "rwijk" about column dependencies and dependency types. Many Thanks!
DeleteHello,
ReplyDeletegreat post !
I really had a hard time before seeing your post to do this, so thanks for the work.
For the Call Tree Query, as the tree concerns only the functions who call other 'things', the tree is not complete, we don't have the leaf of the tree..
I tried to do this by using concatenate this way :
DECODE(CONNECT_BY_ISLEAF,1,CHR(10)||' --> ' || called_name,'')
It works but in SQL Developper you can't see the carriage return.
I can't manage to make the leaves appear on the tree :(
Thanks,
Adel
Adelz, Thank you for the feedback. You are correct. The "CALLING_NAME" column contains branch names and the "CALLED_NAME" contains the leaf names. Because calls can occur to other schema, a full tree should have the owner included in the name. The name of each node gets very long, so I chose not to include it. You might try the following:
Delete1) Don't use CONNECT_BY_ISLEAF. Simply concatenate the CALLED_NAME.
2) Change (Level-1) to (Level) in the "main query"
3) Add the needed columns to the "par" sub-query and union it in the "main query" to create the root node/branch.