Do you sometimes think: "Ohh, this feature is missing in APEX Builder"?

Me too, very often.

APEX Team is doing a great job, but they can't create every feature we want (yet?:).

How do you remove all unnecessary List of Values with one click?

Recently, I had a requirement to clean one of my customers' old apps.

The first step was to remove all unnecessary List of Values.

You may think: I can go into Shared Components -> LOVs -> Utilization-> find all unused LOVs, select them all and click "delete"...

Oops, you can't ( yet?) :)

Ok, it must be in "grid edit"...

No, it's not.

If you have a few unused LOVs, you will click one by one and remove them.

Now, imagine clicking 435 times...

Install app 4000 (APEX Builder) into APEX Builder

And then I reminded myself that a few weeks ago, one of my colleagues, Jakub Dobruchowski (his blog is here), told me that I could install app 4000 (Oracle APEX Builder) into my apex and investigate it if I'm missing something.

So I downloaded the latest APEX 23.2 files and figured out that among apex installation files, there's a /builder/ folder

And inside:

So I installed the f4000.sql app into my apex.oracle.com workspace:

The "Edit" page for every list of values is p4111

So, I investigated this page in my Oracle APEX Builder app:

Then I researched what's behind the report showing "Total references" for LOVs on page 87.

Having all this information, I have created a script to delete all not-wanted LOVs from your application.

--replace APEX_220200 with your current APEX version
--flow id is you APP_ID
/*
--get security group
declare
   val number;
begin
   val := APEX_UTIL.FIND_SECURITY_GROUP_ID(p_workspace => 'YOUR_WORKSPACE_NAME');
   dbms_output.put_line(val);
end;
*/
delete from APEX_220200.WWV_FLOW_LISTS_OF_VALUES$
 where id in
       (
          select id
            from (
                    select /* APEX4000P87a lov_util */ id,
                           lov_name,
                           (
                              select count(*)
                                from APEX_220200.wwv_flow_step_items i
                               where flow_id = :fb_flow_id
                                 and i.named_lov = a.lov_name
                           ) item_references,
                           (
                              select count(*)
                                from APEX_220200.wwv_flow_region_report_column c
                               where c.flow_id = a.flow_id
                                 and c.security_group_id = a.security_group_id
                                 and c.named_lov = a.id
                           ) +
                           (
                              select count(*)
                                from APEX_220200.wwv_flow_region_columns c
                               where c.flow_id = a.flow_id
                                 and c.security_group_id = a.security_group_id
                                 and (c.lov_id = a.id or c.filter_lov_id = a.id)
                           ) report_column_references,
                           (
                              select count(*)
                                from APEX_220200.wwv_flow_worksheet_columns c
                               where c.flow_id = a.flow_id
                                 and c.security_group_id = a.security_group_id
                                 and c.rpt_named_lov = a.id
                           ) ir_references,
                           (
                              select count(*)
                                from APEX_220200.wwv_flow_step_items i
                               where flow_id = :fb_flow_id
                                 and i.named_lov = a.lov_name
                           )
                           +
                           (
                              select count(*)
                                from APEX_220200.wwv_flow_region_report_column c
                               where c.flow_id = a.flow_id
                                 and c.security_group_id = a.security_group_id
                                 and c.named_lov = a.id
                           )
                           +
                           (
                              select count(*)
                                from APEX_220200.wwv_flow_region_columns c
                               where c.flow_id = a.flow_id
                                 and c.security_group_id = a.security_group_id
                                 and (c.lov_id = a.id or c.filter_lov_id = a.id)
                           )
                           +
                           (
                              select count(*)
                                from APEX_220200.wwv_flow_worksheet_columns c
                               where c.flow_id = a.flow_id
                                 and c.security_group_id = a.security_group_id
                                 and c.rpt_named_lov = a.id
                           ) total_references
                      from APEX_220200.WWV_FLOW_LISTS_OF_VALUES$ a
                     where flow_id = :FB_FLOW_ID
                       and security_group_id = :flow_security_group_id
                 )
           where total_references = 0
       );

Summary

Making manual changes in APEX internal tables is not advised.

Everything I described - you are doing it at your own risk, and I won't take any responsibility.

This blog post aims to inform people that they can install APEX internal apps (not only app 4000) into APEX Builder and investigate them.

Either if you're curious about what's behind some places in APEX or you're missing some features - it can help you.

But once again, you're doing it at your own risk :)

And my final concern is...

What was created first if Oracle APEX Builder is built in Oracle APEX Builder? :)

If a hen lays eggs, and a hen hatched from an egg?

Cheers.