Oracle Apex unique column alias on query generated by Pl/SQL

Question

I am trying to get report which query is being dynamically pulled by PL/SQL block. I got inspiration from this article number of columns is dynamic and depend on the loop:

declare
l_qry VARCHAR2(4300);
s_qry VARCHAR2(80) := 0;
v_resort_id NUMBER := 1;

begin

l_qry := 'select a.column1,';

FOR pci in ( select id,name from table_pci where resort_id = v_resort_id) LOOP

s_qry := s_qry||'package.some_function (a.id,''' || pci.id || ''' , ''PC'') Property_type,' ;

 END LOOP;

   l_qry := l_qry ||s_qry;

   l_qry := l_qry||'a.column2 from features a where a.feature_type = ''condition1'' ';


return(l_qry);

end;

Property_type is hardcoded alias on the dynamic column, so everytime loop goes round it will try to generate column with the same name and Apex with flag it as error.If I select Use Generic Column Names (parse query at runtime only) it return correct number of columns but named Col1,Col2,Col3...

If I try to grab something dynamic from table_pci like name, I tried:

s_qry := s_qry||'package.some_function (a.id,''' || pci.id || ''' , ''PC'') '''|| pci.name || ''',' ;

I get error

failed to parse SQL query:
ORA-00923: FROM keyword not found where expected

Any help with finding a way to dynamicaly name the column greatly appreicated. Any pointers, any advices and links.

Ollie Ollie · Accepted Answer · 2013-09-18T21:45:53

I think you might have a syntax error in your attempt to use a dynamic column alias.

Try:

s_qry := s_qry||'package.some_function (a.id,''' || pci.id || ''' , ''PC'') AS '|| pci.name || ',' ;

If the value in pci.name potentially has spaces in it them enclose it with double quotes:

s_qry := s_qry||'package.some_function (a.id,''' || pci.id || ''' , ''PC'') AS "'|| pci.name || '",' ;

Hope it helps...

Oracle Apex unique column alias on query generated by Pl/SQL

1 Answers