sql - 使用 XMLAgg/XMLElement 时出现缓冲区太小错误

Question

这个问题已经有答案了:

XMLAGG with RTRIM issue (2 个回答)

已关闭 7 年前。

我在oracle中执行以下查询，出现以下错误

ORA-19011 : Character string buffer too small

select Rtrim(Xmlagg (Xmlelement (e, wonum  || ',')).extract ( '//text()' ), ',')
  as wolist
from ( select w.wonum from workorder w  
  connect by prior w.wonum = w.parent and prior w.siteid = siteid 
  start with w.siteid =  'ABCD' and w.wonum = 'P1234' )

我从未使用过 Xmlagg/Xmlelement 因此我不确定问题是什么。当执行内部查询时，输出将如下所示

select w.wonum from workorder w
connect by prior w.wonum = w.parent
and prior w.siteid = siteid
start with w.siteid = 'ABCD' and w.wonum = 'P1234'

wonum   parent
P1234
5678    P1234
9999    5678
8888    9999

等等...

Answer 1

聚合不是问题；问题在于聚合。当您尝试剪掉剩下的尾随逗号时，就会出现错误。

您正在将 XMLAgg 结果(XMLType 对象)隐式转换为 varchar2；当其长度超过 4000 个字符时，您将收到此错误，因为这是 SQL 中 varchar2 值的最大长度(至少在 Oracle 12c 之前)。

在调用 rtrim() 之前，您需要使用 getclobval() 显式获取 CLOB 形式的值:

select Rtrim(
  (Xmlagg(Xmlelement(e,wonum||',')).extract('//text()')).getclobval(),
    ',') as wolist
from ( select w.wonum from workorder w  
  connect by prior w.wonum = w.parent and prior w.siteid = siteid 
  start with w.siteid =  'ABCD' and w.wonum = 'P1234' );

您还可以定义your own aggregate function可以返回 CLOB 并处理超过 4000 个字符；然后可以像 listagg() 那样调用它，而无需 XML 解决方法。