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.

Oracle Apex's Static List Of Values editors

May be you didn't notice it, but there is more than one Static List Of Values editor in Oracle Application Express version 2.2.
The first one, with a rather minimalistic user interface is present in each item's definition page, under the List of Values attribute properties, and allows you to edit *only* the first 15 elements.
It means that if you manually entered 16 or more values into the List of values definition field and then you click on the editor link, they will be truncated at the 15th, so watch out before clicking on APPLY_CHANGES!

Then, in case you need to stuff more than 15 entries in the static LOV, you're better off taking advantage of the automatic LOV converter that you find in the upper right corner of the page, under the Task List as Convert LOV.

Once the List of values has been converted into a named list of values, you can finally enjoy the a much more refined version of the editor that allows you to easily change the order of appearance of the elements, add new ones and handle more than 15 entries.
This editor is accessible from the Grid Edit button, after clicking on the LOV name that you find in the shared components section of the page definition.