Wednesday, October 31, 2007

Apex: impact analysis script

Ever wondered where a particular package is being used by your Apex application(s)? This simpleSQL Plus script may be of use:



column obj_name format a50

undef search_text

accept search_text prompt "Enter search text: "

select application_id, page_id, 'Region' objtype, region_name obj_name
from apex_application_page_regions
where lower(region_source) like '%&search_text.%'
UNION ALL
select application_id, page_id, 'Item' obj_type, item_name obj_name
from apex_application_page_items
where lower(item_source) like '%&search_text.%'
UNION ALL
select application_id, page_id, 'Process' obj_type, process_name obj_name
from apex_application_page_proc
where lower(process_source) like '%&search_text.%'
UNION ALL
select application_id, page_id, 'Branch' obj_type, TO_CHAR(process_sequence) obj_name
from apex_application_page_branches
where lower(branch_action) like '%&search_text.%'
ORDER BY 1,2,3,4
/


For example:

Enter search text: my_package.my_fun

APPLICATION_ID PAGE_ID OBJTYPE OBJ_NAME
-------------- ---------- ------- ------------------------------------------
101 123 Process Call my function
101 127 Branch 30
102 128 Region My Report
102 129 Region My Query
103 21 Item P21_MY_ITEM


Feel free to tell me if (a) I have missed anything, or (b) I have re-invented the wheel and should be using some built-in Apex utility!