APEX 5.2 Dynamic Interactive report

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.

create table TEST_QUERYS
( id number(10) NOT NULL
, query varchar2(4000) NOT NULL
);

insert into TEST_QUERYS
(id
,query
)values
(1
'select * from emp'
)

In your Interactive Report Select :
Source Type = PL/SQL Function Body returning SQL Query

declare
lv_query varchar2(4000);
begin

select query
into lv_query
from TEST_QUERYS
where id = 1(You can use Bind variables here.);

return lv_query ;
end;

And there you have it.