Listagg function with PLSQL collection

Question

I have a PL / SQL collection of the following type

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

I would like to combine the values ​​into a single line using a simple built-in function such as LISTAGG , without writing any user-defined functions or loops. All LISTAGG examples LISTAGG not show how to use PL / SQL collections. I am using Oracle 11g R2. Is it possible?

Answer 1

In order to be able to use the LISTAGG function with the collection, the collection must be declared as a nested table not as an associative array and must be created as a sql type (schema object), since it is not possible to use the pl / sql type in a select expression. To do this, you can 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, loop is your only choice.

Answer 2

LISTAGG is an SQL analytic function, and they do not work with PL / SQL collections, but cursors / rows.

So, in short, no, that’s not possible.

However, iterating over a PL / SQL table to construct 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);