解决LISTAGG引发ORA-01489字符串拼接过长问题

当使用LISTAGG拼接超过4000字符的文本时，会触发ORA-01489错误，以下是不同Oracle版本对应的解决方案：

方案一：Oracle 12cR2及以上版本 - 使用LISTAGG的ON OVERFLOW子句 #

Oracle 12cR2新增了LISTAGG的溢出处理机制，可直接将拼接结果转为CLOB以支持大文本：

SELECT OWNER,
       NAME,
       TYPE,
       LISTAGG(TEXT, '') WITHIN GROUP (ORDER BY LINE) ON OVERFLOW CONTINUE INTO CLOB AS TEXT
FROM ALL_SOURCE 
WHERE OWNER = 'ITMS'
GROUP BY OWNER, NAME, TYPE

也可根据需求选择截断（保留部分内容并标记）：

SELECT OWNER,
       NAME,
       TYPE,
       LISTAGG(TEXT, '') WITHIN GROUP (ORDER BY LINE) ON OVERFLOW TRUNCATE '...' TO 1000 WITH COUNT AS TEXT
FROM ALL_SOURCE 
WHERE OWNER = 'ITMS'
GROUP BY OWNER, NAME, TYPE

方案二：Oracle 11g及以上版本 - 使用XMLAGG生成CLOB #

通过XMLAGG拼接文本并转换为CLOB，兼容更早版本：

SELECT OWNER,
       NAME,
       TYPE,
       RTRIM(XMLAGG(XMLELEMENT(E, TEXT) ORDER BY LINE).EXTRACT('//text()').GETCLOBVAL(), CHR(10)) AS TEXT
FROM ALL_SOURCE 
WHERE OWNER = 'ITMS'
GROUP BY OWNER, NAME, TYPE

RTRIM(..., CHR(10))用于移除拼接后末尾多余的换行符，可根据实际情况调整。

方案三：Oracle 10g及以下版本 - 自定义CLOB聚合函数 #

对于更早的Oracle版本，需要自定义返回CLOB的聚合函数：

1. 创建聚合类型及体：

CREATE OR REPLACE TYPE clob_agg_type AS OBJECT
(
  total CLOB,
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT clob_agg_type) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT clob_agg_type, value IN VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateTerminate(self IN clob_agg_type, returnValue OUT CLOB, flags IN NUMBER) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT clob_agg_type, ctx2 IN clob_agg_type) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY clob_agg_type IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT clob_agg_type) RETURN NUMBER IS
  BEGIN
    sctx := clob_agg_type(EMPTY_CLOB());
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT clob_agg_type, value IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    self.total := self.total || value;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self IN clob_agg_type, returnValue OUT CLOB, flags IN NUMBER) RETURN NUMBER IS
  BEGIN
    returnValue := self.total;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT clob_agg_type, ctx2 IN clob_agg_type) RETURN NUMBER IS
  BEGIN
    self.total := self.total || ctx2.total;
    RETURN ODCIConst.Success;
  END;
END;
/

2. 创建聚合函数：

CREATE OR REPLACE FUNCTION clob_agg(input VARCHAR2) RETURN CLOB
PARALLEL_ENABLE AGGREGATE USING clob_agg_type;
/

3. 使用自定义函数查询：

SELECT OWNER,
       NAME,
       TYPE,
       clob_agg(TEXT) WITHIN GROUP (ORDER BY LINE) AS TEXT
FROM ALL_SOURCE 
WHERE OWNER = 'ITMS'
GROUP BY OWNER, NAME, TYPE

内容的提问来源于stack exchange，提问作者Garvit Vashisth