Kaley Crum

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

7 SQL Tuning Secrets You can Use Immediately, Even If You've Never Tuned A Query In Your Life

21 thoughts on “How to Fix “ORA-01489: Result of String Concatenation is Too Long” When Using Listagg”

  5. 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.

    Reply

  6. can you provide custom function to handle this task? i hate listagg function it gives errors at critical moments.

    Reply

  7. Hello Kaley,

    Gr88 solution.

    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
    from dba_objects
    where rownum <= 500;

    I need output:
    'Value1', 'Value2', …… 'ValueN'

    Thank in advance.

    Reply

  9. Caveat: Unfortunately, this does not work when using the
    WITHIN GROUP (ORDER BY obect2_name)
    OVER (Partition by object_id)

    Reply

    1. You can use this:

      select rtrim(xmlagg(xmlelement(e,object_name,’, ‘).extract(‘//text()’) order by object_id).getclobval(),’, ‘) x
      from dba_objects
      where rownum <= 500
      group by object_id

      Reply

  14. Compared with the listagg version it seams it doesn’t correctly manage distinct values
    With these data:

    ACH01380472|AC0331
    ACH01380472|AC0331
    ACH01380472|AC0327
    ACH01380472|AC0327
    ACH01380472|AC0331
    ACH01380472|AC0331
    ACH01380472|AC0327
    ACH01380472|AC0327

    The result is this (AC0327 repeated twice):

    AC0327, AC0327, AC0331

    Any help?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *