使用SYS_CONNECT_BY_PATH遇到“返回字符串超长”如何处理

Question

使用如下代码根据相同ID_COL拼接STRING_COL，但有些STRING_COL字符串长度过长，造成出现上述错误，Oracle好像只允许返回最多4000字符的字符串。SELECT ID_COL,TRIM(LEADING '|' FROM MAX (SYS_CONNECT_BY_PATH (SUBSTR (STRING_COL, 1, 2000), '|'))) RESULT
FROM (SELECT ID_COL, STRING_COL, ROW_NUMBER () OVER (PARTITION BY ID_COL ORDER BY ORDER_COL) AS RECORD_NUMBER
      FROM TABLE_NAME)
START WITH RECORD_NUMBER = 1
CONNECT BY PRIOR RECORD_NUMBER =  RECORD_NUMBER - 1 AND ID_COL = PRIOR ID_COL
GROUP BY ID_COLORA-01489: result of string concatenation is too long 我尝试在返回的字符串上加substr截取2000字节，但依然报错
请教有什么方法让Oracle在拼接字符串时只截取前4000位而忽略多于的字符？

Answer 1

substr(MAX (SYS_CONNECT_BY_PATH (SUBSTR (STRING_COL, 1, 2000), '|'))),1,2000)
试试这个

Answer 2

不行，Oracle可能在执行substr之前，就遇到字符串超长并报错了。

Answer 3

没错
拼接之前先检查length(STRING_COL)相加是否会超出限制

Answer 4

select ...sys_connect_by_path(string_col,'|'),...
from (SELECT ID_COL, STRING_COL,
  sum(length(string_col)+1)over(partition by ID_COL order by ORDER_COL)L, 
  ROW_NUMBER () OVER (PARTITION BY ID_COL ORDER BY ORDER_COL) AS RECORD_NUMBER 
      FROM TABLE_NAME)
START WITH RECORD_NUMBER = 1 
CONNECT BY PRIOR RECORD_NUMBER =  RECORD_NUMBER - 1 AND ID_COL = PRIOR ID_COL 
where L<2000