Oracle 12.1, ORA-01489 result of string concatenation is too long

Listagg is a great way to concatenate strings in Oracle 12. I have used it in many cases, but sometimes it is just not good enough for a very large strings. Here is the way you can use it for strings, that would not exceed 4000 chars:

  SELECT FNR, ATTEST_DATO, STATUS, TYPE_FORMAAL, 
         LISTAGG(INNHOLD,  CHR(13)||CHR(10) ) 
          WITHIN GROUP (ORDER BY LINJENR ASC) AS INNHOLD 
    FROM Juridisk_Formaal 
GROUP BY FNR, ATTEST_DATO, STATUS , TYPE_FORMAAL;

Here is what can be used if concatenated string would exceed 4000 chars:

  SELECT FNR, ATTEST_DATO, STATUS, TYPE_FORMAAL,
         XMLAGG(XMLELEMENT(e,INNHOLD,CHR(13)||CHR(10)).extract('//text()') 
          ORDER BY LINJENR ASC).getclobval() as INNHOLD 
    FROM Juridisk_Formaal e 
GROUP BY FNR, ATTEST_DATO, STATUS , TYPE_FORMAAL; 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s