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;