dynamic interactive report in oracle apex

 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';