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.%'
select application_id, page_id, 'Item' obj_type, item_name obj_name
from apex_application_page_items
where lower(item_source) like '%&search_text.%'
select application_id, page_id, 'Process' obj_type, process_name obj_name
from apex_application_page_proc
where lower(process_source) like '%&search_text.%'
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

-------------- ---------- ------- ------------------------------------------
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!


Anonymous said...


Very useful, thanks.


Patrick Wolf said...

Hi Tony,

you should also check the expression1 and expression2 column in most of the views.

BTW, Oracle APEX has an out-of-the-box report which shows the desired result. Have a look at the Database Object Dependencies report.


Tony Andrews said...


Thanks, I will add expression1 & 2 to my query and re-post later. I knew I must have missed something!

Regarding the Dependencies Report in Apex, I must confess I wasn't aware of it until you pointed it out. However, I don't think it does quite what I was after when I wrote my script: I wanted a quick way to find out "where, if anywhere, in my applications (plural) do I call packaged procedure mypkg.myproc?" I don't want to have to go to each application in turn and run a report that takes several minutes (and in my first attempt just now then failed with ORA-00001: unique constraint (FLOWS_030000.WWV_FLOW_COLLECTION_MEMBERS_PK) violated)! And I want a lower granularity of info than package.

Thanks again,


Patrick Wolf said...

Ok, I see.

Maybe you could upload your application to so that Scott could take a look why it fails. Just to get rid of this obvious bug in a future version.

When you want to do a complete check, you should also consider all the other objects (breadcrums, lists, ...) where you can use PL/SQL in a condition. The Lov sql statement, the default value, ... should also not be forgotten.