Thursday, 7 June 2007

Have a clean up, utilising the APEX repository

Introduction...
Remember switching those conditions to 'Never' on various APEX components and forgotten where and when? Use the awesome APEX repository to have a tidy up.


How...
The following query will return all of the following APEX components that have a condition set to 'Never':
Breadcrumbs, computations, list entries, list of values, navigation bars entries, branches, processes, regions, report columns, validations, buttons, items, tabs and shortcuts.

The query allows for either viewing components by application, or by workspace. To view by application enter the application ID for the app_id bind variable, to view all applications in a workspace just leave app_id as null.
select
component,
description,
sequence,
location,
identifier,
app_id
from
(
select
'Breadcrumb Entry' component,
entry_label description,
null sequence,
'Page: ' || defined_for_page location,
breadcrumb_id identifier,
application_id app_id
from APEX_APPLICATION_BC_ENTRIES
where upper(condition_type) = 'NEVER'
union
select
'Application Computation' component,
computation_item description,
computation_sequence sequence,
'Shared Component - Logic' location,
application_comment_id identifier,
application_id app_id
from APEX_APPLICATION_COMPUTATIONS
where upper(condition_type) = 'NEVER'
union
select
'List Entry' component,
entry_text description,
display_sequence sequence,
'Shared Component - Navigation - List (' ||
list_name || ')' location,
list_entry_id identifier,
application_id app_id
from APEX_APPLICATION_LIST_ENTRIES
where upper(condition_type) = 'NEVER'
union
select
'LOV Entry' component,
display_value description,
display_sequence sequence,
'Shared Component - User Interface - LOV (' ||
list_of_values_name location,
lov_entry_id identifier,
application_id app_id
from APEX_APPLICATION_LOV_ENTRIES
where upper(condition_type) = 'NEVER'
union
select
'Navigation Bar Entry' component,
icon_subtext description,
display_sequence sequence,
'Shared Component - Navigation' location,
nav_bar_id identifier,
application_id app_id
from APEX_APPLICATION_NAV_BAR
where upper(condition_type) = 'NEVER'
union
select
'Page Branch' component,
branch_point description,
process_sequence sequence,
'Page: ' || page_id location,
branch_id identifier,
application_id app_id
from APEX_APPLICATION_PAGE_BRANCHES
where upper(condition_type) = 'NEVER'
union
select
'Page Computation' component,
item_name description,
execution_sequence sequence,
'Page: ' || page_id location,
computation_id identifier,
application_id app_id
from APEX_APPLICATION_PAGE_COMP
where upper(condition_type) = 'NEVER'
union
select
'Page Process' component,
process_name description,
execution_sequence sequence,
'Page: ' || page_id location,
process_id identifier,
application_id app_id
from APEX_APPLICATION_PAGE_PROC
where upper(condition_type) = 'NEVER'
union
select
'Page Region' component,
region_name description,
display_sequence sequence,
'Page: ' || page_id location,
region_id identifier,
application_id app_id
from APEX_APPLICATION_PAGE_REGIONS
where upper(condition_type) = 'NEVER'
union
select
'Report Column' component,
column_alias description,
display_sequence sequence,
'Page: ' || page_id || ' (' ||
region_name || ')' location,
region_report_column_id identifier,
application_id app_id
from APEX_APPLICATION_PAGE_RPT_COLS
where upper(condition_type) = 'NEVER'
union
select
'Page Validation' component,
validation_name description,
validation_sequence sequence,
'Page: ' || page_id location,
validation_id identifier,
application_id app_id
from APEX_APPLICATION_PAGE_VAL
where upper(condition_type) = 'NEVER'
union
select
'Button' component,
button_name description,
button_sequence sequence,
'Page: ' || page_id || ' (' ||
region || ')' location,
button_id identifier,
application_id app_id
from APEX_APPLICATION_PAGE_BUTTONS
where upper(condition_type) = 'NEVER'
union
select
'Page Item' component,
item_name description,
display_sequence sequence,
'Page: ' || page_id || ' (' ||
region || ')' location,
item_id identifier,
application_id app_id
from APEX_APPLICATION_PAGE_ITEMS
where upper(condition_type) = 'NEVER'
union
select
'Parent Tab' component,
tab_name description,
display_sequence sequence,
'Shared Component - Navigation - Tabs' location,
parent_tab_id identifier,
application_id app_id
from APEX_APPLICATION_PARENT_TABS
where upper(condition_type) = 'NEVER'
union
select
'Application Process' component,
process_name description,
process_sequence sequence,
'Shared Component - Logic' location,
application_process_id identifier,
application_id app_id
from APEX_APPLICATION_PROCESSES
where upper(condition_type) = 'NEVER'
union
select
'Shortcuts' component,
shortcut_name description,
null sequence,
'Shared Component - User Interface' location,
shortcut_id identifier,
application_id app_id
from APEX_APPLICATION_SHORTCUTS
where upper(condition_type) = 'NEVER'
union
select
'Tab' component,
tab_name description,
display_sequence sequence,
'Shared Component - Navigation - Tabs' location,
tab_id identifier,
application_id app_id
from APEX_APPLICATION_TABS
where upper(condition_type) = 'NEVER'
)
where app_id = decode(:app_id,null,app_id,:app_id)

The APEX repository views that are being used return different information for the different component types, so I have used generic place holders for the results of an inline view, which does a series of unions on queries retrieving data from the views. I have tried to ensure the what is retrieved for each component type is descriptive enough so as to be able to easily locate the component in your application builder and delete it. A sample result is shown below:


By the way, in writing this post, for an easy and accurate way to get a definitive list of all the apex components that could be conditionally displayed, I went to the 'APEX_DICTIONARY' view and applied the following predicate:
upper(column_name) = 'CONDITION_TYPE'
This returned a list of APEX views that have columns with the name 'CONDITION_TYPE'. These views were then used as a basis for my main query.

Tidy app, tidy mind.


Further information...
Patrick Wolf's excellent post and presentation slides on 'The Power of the Apex Repository'.

4 comments:

Patrick Wolf said...

Anthony,

good to see that more and more people are starting to leverage the apex repository views!

Keep up blogging
Patrick

Dimitri Gielis said...

See your blog for the first time.

Welcome on board of the APEX Blogger.

Keep up the good posts,
Dimitri

Anthony Rayner said...

Dimitri / Patrick,

Thank you !!

More to come shortly!
Anthony.

Andrew said...

Ant,

Interesting article and a nice new shiny blog!

Andy T.