Oracle SQL中按ID和序列拼接超长拆分消息的解决方案

针对拆分的varchar2消息按msg_id分组、msg_order排序拼接的需求，提供三种可行方案：

方案1：LISTAGG结合CLOB转换（Oracle 12c+） #

LISTAGG默认返回varchar2，超过4000字符会触发ORA-01489错误，把每个消息片段转成CLOB再聚合即可避开长度限制：

SELECT msg_id,
       TO_CLOB(LISTAGG(TO_CLOB(msg_content), '') WITHIN GROUP (ORDER BY msg_order)) AS full_msg
FROM your_table
GROUP BY msg_id;

方案2：修正XMLAGG实现完整拼接 #

之前XMLAGG只取换行前内容是因为未正确提取XML文本，按下面写法可保证完整内容，同时按msg_id分组、msg_order排序：

SELECT msg_id,
       XMLAGG(XMLELEMENT(E, msg_content) ORDER BY msg_order).EXTRACT('//text()').GETCLOBVAL() AS full_msg
FROM your_table
GROUP BY msg_id;

如果拼接后有多余首尾空格，可在最外层加TRIM()函数。

方案3：自定义CLOB聚合函数（全版本兼容） #

如果上述方案不适用，可创建自定义聚合函数直接返回CLOB：
首先创建类型和函数：

CREATE OR REPLACE TYPE clob_agg_type AS OBJECT
(
  total_clob 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 ODCIAggregateMerge(self IN OUT clob_agg_type, ctx2 IN clob_agg_type) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateTerminate(self IN clob_agg_type, returnValue OUT CLOB, flags IN NUMBER) 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_clob := self.total_clob || value;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT clob_agg_type, ctx2 IN clob_agg_type) RETURN NUMBER IS
  BEGIN
    self.total_clob := self.total_clob || ctx2.total_clob;
    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_clob;
    RETURN ODCIConst.Success;
  END;
END;
/

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

然后调用函数完成拼接：

SELECT msg_id,
       clob_agg(msg_content) AS full_msg
FROM your_table
GROUP BY msg_id
ORDER BY msg_id;

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