Oracle 12c CASE条件不触发时LISTAGG仍执行报错的原因及解决方法

• Oracle SQL的执行逻辑中，聚合函数的计算优先级高于CASE分支的判断逻辑：数据库执行时会先计算所有出现在SELECT子句中的聚合函数结果，再执行CASE的条件分支判断，不会因为CASE分支写了恒成立的1=1就跳过ELSE分支里LISTAGG的计算，所以哪怕永远不会走到ELSE分支，依然会触发LISTAGG的超长报错。
• 你使用的12cR1（12.1.0.1）版本本身不支持LISTAGG的ON OVERFLOW语法，该语法是12cR2版本才新增的特性，无法通过原生LISTAGG语法处理超长拼接的问题。
• 你参考的旧方案不生效的核心原因就是聚合函数的计算时机早于分支判断，CASE的短路逻辑对SELECT子句中的聚合函数不生效。

方案1：使用XMLAGG函数（无4000字节限制，无需自定义代码，推荐） #

XMLAGG是Oracle原生支持的聚合函数，拼接结果默认返回XML类型，长度不受4000字节限制，转换为字符串后可以避免超长报错，示例写法如下：

select distinct t.id,
    RTRIM(
        XMLAGG(XMLELEMENT(e, mh.RNAME1, '; ') ORDER BY mh.RNAME1).EXTRACT('//text()').GETCLOBVAL(),
        '; '
    ) as RNAME1LIST,
    RTRIM(
        XMLAGG(XMLELEMENT(e, mh.RNAME2, '; ') ORDER BY mh.RNAME2).EXTRACT('//text()').GETCLOBVAL(),
        '; '
    ) as RNAME2LIST, 
    t.email1 as EMAIL1
from mh 
join mp on MH.MID = MP.MID
RIGHT JOIN T ON mh.T_ID = T.ID
group by t.id, t.email1;

如果拼接内容包含<、&这类XML特殊字符，额外加一层替换处理转义字符即可。

方案2：自定义聚合函数返回CLOB #

如果对拼接结果的格式有严格要求，也可以通过PL/SQL自定义支持CLOB拼接的聚合函数，示例代码如下：

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());
    DBMS_LOB.OPEN(sctx.total, DBMS_LOB.LOB_READWRITE);
    RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateIterate(self IN OUT clob_agg_type, value IN VARCHAR2) RETURN NUMBER IS
BEGIN
    IF value IS NOT NULL THEN
        IF DBMS_LOB.GETLENGTH(self.total) > 0 THEN
            DBMS_LOB.WRITEAPPEND(self.total, LENGTH('; '), '; ');
        END IF;
        DBMS_LOB.WRITEAPPEND(self.total, LENGTH(value), value);
    END IF;
    RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateTerminate(self IN clob_agg_type, returnValue OUT CLOB, flags IN NUMBER) RETURN NUMBER IS
BEGIN
    DBMS_LOB.CLOSE(self.total);
    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
    IF DBMS_LOB.GETLENGTH(ctx2.total) > 0 THEN
        IF DBMS_LOB.GETLENGTH(self.total) > 0 THEN
            DBMS_LOB.WRITEAPPEND(self.total, LENGTH('; '), '; ');
        END IF;
        DBMS_LOB.WRITEAPPEND(self.total, DBMS_LOB.GETLENGTH(ctx2.total), ctx2.total);
    END IF;
    RETURN ODCIConst.Success;
END;
END;
/
CREATE OR REPLACE FUNCTION clob_agg(input VARCHAR2) RETURN CLOB
AGGREGATE USING clob_agg_type;
/

调用时直接替换LISTAGG即可：

select distinct t.id,
    clob_agg(mh.RNAME1 ORDER BY mh.RNAME1) as RNAME1LIST,
    clob_agg(mh.RNAME2 ORDER BY mh.RNAME2) as RNAME2LIST,
    t.email1 as EMAIL1
from mh 
join mp on MH.MID = MP.MID
RIGHT JOIN T ON mh.T_ID = T.ID
group by t.id, t.email1;

方案3：业务层面截断 #

如果业务允许超长内容截断，可以先通过子查询限制每个分组的拼接行数，避免拼接后总长度超过4000字节。

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