Thursday, November 01, 2007

Apex Impact Analysis script (v2)

After receiving useful comments from Patrick Wolf on my previous post, here is an enhanced version of the script that also checks condition expressions. I'm not including condition_expression2 because I don't think that ever contains anything other than literal values (am I right?)



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, 'Source' usage_type
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, 'Source' usage_type
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, 'Source' usage_type
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, 'Source' usage_type
from apex_application_page_branches
where lower(branch_action) like '%&search_text.%'
UNION ALL
select application_id, page_id, 'Region' obj_type, region_name obj_name, 'Condition' usage_type
from apex_application_page_regions
where lower(condition_expression1) like '%&search_text.%'
UNION ALL
select application_id, page_id, 'Item' obj_type, item_name obj_name, 'Condition' usage_type
from apex_application_page_items
where lower(condition_expression1) like '%&search_text.%'
UNION ALL
select application_id, page_id, 'Item' obj_type, item_name obj_name, 'Read Only' usage_type
from apex_application_page_items
where lower(read_only_condition_exp1) like '%&search_text.%'
UNION ALL
select application_id, page_id, 'Process' obj_type, process_name obj_name, 'Condition' usage_type
from apex_application_page_proc
where lower(condition_expression1) like '%&search_text.%'
UNION ALL
select application_id, page_id, 'Branch' obj_type, TO_CHAR(process_sequence) obj_name, 'Condition' usage_type
from apex_application_page_branches
where lower(condition_expression1) like '%&search_text.%'
ORDER BY 1,2,3,4
/