PL/SQL: Generating CSV varchar from select statement without loops

Question

Using PL/SQL, I'm looking to programmatically generate a CSV string/varchar object from a select statement. So the output of the select statement would normally be 1-n records (I only need one column). But the kicker is, I cannot use loops or any iterative process and it must be done without using external libs.

I can freely convert the data to a table or to a different data type and use more memory if needed. But I cannot explicitly use loops.

I guess I'm looking for a way to do something like this:

declare
   my_csv varchar2(4000);
begin
   select implode(my_column,',') into my_csv
   from my_table
   where some_column = 'some value';

   dbms_output.put_line('I got my list: ' || my_csv);
end;

Where essentially the implode would theoretically work like the PHP implode function, and the ',' comma is my separator. I can define the implode function myself, but again, I cannot explicitly use loops.

Any ideas?

Answer 1

If length of the result doesn't exceed 4000 characters, listagg might help:

SQL> select listagg(ename, ',') within group (order by null) my_csv
  2  from emp;

MY_CSV
--------------------------------------------------------------------------------------
ADAMS,ALLEN,BLAKE,CLARK,FORD,JAMES,JONES,KING,MARTIN,MILLER,SCOTT,SMITH,TURNER,WARD

SQL>

If it does (exceed 4000 characters), XMLAGG is your choice:

SQL> select
  2    rtrim(xmlagg(xmlelement(e, ename,',').extract('//text()') order by null).GetClobVal(),',')
  3    result
  4  from emp;

RESULT
--------------------------------------------------------------------------------------------------
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MIL

SQL>