1. Create 3 items --> P1_SQL, P1_COLCOUNT and P1_HEADER
2. Create a region --> PL/SQL Dyanamic Content
declare
l_query varchar2(32767) := nvl(:P1_SQL,'select empno,ename from emp');
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
l_sql varchar2(4000) := 'select /*smartcheck*/ ';
l_sep varchar2(1);
v_sql VARCHAR2 (32000) := NULL;
v_dates VARCHAR2 (10000) := NULL;
BEGIN
IF APEX_COLLECTION.COLLECTION_EXISTS ( p_collection_name => 'EMP_SQL') THEN
APEX_COLLECTION.DELETE_COLLECTION( p_collection_name => 'EMP_SQL');
end if;
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY (
p_collection_name => 'EMP_SQL',
p_query => l_query);
dbms_sql.close_cursor(l_theCursor);
l_theCursor := dbms_sql.open_cursor;
dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
:P1_COLCOUNT := l_colcnt;
for i in 1 .. least(l_colCnt,2)
loop
apex_util.set_session_state( p_name => 'P1_HEADER'||lpad(i,2,0)
, p_value => l_descTbl(i).col_name
);
end loop;
end;
3. Create Interactive Report
select
c001 ,c002
from apex_collections
where collection_name = 'EMP_SQL';
