APEX Data Dictionary - Der View APEX_DICTIONARY

Alle APEX-Views anzeigen:

select * from apex_dictionary where column_id = 0;
--Alternativ
select distinct apex_view_name from apex_dictionary;

Views als Baumstruktur anzeigen:

with apex_dict_tables as (
    select apex_view_name, parent_view
    from apex_dictionary 
    where comment_type = 'View'
)
select 
    lpad(' ',level * 2) || '| ' ||apex_view_name view_name,
    level
from apex_dict_tables
start with apex_view_name = 'APEX_APPLICATIONS' 
connect by (parent_view = prior apex_view_name and level < 10);

Komponentenübersicht:

with apex_app_structure as (
    select 
        application_id          app_id,
        'APPLICATION'           comp_id,
        to_char(application_id) comp_name,
        null                    parent_comp_id,
        'APPLICATION'           comp_type,
        null                    comp_subtype,
        null                    comp_source
    from apex_applications
    union all
    select 
        p.application_id   app_id,
       'PAGE_' || page_id comp_id,
       to_char(page_id)   comp_name,
       'APPLICATION'      parent_comp_id,
       'PAGE'             comp_type,
       null               comp_subtype,
       null               comp_source
    from apex_application_pages p 
    join apex_applications a on p.application_id = a.application_id
    union all
    select 
        r.application_id                         app_id,
        'REG_P' || page_id || '_' || region_name comp_id,
        region_name                              comp_name,
        'PAGE_' || page_id                       parent_comp_id,
        'REGION'                                 comp_type,
        source_type                              comp_subtype,
        null                                     comp_source
    from apex_application_page_regions r
    join apex_applications a on a.application_id = r.application_id
    union all
    select 
        r.application_id                        app_id,
        'ITEM_P' || page_id || '_' || item_name comp_id,
        item_name                               comp_name,
        'PAGE_' || page_id                      parent_comp_id,
        'ITEM'                                  comp_type,
        display_as                              comp_subtype,
        item_source_type                        comp_source
    from apex_application_page_items r 
    join apex_applications a on a.application_id = r.application_id
    union all
    select 
        r.application_id                          app_id,
       'PROC_' || page_id || '_' || process_name comp_id,
       process_name                              comp_name,
       'PAGE_' || page_id                        parent_comp_id,
       'PROCESS'                                 comp_type,
       process_type                              comp_subtype,
       null                                      comp_source
    from apex_application_page_proc r
    join apex_applications a on a.application_id = r.application_id
)

, myapp as (
  select * from apex_app_structure where app_id=:APP_ID
) 
select lpad(' ',(level-1) * 2) || comp_type ||': '||comp_name || ' ('||comp_subtype||')' component
from myapp
start with comp_id='APPLICATION'
connect by parent_comp_id = prior comp_id
order siblings by app_id, comp_type;

--Alternativ: Anzahl Komponenten
/* 
select 
  comp_type,
  comp_subtype,
  count(comp_name) anzahl
from apex_app_structure
where app_id = :APP_ID
group by rollup (comp_type, comp_subtype)
*/