Hi,
I am trying to figure out how to extract the name of the table I use for the various Select Lists and LOV's in one of my applications, from the Apex view apex_application_page_items.
I can get the basic information I want from this script:
SELECT page_id,
page_name,
item_name,
label,
display_as,
lov_named_lov,
lov_definition,
condition_type,
condition_expression1,
condition_expression2
FROM apex_application_page_items
WHERE application_id = 221
AND (display_as = 'Select List' OR display_as = 'Popup LOV')But as part of a new view, I would also like to somehow extract the table name of the lookup table out of the lov_definition. A dynamic query (view), would make this far easier than manually keeping a table updated.
It seems like getting the name extracted from the simple Select List, like:
select protocol_description d, protocol_description r
from lu_water_coll_protocol
order by sort_order
should be fairly simple, but I just can't get it figured out. In the above example I would like to pull out the 'lu_water_coll_protocol' as the lookup table name.
And then of course trying to extract that from more complicated ones like a PL/SQL Function returning SQL is far more difficult (at least for me). For my use, I have added the 'LU_' prefix to all of my lookup tables, so maybe pulling the table_names from DBA_TABLES would make it easier, but my mind is still at a loss on how to accomplish this.
I'm hoping to retire soon and I am trying to make things as easy as I can for whoever takes over for me.
I don't think I need to create a test case, as I 'think' you can probably use your own schema to accomplish what I think I need, and it seems like this is already an Apex app with a couple select lists/LOV's.
That seems to be a narrower focus on LOVs that reality no?
For example, and LOV could be
- static values
- a simple select statement
- a ten way join with 15 union all statements
- a nested SQL with 8 subqueries
etc etc etc
So the concept of "the table" within an LOV is not really there.
One thing you *could* do is
- for each row in your query result as you provided
- if the defintion does not start with "STATIC" or a period, then
- run an explain plan on the contents
- run select distinct object_name on the plan_table joined to all_tables to potential tables
Still plenty of gaps there, but you get the idea.
SQL> variable s varchar2(1000)
SQL>
SQL> exec :s := 'select * from emp e, dept d where d.deptno = e.deptno'
PL/SQL procedure successfully completed.
SQL>
SQL> delete plan_table;
6 rows deleted.
SQL> exec execute immediate 'explain plan into plan_table for '||:s;
PL/SQL procedure successfully completed.
SQL>
SQL> select t.table_name
2 from dba_tables t,
3 plan_table p
4 where t.owner = p.object_owner
5 and t.table_name = p.object_name;
TABLE_NAME
------------------------------
DEPT
EMP
SQL>
SQL>
SQL> select listagg(t.table_name,',') within group ( order by t.table_name)
2 from dba_tables t,
3 plan_table p
4 where t.owner = p.object_owner
5 and t.table_name = p.object_name;
LISTAGG(T.TABLE_NAME,',')WITHINGROUP(ORDERBYT.TABLE_NAME)
-------------------------------------------------------------------------------------------
DEPT,EMP