Listagg function with PLSQL collection

Question

I have a PL/SQL collection of following type

type p_typ_str_tab is table of varchar2(4000) index by pls_integer;

I would like to aggregate the values into a single string with a simple inline function like LISTAGG without writing any custom functions or for loops. All examples of LISTAGG don't show how to use PL/SQL collections. I'm using Oracle 11g R2. Is this possible?

Answer 1

To be able to use LISTAGG function with a collection, the collection must be declared as nested table not as an associative array and must be created as sql type (schema object) because it's not possible to use pl/sql type in a select statement. To that end you might do the following:

--- create a nested table type
SQL> create or replace type t_tb_type is table of number;
  2  /

Type created


--- and use it as follows
SQL> select listagg(column_value, ',') within group(order by column_value) res
  2    from table(t_tb_type(1,2,3)) -- or call the function that returns data of 
  3  /                              -- t_tb_type type

RES
-------
1,2,3

Otherwise the loop is your only choice.

Answer 2

LISTAGG is an analytic SQL function, and those don't operate against PL/SQL collections, but cursors/rowsets.

So, in short, no, it's not possible.

That said, iterating over a PL/SQL table to build a concatenated string is trivial:

l_new_string := null;
for i in str_tab.first .. str_tab.last
loop
  if str_tab(i) is not null then
    l_new_string := str_tab(i) || ', ';
  end if;
end loop;
-- trim off the trailing comma and space
l_new_string := substr(l_new_string, 1, length(l_new_string) - 2);