OraFAQ Forum: SQL & PL/SQL » XMLELEMENT ISSUE

Home » SQL & PL/SQL » SQL & PL/SQL » XMLELEMENT ISSUE (Oracle DB 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

XMLELEMENT ISSUE [message #456606]

Tue, 18 May 2010 12:57

infosuresh2k
Messages: 77
Registered: September 2009
Location: CHENNAI, INDIA

Member

Hi,

I have created below table

CREATE TABLE CLOB_TEST
(
NO NUMBER,
NAME CLOB
);

i have inserted the values like below

begin
for i in 1..1000
loop
insert into clob_TEST( NO, NAME ) values(i, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'||ASCII(I));
end loop;
end;

while running the below query i am getting the error ora-19011(charecter string buffer too small, because return value length exceeds 3000 chars)

SELECT rtrim(replace(replace(XMLAGG(
XMLELEMENT(
"emp",
e.name
)
), '<emp>'), '</emp>', ','), ',')
FROM clob_test e
where rownum <=103

i am using the below below query its rownum is less then 102 it returns value, because its length is less then 3000 chars

these 2 query have only one difference(rownum)

SELECT rtrim(replace(replace(XMLAGG(
XMLELEMENT(
"emp",
e.name
)
), '<emp>'), '</emp>', ','), ',')
FROM clob_test e
where rownum <=102

Can anyone tell me how to solve this issue.

Regards,
Suresh.V

Report message to a moderator

Re: XMLELEMENT ISSUE [message #456608 is a reply to message #456606]

Tue, 18 May 2010 13:11

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Can anyone tell me how to solve this issue.
I get same results on V11.2.0.1

Older builtin FUNCTIONs have VARCHAR2 size limitations & it appears this happens in your test case.

I doubt it will be fixed any time soon, so you need to find a suitable work around.

Sorry! Sometimes you need to accept reality.

SQL> CREATE TABLE CLOB_TEST
(
NO NUMBER,
NAME CLOB
);  2    3    4    5  

Table created.

SQL> !vi a.sql

SQL> set term on echo on
SQL> @a
SQL> declare
  2  i number;
  3  begin
  4  for i in 1..1000
  5  loop
  6  insert into clob_TEST( NO, NAME ) values(i, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'||ASCII(I));
  7  end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> SELECT Rtrim(Replace(Replace(Xmlagg(Xmlelement("emp", e.NAME)), ''), '', ','), ',')
FROM   clob_test e
WHERE  ROWNUM <= 103    2    3  ;
SELECT Rtrim(Replace(Replace(Xmlagg(Xmlelement("emp", e.NAME)), ''), '', ','), ',')
*
ERROR at line 1:
ORA-19011: Character string buffer too small


SQL> ed
Wrote file afiedt.buf

  1  SELECT Rtrim(Replace(Replace(Xmlagg(Xmlelement("emp", e.NAME)), ''), '', ','), ',')
  2  FROM   clob_test e
  3* WHERE  ROWNUM <= 102
SQL> /

RTRIM(REPLACE(REPLACE(XMLAGG(XMLELEMENT("EMP",E.NAME)),''),'',','),',')
--------------------------------------------------------------------------------
<emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ50</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ50</emp><e
mp>ABCDEFGHIJKLMNOPQRSTUVWXYZ50</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ50</emp><emp
>ABCDEFGHIJKLMNOPQRSTUVWXYZ50</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ50</emp><emp>A
BCDEFGHIJKLMNOPQRSTUVWXYZ50</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ50</emp><emp>ABC
DEFGHIJKLMNOPQRSTUVWXYZ50</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ50</emp><emp>ABCDE
FGHIJKLMNOPQRSTUVWXYZ50</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ50</emp><emp>ABCDEFG
HIJKLMNOPQRSTUVWXYZ50</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ50</emp><emp>ABCDEFGHI
JKLMNOPQRSTUVWXYZ50</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJK
LMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLM
NOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNO
PQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQ

RTRIM(REPLACE(REPLACE(XMLAGG(XMLELEMENT("EMP",E.NAME)),''),'',','),',')
--------------------------------------------------------------------------------
RSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRS
TUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTU
VWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVW
XYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXY
Z51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ5
1</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51<
/emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</e
mp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp
><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><
emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><em
p>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>

RTRIM(REPLACE(REPLACE(XMLAGG(XMLELEMENT("EMP",E.NAME)),''),'',','),',')
--------------------------------------------------------------------------------
ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>AB
CDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCD
EFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEF
GHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGH
IJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJ
KLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKL
MNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMN
OPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOP
QRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQR
STUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRST
UVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUV

RTRIM(REPLACE(REPLACE(XMLAGG(XMLELEMENT("EMP",E.NAME)),''),'',','),',')
--------------------------------------------------------------------------------
WXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWX
YZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ
49</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ50</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51
</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ52</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ53</
emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ54</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ55</em
p><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ56</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ57</emp>
<emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ49</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ49</emp><e
mp>ABCDEFGHIJKLMNOPQRSTUVWXYZ49</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ49</emp><emp
>ABCDEFGHIJKLMNOPQRSTUVWXYZ49</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ49</emp><emp>A
BCDEFGHIJKLMNOPQRSTUVWXYZ49</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ49</emp><emp>ABC
DEFGHIJKLMNOPQRSTUVWXYZ49</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ49</emp><emp>ABCDE

RTRIM(REPLACE(REPLACE(XMLAGG(XMLELEMENT("EMP",E.NAME)),''),'',','),',')
--------------------------------------------------------------------------------
FGHIJKLMNOPQRSTUVWXYZ50</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ50</emp><emp>ABCDEFG
HIJKLMNOPQRSTUVWXYZ50</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ50</emp><emp>ABCDEFGHI
JKLMNOPQRSTUVWXYZ50</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ50</emp><emp>ABCDEFGHIJK
LMNOPQRSTUVWXYZ50</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ50</emp><emp>ABCDEFGHIJKLM
NOPQRSTUVWXYZ50</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ50</emp><emp>ABCDEFGHIJKLMNO
PQRSTUVWXYZ51</emp><emp>ABCDEFGHIJKLMNOPQRSTUVWXYZ51</emp>

[Updated on: Tue, 18 May 2010 13:15]

Report message to a moderator

Previous Topic:	How to use Substitution Variables in FROM phrase?
Next Topic:	convert_nclob_to_blob

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Aug 02 21:48:34 CDT 2025