Where-used feature for Apex LOVs - the missing bit

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

When working on large Apex projects, it's easy to create components that shortly after must be discarded because the customer changed his mind on a certain feature or we found a better solution for a certain problem.
So, it may happen that shared components like list of values, in short LOVs, clutter the repository and you don't remember if a particular one is still used or not.

Oracle Application Express provides you with a where-used feature in the form of a list on the right hand side of the page containing the LOV definition (see the picture below).


The downside to this approach is in that you must open each and every LOV definition and see if this list is empty or not.
If the LOVs are just a few is not a big deal, but with dozens of them, it is certainly a waste of time.

Fortunately the apex dictionary views come to the rescue.

It doesn't take too long to come up with a couple of queries returning a list of all used and unused lists of values and this query is fairly simpler than the query i wrote for tracking build options time ago.

-- LOVs where used by app, page and component type

select app_id, named_lov, page_id, component_type, component_name
from (
select APPLICATION_ID as app_id,
     LOV_NAMED_LOV as named_lov,
     PAGE_ID,
     'PAGE ITEM' as component_type,
     ITEM_NAME as component_name
from apex_application_page_items
union all
select APPLICATION_ID as app_id,
     NAMED_LIST_OF_VALUES as named_lov,
     PAGE_ID,
     'REPORT COLUMN' as component_type,
     COLUMN_ALIAS as component_name
from APEX_APPLICATION_PAGE_RPT_COLS
-- comment out the following query if running on Apex 3.2
union all
select APPLICATION_ID as app_id,
     NAMED_LOV,
     PAGE_ID,
     'INTERACTIVE REPORT COLUMN' as component_type,
     COLUMN_ALIAS as component_name
from APEX_APPLICATION_PAGE_IR_COL
--
) a
where app_id = :APP_ID
and a.named_lov in (select LIST_OF_VALUES_NAME
                 from APEX_APPLICATION_LOVS b
                where b.APPLICATION_ID = a.app_id)
order by app_id, named_lov, page_id;



-- unused LOVs

select APPLICATION_ID as app_id,
      LIST_OF_VALUES_NAME as named_lov
 from APEX_APPLICATION_LOVS
where APPLICATION_ID = :APP_ID
minus
select app_id, named_lov
from (
select APPLICATION_ID as app_id,
      LOV_NAMED_LOV as named_lov
 from apex_application_page_items
union
select APPLICATION_ID as app_id,
      NAMED_LIST_OF_VALUES as named_lov
 from APEX_APPLICATION_PAGE_RPT_COLS
-- comment out the following query if running on Apex 3.2
union
select APPLICATION_ID as app_id,
      NAMED_LOV
 from APEX_APPLICATION_PAGE_IR_COL
--
) a
where app_id = :APP_ID
and a.named_lov in (select LIST_OF_VALUES_NAME
                     from APEX_APPLICATION_LOVS b
                    where b.APPLICATION_ID = a.app_id);

I hope i caught all possible components, i found that LOV column names in the dictionary come in four distinct flavors out of five total possibilities! (unless i missed some...)

See more articles about Oracle Application Express or download tools and utilities.