1)Create A page and Create a basic report on the page with the option of the source as the sql query (PLSQL function body returning the sql query).
2) Create an function which will take the table name as the input and return the query as the o/p.
create or replace function "QUERY_FOR_REPORT"
(table_name in VARCHAR2 default 'EMP')
return VARCHAR2
is
l_query varchar2(2000);
begin
l_query:='select * from '||table_name;
return VARCHAR2
is
l_query varchar2(2000);
begin
l_query:='select * from '||table_name;
return l_query;
end;
end;
3)
Call this function on the report as following .
BEGIN
RETURN QUERY_FOR_REPORT('AGENDA ' ||';');
END;
RETURN QUERY_FOR_REPORT('AGENDA ' ||';');
END;
4) to check the o/p pf your function you can run the same function on the sql workshop and check it.
Declare
l_r varchar(200):=QUERY_FOR_REPORT('AGENDA ' ||';');
Begin
dbms_output.put_line(l_r);
dbms_output.put_line(l_r);
END;
5) now runt the page , it displays the report .
