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
/

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!

Apex 3.0

I have finally got access to the Apex 3.0 evaluation instance, after some problems reading the email containing my login credentials. It looks good. The "What's New in Apex 3.0" document details lots of interesting new features; however there a couple of things on my "wish list" that are not mentioned there, but have been added. These are not very exciting, but assist the developer:

• Region Static ID: now you can give your regions an identifier of your choice, rather than having to refer to Apex region IDs like R17346384974630405 in your code. Not only can your own identifiers be easier to read and remember, they won't change when you copy the page.
• #BUTTON_ID#: this new substitution variable means you can reference the button ID in the button template, and so enable manipulation of the button via Javascript.

Up to now I have devised my own work-arounds for these, but these will make life simpler in future.

No doubt I'll stumble across other goodies as I spend more time playing with, er I mean "evaluating", 3.0