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
20 thoughts on “How to Fix “ORA-01489: Result of String Concatenation is Too Long” When Using Listagg”
I had this problem , ora-01489, I tried the solution in this article, and it worked. Thanks for that!
i tried this and its giving me “missing keyword ” error, pls what could be the issue
This is great solution , Thanks a lot for your help.
It’s worked great for me!!
Thank you so much!
Happy to help!
This solution worked nicely except that it changed the double quotes (“) in the concatenated strings to " The double quotes are needed and now I have a way to not have that happen.
can you provide custom function to handle this task? i hate listagg function it gives errors at critical moments.
But can you let me know, how to get result of below using XMLAGG:
select listagg(””||object_name||””,’, ‘) within group (order by object_id) x
where rownum <= 500;
I need output:
'Value1', 'Value2', …… 'ValueN'
Thank in advance.
i wonder why it appends the delimiters at the end that annoying…thanx
Caveat: Unfortunately, this does not work when using the
WITHIN GROUP (ORDER BY obect2_name)
OVER (Partition by object_id)
You can use this:
select rtrim(xmlagg(xmlelement(e,object_name,’, ‘).extract(‘//text()’) order by object_id).getclobval(),’, ‘) x
where rownum <= 500
group by object_id
How above query can be used with partition caluse? I cant use group by
Show de bola!! deu foi certo aqui, obrigadãooo…!
Thanks for this. Worked for me!!
Thank you so much for your direct approach to the solution, you saved a day for me. 🙂
Compared with the listagg version it seams it doesn’t correctly manage distinct values
With these data:
The result is this (AC0327 repeated twice):
AC0327, AC0327, AC0331
THANK U BRO!