1

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

cengiz sevimli
  • 1,461
  • 8
  • 19
Thuan Le
  • 11
  • 6
  • This cannot be done as a normal query. The interactive grid metadata is generated when the interactive grid is created so the number of columns and datatypes need to be known at create time. It can be done with source type "Function body returning SQL Query" but I'm not sure this was already available in apex 20.2 – Koen Lostrie May 31 '22 at 05:51
  • Thanks Koen, I have to tried using Function body returning SQL Query, I also create a function to return SQL in database and call this function through a variable in Function body returning SQL Query but it still doesn't work. – Thuan Le May 31 '22 at 06:16

1 Answers1

0

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.

Koen Lostrie
  • 14,938
  • 2
  • 13
  • 19