CODEWITHSUNDEEP
sqloracle11g
Makster
Makster

Reputation: 85

result of string concatenation is too long after using to_clob

I am trying to create a table view by running the sql below

  SELECT   IACM1.CMNT_REAS_TYP,
           TO_CLOB(LPAD (
                      LISTAGG (IACM1.CMNT_TXT, ' ')
                         WITHIN GROUP (ORDER BY IACM1.LN_NUM),
                      4000,
                      LISTAGG (IACM1.CMNT_TXT, ' ')
                         WITHIN GROUP (ORDER BY IACM1.LN_NUM)
                   ))
    FROM   FT_T_IACM IACM1, FT_T_IACM IACM2
   WHERE   IACM1.ISSACT_ID = IACM2.ISSACT_ID
           AND IACM1.CMNT_REAS_TYP = IACM2.CMNT_REAS_TYP
GROUP BY   IACM1.cmnt_reas_typ;

But I am getting the error below

ORA-01489: result of string concatenation is too long 01489. 00000 - "result of string concatenation is too long" *Cause: String concatenation result is more than the maximum size. *Action: Make sure that the result is less than the maximum size.

I looked up and I found suggestions to use to_clob but it is still throwing this error. I am using oracle 11g. Thanks for the help in advance.

Upvotes: 3

Views: 24900

Answers (2)

Ankur Bhutani
Ankur Bhutani

Reputation: 3219

No need to create custom function. Oracle has provided xmlagg function already to do that.

All you need is to convert the output into clob by GetClobVal and also need to rtrim as it will return delimiter in the end of the results. 

SELECT RTRIM(XMLAGG(XMLELEMENT(E,colname,',').EXTRACT('//text()') ORDER BY colname).GetClobVal(),',') from tablename;

Upvotes: 2

Todd George
Todd George

Reputation: 71

The longest a concatenated string in a LISTAGG can be is 4000 characters. In this query, the sum of the lengths of CMNT_TXT for one or more CMNT_REAS_TYP values appears to be more than 4000. The LISTAGG builds the string before the LPAD truncs it to 4000 characters - so the LPAD has no effect in this case. Also, the TO_CLOB has no impact because the LISTAGG goes to a varchar2 before anything else happens.

One way to fix this would be to put additional fields in your Group By if possible. If that's not an option, you might try using COLLECT instead of LISTAGG - you'll have more issues with getting datatypes to match up but it's doable.

Here's a link with some comparisons between LISTAGG and COLLECT and a little bit on how to use COLLECT: http://www.oracle-developer.net/display.php?id=515

Upvotes: 3

Related Questions