Skip to Main Content
  • Questions
  • Creation of Dynamic Region OR Dynamic Interactive Report


Question and Answer

Connor McDonald

Thanks for the question, Daxesh.

Asked: May 16, 2020 - 7:05 am UTC

Last updated: December 07, 2023 - 6:22 am UTC

Version: 20.1

Viewed 10K+ times! This question is

You Asked

Before understand question mind I have one table with 3 columns one is id second is description and third is sql query, i created one Apex page with two region one is static region and one is Interactive Report Region.
In Static region i have one select list for user and in Interactive report region it execute query according the selection and gives the output. I use source type : L/SQL Function Body returning SQL Query for interactive report. I write following code

when page load exception is raised and report shows but when we change selection it gives error i am unable to track it. i also tried with apex_collections but problem is that some unused columns are also on report and report heading i am not be able to change dynamically. Total case i upload on
workspace : HLL username : password: Apex@1234 to run system username demo and password is demo. Problem is on page no :2.

the test case you can download it from here (i don't know how to upload and create LiveSql link i upload scripts on LiveSql but unable to creating link.

and Connor said...

To my knowledge you cannot change the columns (ie, the *structure*) of a report dynamically. I have an app that lets me run any query - I do it with an APEX collection as follows

    l_query varchar2(32767) := nvl(:P1_sql,'select * from dual');
    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);

     APEX_COLLECTION.DELETE_COLLECTION( p_collection_name => 'ANY_SQL');
  end if;

      p_collection_name => 'ANY_SQL',
      p_query => l_query);

   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,50)
     apex_util.set_session_state( p_name  => 'P1_HEADER'||lpad(i,2,0)
                                , p_value =>  l_descTbl(i).col_name
   end loop;


which populates the collection and also set the column headings to be the names of the columns. My region is then:

from apex_collections 
where collection_name = 'ANY_SQL'


  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment



DAXESH LAIWALA, May 19, 2020 - 10:22 am UTC

Thanks for good support/answer/knowledge sharing. but here i have one question we set the heading in :P1_HEADING01 and etc., how to programming set grid column label of relevant column e.g. c001 heading of :P1_HEADING01 and also server side condition that if value of :P1_HEADING01 is not null for c001 and etc.,

or i have to do it manually for all columns
Connor McDonald
May 19, 2020 - 11:08 am UTC

For each column the column label is:

&P1_HEADING01 .for c001
&P1_HEADING01. for c002

and so forth....


DAXESH LAIWALA, May 19, 2020 - 11:31 am UTC

yes sir its working but can we set it by programming or manually we have to do it. any method/package/builtin for doing it by writing code or set property for each column.
and even server side condition too.

A reader, July 01, 2021 - 11:49 am UTC

Not able to login to your Workspace on

Gopal, November 24, 2021 - 11:35 am UTC


i tried using the credentials you shared. Not able to login.
Please help
Chris Saxon
November 24, 2021 - 2:16 pm UTC

You mean you've requested a free workspace on and have lost your credentials? Have you tried resetting your password?

Or do you mean something else?

Other options?

Alain L., November 29, 2023 - 11:08 am UTC

Hi Connor,
this is great stuff, works nicely!
But what if I need more than 50 columns with text?
Is the only option to create my own kind of collection storing data in a table together with session information to make it unique for users?

Thanks a lot.
Connor McDonald
December 04, 2023 - 2:43 am UTC

Yup, you're pretty much on your own there.

Here's a rudimentary one to get you started

SQL> create table t(vc1 varchar2(100), d1 date, n1 number);

Table created.

SQL> begin
  2  for i in 2 .. 100 loop
  3    execute immediate 'alter table t add  vc'||i||' varchar2(100)';
  4    execute immediate 'alter table t add  d'||i||' date';
  5    execute immediate 'alter table t add  n'||i||' number';
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> create or replace
  2  procedure my_collection_generator(p_query varchar2) is
  3    l_theCursor integer default dbms_sql.open_cursor;
  4    l_columnValue varchar2(4000);
  5    l_status integer;
  6    l_descTbl dbms_sql.desc_tab;
  7    l_colCnt number;
  8    l_cs varchar2(255);
  9    l_date_fmt varchar2(255);
 11    l_row t%rowtype;
 13    l_char_arr sys.odcivarchar2list := sys.odcivarchar2list();
 14    l_date_arr sys.odcidatelist := sys.odcidatelist();
 15    l_num_arr  sys.odcinumberlist := sys.odcinumberlist();
 16  begin
 18    execute immediate 'alter session set nls_date_format = ''yyyymmddhh24miss''';
 20    dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
 21    dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
 23    for i in 1 .. l_colCnt loop
 24      if ( l_descTbl(i).col_type not in ( 113 ) )
 25      then
 26        dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
 27      end if;
 28    end loop;
 30    l_status := dbms_sql.execute(l_theCursor);
 32    while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
 33    loop
 34      l_char_arr := sys.odcivarchar2list();
 35      l_date_arr := sys.odcidatelist();
 36      l_num_arr  := sys.odcinumberlist();
 37      for i in 1 .. l_colCnt loop
 38        if l_descTbl(i).col_type in ( 2,100,101 ) then
 39          dbms_sql.column_value( l_theCursor, i, l_columnValue );
 40          l_num_arr.extend;
 41          l_num_arr(l_num_arr.count) := l_columnValue;
 42        elsif l_descTbl(i).col_type in ( 12,13 ) then
 43          dbms_sql.column_value( l_theCursor, i, l_columnValue );
 44          l_date_arr.extend;
 45          l_date_arr(l_date_arr.count) := l_columnValue;
 46        else
 47          dbms_sql.column_value( l_theCursor, i, l_columnValue );
 48          l_char_arr.extend;
 49          l_char_arr(l_char_arr.count) := l_columnValue;
 50        end if;
 51      end loop;
 53      if l_date_arr.exists(1) then l_row.d1 := l_date_arr(1); end if;
 54      if l_date_arr.exists(2) then l_row.d2 := l_date_arr(2); end if;
 55      if l_date_arr.exists(3) then l_row.d3 := l_date_arr(3); end if;
 56      if l_date_arr.exists(4) then l_row.d4 := l_date_arr(4); end if;
 57      ...
 58      if l_date_arr.exists(100) then l_row.d100 := l_date_arr(100); end if;
 60      if l_num_arr.exists(1) then l_row.n1 := l_num_arr(1); end if;
 61      if l_num_arr.exists(2) then l_row.n2 := l_num_arr(2); end if;
 62      if l_num_arr.exists(3) then l_row.n3 := l_num_arr(3); end if;
 63      if l_num_arr.exists(4) then l_row.n4 := l_num_arr(4); end if;
 64     ...
 65      if l_num_arr.exists(100) then l_row.n4 := l_num_arr(100); end if;
 67      if l_char_arr.exists(1) then l_row.vc1 := l_char_arr(1); end if;
 68      if l_char_arr.exists(2) then l_row.vc2 := l_char_arr(2); end if;
 69      if l_char_arr.exists(3) then l_row.vc3 := l_char_arr(3); end if;
 70      if l_char_arr.exists(4) then l_row.vc4 := l_char_arr(4); end if;
 71       ...
 72      if l_char_arr.exists(100) then l_row.vc4 := l_char_arr(100); end if;
 74      insert into t values l_row;
 75    end loop;
 77    dbms_sql.close_cursor( l_theCursor);
 78  exception
 79    when others then
 80      dbms_sql.close_cursor( l_theCursor);
 81      raise;
 82  end;
 83  /

Procedure created.

SQL> sho err
No errors.
SQL> exec my_collection_generator('select * from emp');

PL/SQL procedure successfully completed.

Thank you!

Alain L., December 05, 2023 - 9:03 am UTC

Thanks a lot Connor!
Looks good and provides me with a nice boot strap into my resolution.

Small hint

Alain L., December 06, 2023 - 3:29 pm UTC

Just one small addition, as I run into the issue getting ORA-06502 error on reading columns descriptions from the query.
Use dbms_sql.desc_tab2 and function dbms_sql.describe_columns2 instead of dbms_sql.desc_tab and dbms_sql.describe_columns.
As per Oracle 19c documentation, the data type dbms_sql.desc_rec and therefore dbms_sql.desc_tab are deprecated.
The main difference is the size of col_name in dbms_sql.desc_rec2 which allows to handle column names bigger than 32 bytes.
Connor McDonald
December 07, 2023 - 6:22 am UTC

Ah yes, thanks for picking that up.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library