Oracle使用sys_connect_by_path查询报ORA-01489字符串拼接过长错误

报错原因 #

ORA-01489: result of string concatenation is too long 报错的核心原因是 sys_connect_by_path 函数返回的结果为 VARCHAR2 类型，Oracle 数据库 SQL 上下文的 VARCHAR2 类型默认最大长度仅为 4000 字节，即使开启 12c 及以上版本的 MAX_STRING_SIZE 扩展参数，最大也仅支持 32767 字节。当你拼接的消息条数较多、单条内容较长时，拼接结果超出长度限制就会触发该报错。

你原SQL末尾的 MINUS SELECT NULL FROM dual 没有实际业务作用，优化方案中已将其移除。

解决方案 #

方案1：使用XMLAGG实现无长度限制拼接（推荐） #

XMLAGG 函数支持返回 CLOB 类型，没有长度限制，适配所有数据量的拼接场景，是兼容性最好的解决方案，修改后的语句如下：

SELECT RTRIM(
         XMLAGG(
           XMLELEMENT(e, TO_CHAR(rn) || '.' || MESSAGE, '~')
           ORDER BY rn
         ).EXTRACT('//text()').GETCLOBVAL(),
         '~'
       ) AS MESSAGE
FROM (
  SELECT tif, 
         MESSAGE, 
         ROWNUM rn 
  FROM BULL_MESS msg, 
       BULL_MAPPING MAP 
  WHERE map.tif        = ? 
    AND msg.message_id   = MAP.message_id 
    AND msg.enabled_flag = 'Y'
)

方案2：使用LISTAGG的溢出处理逻辑（适配12cR2及以上版本） #

如果你使用的是Oracle 12cR2及更高版本，可以用LISTAGG函数自带的溢出处理语法，拼接结果超出长度时自动截断避免报错：

SELECT LISTAGG(TO_CHAR(rn) || '.' || MESSAGE, '~') 
         ON OVERFLOW TRUNCATE ('...') WITH COUNT AS MESSAGE
FROM (
  SELECT tif, 
         MESSAGE, 
         ROWNUM rn 
  FROM BULL_MESS msg, 
       BULL_MAPPING MAP 
  WHERE map.tif        = ? 
    AND msg.message_id   = MAP.message_id 
    AND msg.enabled_flag = 'Y'
)
ORDER BY rn

方案3：应用层分批拼接 #

如果业务不允许使用CLOB类型，也可以对查询结果做分页查询，控制每批拼接的字符串总长度不超过4000字节，再在应用层对多批结果做二次合并。

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