ORA-01489: Error when using LISTAGG

In Pl/SQL,to concatenate the values from a column, we can use LISTAGG. i.e

select LISTAGG(TABLE_NAME, ‘,’) WITHIN GROUP (ORDER BY TABLE_NAME)
from dba_tables where owner='[OWNER]’
/* criteria can be added here*/ and table_name like ‘FSF%’;

However, when the number of values is to big, we can get the next error.

ORA-01489: Das Ergebnis der Zeichenfolgenverkettung ist zu lang
01489. 00000 – “result of string concatenation is too long”
*Cause: String concatenation result is more than the maximum size.
*Action: Make sure that the result is less than the maximum size.

 

Solution:

Use the next sentence instead LISTAGG

select rtrim(xmlcast(xmlagg(xmlelement(e,TABLE_NAME,’,’).extract(‘//text()’) order by TABLE_NAME) AS CLOB)) into P_TABLIST from
dba_tables where owner='[OWNER]’
/* criteria can be added here*/ and table_name like ‘FSF%’;