CODEWITHSUNDEEP
oracle-databasexmltype
Capital_East
Capital_East

Reputation: 41

xmltype character string buffer too small

In my stored procedure:

 declare
v_xml xmltype;
begin
open v_cur for
Select xmlelement('el',xmlagg(xmlelement('el2'))) from table;
loop
fetch v_cur into v_xml; -- line where the error 

*.....additional logic to parse v_xml*
end loop;
end;

I'm getting a "character string buffer too small" error when the record to be fetched into v_xml has a length > 4000. Do you guys have any idea on how to go about this? Thanks

Upvotes: 2

Views: 2323

Answers (2)

Charles Henry
Charles Henry

Reputation: 373

If you use xmlagg(), you'll have to add .getclobval() to the surrounding xmlelement() since the char limit is 4000 on xmlagg(). Obviously this means you'll be using clobs instead of xmltype but you have no choice, you'll have to cast back to xmltype later if needed. Example below:

declare
v_xml clob; -- Use CLOB
begin
open v_cur for
Select xmlelement("el",xmlagg(xmlelement("el2", tab_col))).getclobval() from table; -- add .getclobval()
loop
fetch v_cur into v_xml; -- line where the error 

*.....additional logic to parse v_xml*
end loop;
end;

Upvotes: 3

Yavor
Yavor

Reputation: 321

Maybe you're using old Oracle version? There have been some limitations in past. For me it works with 10 000 000 rows:

declare
    v_xml xmltype;
begin
    select xmlelement("el", xmlagg(xmlelement("el2")))
    into v_xml from (select 1 from dual connect by level <= 10000000);
end;

Upvotes: 0

Related Questions