If you’re using the LISTAGG() function in Oracle, and you’ve encountered the error ORA-01489: result of string concatenation is too long it’s because you’re trying to create a comma-delimited list that is too long for a VARCHAR2.
In Oracle, a VARCHAR2 column can contain at most 4000 characters. Some people might argue that this limit has changed in more recent versions of Oracle. But all Oracle did was use CLOB data types to store variables larger than 4000 characters under the hood. All of this is transparent to the user. Meaning it looks like you can have longer varchar2 values. But really, under the hood, it’s just a CLOB.
If you’re trying to create a list that is longer than 4000 characters, LISTAGG will give you the ORA-01489 error.
How to Fix “ORA-01489: Result of String Concatenation is Too Long”
How do you fix this? You use a function that returns a CLOB, rather than a VARCHAR2. If you were ambitious, you could write your own function. However, the simplest way to do it is to use XMLAGG.
Here’s an example of a query that fails, because the list is too long:
select listagg(object_name,', ') within group (order by object_id) x from dba_objects where rownum <= 500;
Here’s an equivalent version that uses XMLAGG() that works fine, because it returns a CLOB data type:
select rtrim(xmlagg(xmlelement(e,object_name,', ').extract('//text()') order by object_id).getclobval(),', ') x from dba_objects where rownum <= 500
It’s a bit longer and not quite as pretty as listagg, but it’s easier than writing your own function. Again, you can create your own function if you want. It’s just not nearly as easy as this. Check out the Oracle Data Cartridge Developer’s Guide for information on how to make your own aggregate functions.
To adapt the XMLAGG query for your own purposes:
- Change OBJECT_NAME to the column you wish to comma-delimit
- Modify OBJECT_ID to the expression you want to sort on.
- Also you’ll have to change ‘, ‘ to whatever you want to delimit your list with. Bear in mind that ‘, ‘ appears twice in the query above, so you’ll have to change it twice.
Free Oracle SQL Tuning Guide
Check out my FREE guide! 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!
Get it here: tuningsql.com/secrets