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!
4 comments:
Tony,
Very useful, thanks.
John.
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.
Patrick
Patrick,
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,
Tony
Ok, I see.
Maybe you could upload your application to apex.oracle.com 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.
Patrick
Post a Comment