rp0428 wrote:And? It also says:
Then you should already know that the comments at the start of the 'utldtree.sql' script has instructions for how to use it and has sample queries to get the chain. One of those sample queries is for a package body.
Rem This procedure, view and temp table will allow you to see allWhile OP wants package procedure depndency on other package procedures. And since package procedure is not an object, 'utldtree.sql' will not help here.
Rem <font size=3 color=red>objects</font> that are (recursively) dependent on the given <font size=3 color=red>object</font>.
You use it the same way you use the original. So using your sample packages as an example
-- the procedure CREATE procedure deptree_fill_reverse (type char, schema char, name char) is obj_id number; obj_level number; obj_count number; begin delete from deptree_temptab; commit; select object_id into obj_id from all_objects where owner = upper(deptree_fill_reverse.schema) and object_name = upper(deptree_fill_reverse.name) and object_type = upper(deptree_fill_reverse.type); insert into deptree_temptab values(obj_id, 0, 0, deptree_seq.nextval); commit; obj_level := 0; loop select count(*) into obj_count from public_dependency pd where object_id in (select object_id from deptree_temptab where nest_level = obj_level); if obj_count = 0 then exit; end if; insert into deptree_temptab select pd.referenced_object_id, dt.seq#, obj_level + 1, deptree_seq.nextval from public_dependency pd, deptree_temptab dt where (dt.nest_level = obj_level) and (dt.object_id = pd.object_id); commit; obj_level := obj_level + 1; end loop; exception when no_data_found then raise_application_error(-20000, 'ORU-10013: ' || type || ' ' || schema || '.' || name || ' was not found.'); end; -- the view CREATE OR REPLACE VIEW IDEPTREE_REVERSE AS select max(substr(lpad(' ', 6 * dtr.nest_level) || to_char(dtr.nest_level) || '. ' || uo.object_name, 1, 100)) object_name, max(uo.object_type) object_type, max(uo.status) object_status from (select nest_level, object_id, referenced_object_id, rownum seq_num from deptree_temptab connect by prior seq# = referenced_object_id start with nest_level = 0) dtr, all_objects uo where dtr.object_id = uo.object_id group by dtr.seq_num
If you need procedure/function references within packages then in 11g you can use the new PL/SCOPE functionality and get the equivalent of a cross reference.
execute deptree_fill_reverse('PACKAGE BODY', 'SCOTT', 'PACK2'); select * from ideptree_reverse; OBJECT_NAME,OBJECT_TYPE,OBJECT_STATUS 0. PACK2,PACKAGE BODY,VALID 1. PACK2,PACKAGE,VALID 1. PACK4,PACKAGE,VALID execute deptree_fill_reverse('PACKAGE BODY', 'SCOTT', 'PACK1'); select * from ideptree_reverse; OBJECT_NAME,OBJECT_TYPE,OBJECT_STATUS 0. PACK1,PACKAGE BODY,VALID 1. PACK1,PACKAGE,VALID 1. PACK3,PACKAGE,VALID 1. PACK2,PACKAGE,VALID
rp0428 wrote:I don't have experience with PL/Scope, but as fas as I understand, it can tell where (line) in package standalone procedure/package procedure was called. We would still have to figure out what package procedure that line belongs to.
Seems a lot of developers don't yet know about this. See Chap 8 Using PL/Scope in the Advanced Appications Developer's Guide
ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL'; alter package pack4 compile alter package pack3 compile alter package pack2 compile alter package pack1 compile select * from user_identifiers WITH v AS ( SELECT Line, Col, INITCAP(NAME) Name, LOWER(TYPE) Type, LOWER(USAGE) Usage, USAGE_ID, USAGE_CONTEXT_ID, line lineno FROM USER_IDENTIFIERS WHERE Object_Name = 'PACK1' AND Object_Type = 'PACKAGE BODY' ) SELECT RPAD(LPAD(' ', 2*(Level-1)) || Name, 20, '.')||' '|| RPAD(Type, 20)|| RPAD(Usage, 20) IDENTIFIER_USAGE_CONTEXTS, lineno FROM v START WITH USAGE_CONTEXT_ID = 0 CONNECT BY PRIOR USAGE_ID = USAGE_CONTEXT_ID ORDER SIBLINGS BY Line, Col /