How to create Interactive/Classic Report with dynamic SQL?

0
votes

I'm running Apex 19.2 and I would like to create a classical or interactive report based on dynamic query.

The query I'm using is not known at design time. It depends on an page item value.

-- So I have a function that generates the SQL as follows
GetSQLQuery(:P1_MyItem);

This function may return something like 

select Field1 from Table1
or
Select field1,field2 from Table1 inner join Table2 on ...

So it's not a sql query always with the same number of columns. It's completely variable.

I tried using PL/SQL function Body returning SQL Query but it seems like Apex needs to parse the query at design time.

Has anyone an idea how to solve that please ?

Cheers, Thanks.

enter image description here

1
oracleoracle-apex
isn't there an option "Use Generic Column Names" ? I think if you enable that it doesn't try to get the column names on compile – Koen Lostrie

1 Answers

1
votes

Enable the Use Generic Column Names option, as Koen said.

enter image description here

Then set Generic Column Count to the upper bound of the number of columns the query might return.

If you need dynamic column headers too, go to the region attributes and set Type (under Heading) to the appropriate value. PL/SQL Function Body is the most flexible and powerful option, but it's also the most work. Just make sure you return the correct number of headings as per the query.