How to get column values dynamically in Oracle Apex Interactive Grid

Question

I want to concatenate a static sql statement with a dynamic string represent for some of dynamic column which have defined by another sql statement like this select column1, column2, '|| v_select ||' from my_table. v_select have example value like column3, column4 And I use this new sql as the source of Oracle APEX interactive grid. But it only generate column1 and column2, column3, column4 couldn't generate. Can someone can help me to resolve my problem. Thanks a lot

Answer 1

This can only be done using a type of "Function body returning SQL Query". This doesn't need to be a function defined as such in the database, it just needs to be an anonymous pl/sql block with a RETURN statement. The help has some examples.

Example on the sample emp/dept dataset. In the example the variable l_additional_cols has a list of additional columns:

DECLARE
  l_additional_cols VARCHAR2(512);
  l_select VARCHAR2(4000);
BEGIN
  l_select := q'~
select ename, %0 from emp
~';
  l_additional_cols := ' job, sal';
  l_select := apex_string.format(l_select, l_additional_cols);
  RETURN l_select;
END;

Note that the returned select statement needs to be valid at compile time, so make sure to have a default value.