In Oracle Apex how do you set the table name of a report using a page item?-CodePudding

I have many tables to choose from, and an Interactive Report whose table name needs to be set using a page item called P4_TABLE_NAME.

Other posts say that this can only be done with PL/SQL. So I set the Source of the Interactive Report to "Function Body returning SQL Query" and the Language to PL/SQL. This is the code I then used:

declare
    v_query varchar2(500);
begin
    v_query := 'select * from '||:P4_TABLE_NAME ;
    return v_query;
end;

This code gives the error "invalid table name". I also tried it on a Classic Report and it gives the same error.

CodePudding user response：

That's kind of tricky because report should know which columns you're selecting unless you set it to use generic column names. It means that no matter which table you query, column names will be col1, col2, col3, ...

Here's how:

I created a new page with a Classic report region and a Select list item (which returns tables I have in my schema)

report's source is a function body that returns a query, just as you put it:

declare
    v_query varchar2(500);
begin
    v_query := 'select * from ' || :P4_TABLE_NAME;
    return v_query;
end;

don't forget to set "Use generic column names" to "Yes"!

Item is trivial:

I set it to "Submit page" (so that when I choose a new table, report's contents changes)

Select list item's query:

select table_name d, table_name r
from user_tables
order by table_name

Run the page: