使用XMLAgg/XMLElement时缓冲区过小错误 [英] Buffer too small error while using XMLAgg/XMLElement

问题描述

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

I am executing the below query in oracle and the following error comes up

ORA-19011:字符串缓冲区太小

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，因此我不确定是什么问题.当执行内部查询时，输出将如下所示

I have never used Xmlagg/Xmlelement hence I am not sure what's the issue. When the inner query is executed then the output would be like below

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

以此类推...

推荐答案

聚集不是问题；当您尝试修剪剩下的逗号时，就会出现错误.

The aggregation isn't the problem; the error is coming when you try to trim off the trailing comma you're left with.

您正在将XMLAgg结果(一个XMLType对象)隐式转换为varchar2；并且当它的长度超过4000个字符时，您将得到此错误，因为它是SQL中varchar2值的最大长度(至少直到Oracle 12c为止).

You are getting an implicit conversion of your XMLAgg result, which is an XMLType object, to varchar2; and when its length exceeds 4000 characters you will get this error as that is the maximum length of a varchar2 value in SQL (at least, until Oracle 12c).

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

You need to explicitly get the value as a CLOB before calling rtrim(), using getclobval():

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' );

您还可以定义您自己的聚合函数，返回一个CLOB并处理超过4000个字符；如果没有XML解决方法，则可以更像listagg()来调用它.

You could also define your own aggregate function that can return a CLOB and handle more than 4000 characters; that could then be called more like listagg(), without the XML workaround.

这篇关于使用XMLAgg/XMLElement时缓冲区过小错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！