2

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

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

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

and so on...

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
max092012
  • 365
  • 1
  • 5
  • 25
  • 1
    How any rows do you get running the query on its own? The generated string is exceeding 4000 characters. Why are you using this instead of `listagg`; because there is too much data? – Alex Poole Mar 01 '16 at 17:15

1 Answers1

5

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

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).

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

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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318