LISTAGG function: "result of string concatenation is too long"
Since the aggregates string can be longer than 4000 bytes, you can't use the LISTAGG
function. You could potentially create a user-defined aggregate function that returns a CLOB
rather than a VARCHAR2
. There is an example of a user-defined aggregate that returns a CLOB
in the original askTom discussion that Tim links to from that first discussion.
listagg
got recently covered by the ISO SQL standard (SQL:2016). As part of that, it also got an on overflow
clause, which is supported by Oracle 12cR2.
LISTAGG(<expression>, <separator> ON OVERFLOW …)
The on overflow
clause supports a truncate
option (as alternative to the default on overflow error
behavior).
ON OVERFLOW TRUNCATE [<filler>] WITH[OUT] COUNT
The optional defaults to three periods (...) and will be added as last element if truncation happens.
If with count is specified and truncation happens, the number of omitted values is put in brackets and appended to the result.
More about listagg
's on overflow
clause: http://modern-sql.com/feature/listagg
You can accomplish similar functionality with the XMLAGG function:
SELECT RTRIM(XMLAGG(XMLELEMENT(E,colname,',').EXTRACT('//text()') ORDER BY colname).GetClobVal(),',') AS LIST
FROM tablename;
This will return a clob value, so no limit on rows.