使用to_clob后字符串连接的结果太长 [英] result of string concatenation is too long after using to_clob

问题描述

我正在尝试通过运行以下sql创建表视图

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;

但是我得到下面的error

ORA-01489:字符串连接的结果太长 01489.00000-字符串连接的结果太长" *原因:字符串连接结果大于最大大小. *操作:确保结果小于最大大小.

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.

我抬起头，发现了使用to_clob的建议，但仍引发此错误.我正在使用oracle 11g.感谢您的提前帮助.

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.

推荐答案

LISTAGG中最长的串联字符串可以是4000个字符.在此查询中，一个或多个CMNT_REAS_TYP值的CMNT_TXT长度总和似乎大于4000.LISTAGG会在LPAD将字符串截断为4000个字符之前生成字符串-因此LPAD在这种情况下无效.另外，TO_CLOB也没有任何影响，因为LISTAGG在发生其他任何事情之前先进入了varchar2.

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.

解决此问题的一种方法是在可能的情况下在分组依据"中添加其他字段.如果这不是一种选择，则可以尝试使用COLLECT而不是LISTAGG-在使数据类型匹配方面会遇到更多问题，但这是可行的.

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.

这里有一个链接，其中包含LISTAGG和COLLECT之间的一些比较，以及有关如何使用COLLECT的一些信息: http://www.oracle-developer.net/display.php?id=515

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

这篇关于使用to_clob后字符串连接的结果太长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！