After writing my own scripts to find either all parents or children of an object, I took a look at Oracle’s solution to find dependencies:
$ORACLE_HOME/rdbms/admin/utldtree.sql
It was actually written about 20 years ago, in 1991:
Rem rkooi 10/19/91 – Creation
Running the script, creates a number of objects:
OBJECT_AND_OBJECT_ID ---------------------------------------------------------------------------------------------------- PROCEDURE SYS.DEPTREE_FILL 3304377 SEQUENCE SYS.DEPTREE_SEQ 3304375 TABLE SYS.DEPTREE_TEMPTAB 3304376 VIEW SYS.DEPTREE 3304378 VIEW SYS.IDEPTREE 3304379 7 rows selected. desc DEPTREE_FILL PROCEDURE DEPTREE_FILL Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- TYPE CHAR IN SCHEMA CHAR IN NAME CHAR IN desc ideptree Name Null? Type ----------------------------------------------------- -------- ------------------------------------ DEPENDENCIES VARCHAR2(4000) desc deptree Name Null? Type ----------------------------------------------------- -------- ------------------------------------ NESTED_LEVEL NUMBER TYPE VARCHAR2(19) SCHEMA VARCHAR2(30) NAME VARCHAR2(1002) SEQ# NUMBER
To use these objects, you are then supposed to run the procedure, DEPTREE_FILL:
begin DEPTREE_FILL ( 'PACKAGE BODY' , 'APPS' , 'FND_GLOBAL' ) ; end; The procedure will populate the temporary TABLE SYS.DEPTREE_TEMPTAB. ---- Then, retrieve from the view: select * from ideptree; DEPENDENCIES ---------------------------------------------------------------------------------------------------- CURSOR ."begin fnd_global.initialize(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12, :13, :14, :1 5, :16, :17, :18); end; " PACKAGE BODY APPS.FND_GLOBAL 2 rows selected. select * from deptree; NESTED_LEVEL TYPE SCHEMA ------------ ------------------- ------------------------------ NAME ---------------------------------------------------------------------------------------------------- SEQ# ---------- 0 PACKAGE BODY APPS FND_GLOBAL 0 ... 1 CURSOR "SELECT Q.RESOURCE_CONSUMER_GROUP FROM FND_CONCURRENT_REQUESTS R, FND_CONCURRENT_PROCESSES P, FND_CO NCURRENT_QUEUES Q WHERE R.REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID AND R.CONTROLLING_MANAGER = P.CONC URRENT_PROCESS_ID AND Q.CONCURRENT_QUEUE_ID= P.CONCURRENT_QUEUE_ID AND Q.APPLICATION_ID = P.QUEUE_AP PLICATION_ID" .5 1 CURSOR "BEGIN :1:=FND_GLOBAL.USER_ID; :2:=FND_GLOBAL.RESP_ID; :3:=FND_GLOBAL.RESP_APPL_ID; :4:=FND_GLOB AL.SECURITY_GROUP_ID; :5:=FND_GLOBAL.LOGIN_ID; :6:=FND_GLOBAL.NLS_LANGUAGE; :7:=FND_GLOBAL.NLS_DA TE_FORMAT; :8:=FND_GLOBAL.NLS_DATE_LANGUAGE; :9:=FND_GLOBAL.NLS_TERRITORY; :10:=FND_GLOBAL.NLS_NU MERIC_CHARACTERS; :11:=FND_GLOBAL.NLS_SORT;END;" .5 14 rows selected.
——————–
Some interesting observations. On this package body, it finds shared cursors!
————————–
When I run the DEPTREE_FILL procedure for the Package, FND_GLOBAL, not the Package Body, I get over 70K rows.
begin DEPTREE_FILL ( 'PACKAGE' , 'APPS' , 'FND_GLOBAL' ) ; end; ----- select * from ideptree ... TRIGGER APPS.MRP_SN_WIP_OPR_RES_INSTS_T1 VIEW APPS.MRP_AP_SUPPLIER_CAPACITIES_V VIEW APPS.MRP_AP_SUPPLIER_FLEX_FENCES_V VIEW APPS.MRP_AP_PO_REQ_SUPPLY_V VIEW APPS.MRP_AP_REPAIR_TRANSFERS_RESV_V VIEW APPS.MRP_AP_SUB_INVENTORIES_NEW_V PACKAGE BODY APPS.PAY_COST_ALLOCATION_SWI PACKAGE BODY APPS.HRDPP_UPDATE_AU_EMP_ASG PACKAGE BODY APPS.HRDPP_UPDATE_AU_EMP_ASG PACKAGE BODY APPS.HRDPP_UPDATE_AU_PERSONAL_PAY_M PACKAGE BODY APPS.HRDPP_UPDATE_AU_PERSON CURSOR ."declare b boolean; retval VARCHAR2(1);begin b := fnd_concurrent.set_interi m_status(:1,:2); if ( b ) then retval := 'T'; else retval := 'F'; end if; :3 := retval; end; " 70483 rows selected. ----- select * from deptree ... NESTED_LEVEL TYPE SCHEMA ------------ ------------------- ------------------------------ NAME ---------------------------------------------------------------------------------------------------- SEQ# ---------- ... 3 PACKAGE BODY APPS HRDPP_MAINTAIN_SUPER_INFO 27800 3 PACKAGE BODY APPS HRDPP_CREATE_PAYE_TAX_INFO 27801 3 PACKAGE BODY APPS HRDPP_CREATE_SUPER_CONTRIBUTIO 27802 3 PACKAGE BODY APPS HRDPP_UPDATE_SUPER_CONTRIBUTIO 27803 ... 2 CURSOR "SELECT Fnd_Profile.value('ICX_CLIENT_IANA_ENCODING') FROM DUAL" 57865.5 2 CURSOR "SELECT Fnd_Profile.value('JTF_FM_ERROR_LOG') FROM DUAL" 57865.5 70541 rows selected. ----------------- Objects can be duplicated in the output many times. select SCHEMA || '.' || NAME AS the_object , count(*) from deptree group by SCHEMA || '.' || NAME having count(*) > 1 order by 2 ... APPS.OKL_CREATE_KLE_PVT 139 APPS.OKL_OPEN_INTERFACE_PVT 163 APPS.OKL_VENDOR_AGREEMENT_PUB 216 APPS.BEN_PLAN_DESIGN_DELETE_API 237 12544 rows selected.
—————-
In other words, APPS.BEN_PLAN_DESIGN_DELETE_API appeared in the output 237 times! Not useful if you want to get the order of operations, requiring that it only appear once.
——————
What does the raw data look like?
select * from ideptree where DEPENDENCIES like '%BEN_PLAN_DESIGN_DELETE_API%' ... PACKAGE BODY APPS.BEN_PLAN_DESIGN_DELETE_API PACKAGE BODY APPS.BEN_PLAN_DESIGN_DELETE_API PACKAGE BODY APPS.BEN_PLAN_DESIGN_DELETE_API 237 rows selected. ------------------ What's the duplicate ratio? select count( distinct SCHEMA || '.' || NAME ) as distinct_count , count(*) as total_count , (count(*) / count( distinct SCHEMA || '.' || NAME ) ) as Duplicate_Ratio from deptree DISTINCT_COUNT TOTAL_COUNT DUPLICATE_RATIO -------------- ----------- --------------- 26477 70541 2.66423688 ----------------
My thoughts on DEPTREE are:
– needs to create five objects before use
– is a two or three stage process
– produces many duplicate rows
– only finds the children of an object, not parents
– can find shared cursor dependencies
Summary: It was a good start, for 1991. But I prefer my own scripts instead.
As you know, DBA_DEPENDENCIES only go one layer up or down.
So, if you have multiple layers, you have to do recursive queries.
I’ve since been looking at solving this problem using the graph database, Neo4J. See one initial article here:
[…] I’m trying to find the solution for this on the net, I came across the following link https://rodgersnotes.wordpress.com/2012/01/05/notes-on-deptree/ where he mentioned that, he uses his own script to find the multi level dependencies of an […]