Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to avoid ORA-19011: Character string buffer too small in XML

YLNSep 23 2014 — edited Sep 23 2014

How to avoid ORA-19011: Character string buffer too small in below sql ?
create table xmltest (xmid number, xmdate date,xmcode number, xmdesc varchar2(100))

insert into xmltest
select level,sysdate + level,level*2 ,'Row'||level from dual connect by level <= 50

DECLARE
l_xml clob;
BEGIN
SELECT xmlagg(xmlelement("XMInfor"
                         ,xmlforest(TO_CHAR(a.xmdate, 'YYYYMMDD') AS "XMDATE"
                                   ,a.xmcode AS "XMCODE"
                                   ,a.xmdesc AS "XMDESC")
                         ,xmlcomment('  xmid->' || a.xmid))) || ''
  INTO l_xml
  FROM xmltest a;
              
END;

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 21 2014
Added on Sep 23 2014
2 comments
634 views