Hello Fellow bloggers,
With APEX 5.2 you get a lot of new options within your interactive report one of them is : PL/SQL Function Body returning SQL Query.
With that function it’s easier than ever before to create dynamic interactive reports.
Where you previously had to create your own functions to fill the column names and data with for example an apex_collection(which definitely has its downfalls with loss of data type for example), now you can get the same result with just a few lines of PL SQL.
Let me show you how.
Create a table where you store your query you want to use in your interactive reports.
In your Interactive Report Select :
Source Type = PL/SQL Function Body returning SQL Query
And there you have it.
3 Comments
michel kanokian · 11 September, 2018 at 6:14 pm
hi dear,
I was tried to bind variable as you said but PL/SQL Function returns “NO DATA FOUND” and do not accept the query, can you make a complete example.
thank you in advance.
NB : i use apex 18.1
Raffy · 16 November, 2018 at 5:24 pm
Check the link below. Looks like this will not work. I wanted to do the same thing but looks I will need to use classic reports instead.
https://community.oracle.com/message/14851323#14851323
Raffy · 16 November, 2018 at 5:22 pm
it seems that doesn’t really work according to the APEX Team. They say “the “shape” of a dynamic query for Interactive Reports shouldn’t change”. So looks like you will need to do user classic reports.