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!

4 comments:

  1. Tony,

    Very useful, thanks.

    John.

    ReplyDelete
  2. 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

    ReplyDelete
  3. 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

    ReplyDelete
  4. 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

    ReplyDelete