Select from :LOV report / Using LOV as table_name bind variable

Question

I'm trying to create a page which has a list of values (P2_LIST) and a region which displays the contents of the table based upon the table name in this list e.g.

select * from :P2_LIST

however I can't save it as I get:

ORA-06550: line 1, column 21: ORA-00903: invalid table name

P2_LIST is a LOV with a list containing valid table names only.

Is there a way to do this?

When I do select P2_LIST from dual I can see it holds the correct TABLE_NAME and if I hardcode the table names then it returns also.

The idea is I can then add more tables to this page simply by adding an entry to the shared component.

If this can be resolved my next part is I want to use a concatenated value for this too:

e.g. If I have two tables:

thisistable_1 - submit thisistable
thisisnottable_1 - submit thisisnottable

Then I want to use something along the lines of:

select * from :P2_LIST||_1

that way I can use the same LOV elsewhere.

I am running: Application Express 5.0.3.00.03

So your LOV contains names of a tables, you want to choose a table from a list, and select data from it? It won't work like this. — Dmitriy
Why isn't it possible? can I not create a function / procedure which does this? I don't want to have multiple regions, I want a single region which can refresh. create table test (col1 varchar2(2000)); insert into test VALUES ('TEST'); insert into test VALUES ('TEST2'); LOV = TEST / TEST2 region = report and query = select * from test where col1 = :P3_NEW . this works fine, but not where P3_NEW is the bind variable. — bob dylan
Yes, you can create a pipelined function, but inside this function you need to use dbms_sql package to describe columns and fetch data. As for me - too much efforts when more simple solution exists. May be if you have 100+ tables... — Dmitriy

Tony Andrews Tony Andrews · Accepted Answer · 2016-02-11T16:31:12

Yes, using a dynamic SQL query.

Instead of select * from :P2_LIST define the report like this:

declare
   q long;
begin
   q := 'select * from ' || :P2_LIST || ';';
   return q;
end;

You'll also need to:

check the "Use Generic Column Names (parse query at runtime only)" setting under the region source
ensure that the source type is "SQL query (PL/SQL function body returning SQL query)"
set the Headings Type to "Column Names (InitCap)"

(NB long in PL/SQL is a subtype defined as varchar2(32760) that I just use to save typing!)

Here's a quick demo: apex.oracle.com/pls/apex/f?p=22644:10

Select from :LOV report / Using LOV as table_name bind variable

2 Answers