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.

Categories: Oracle APEX

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: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.

Leave a Reply

Your email address will not be published. Required fields are marked *