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?

The column is a varchar, no more than 20 characters, and I never expect to get more than 100 records. — rshaq, Dec 13 '19 at 20:21
See here: https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030 — Toru, Dec 14 '19 at 12:20

score 5 · Accepted Answer · answered Dec 13 '19 at 20:20

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>

As documentated here: https://stackoverflow.com/a/29030511/802058 — Toru, Dec 14 '19 at 12:53
That's not exactly "documented", @Toru. Documentation is something else, this is an answer posted on Stack Overflow. But, yes ... that's well know issue, nothing special about it. — Littlefoot, Dec 14 '19 at 13:05

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

1 Answers1