create or replace function sunten_test_xml_clob return clob is
ctx dbms_xmlgen.ctxhandle;
ctxdept dbms_xmlgen.ctxhandle;
xml clob;
xmldept clob;
xmldeptemp clob;
xmldeptemp1 clob;
emp_no number := 7369;

xmlc varchar2(4000); -- required to convert lob to varchar2
off pls_integer := 1;
len pls_integer := 4000;

BEGIN
xml := to_clob(' ');
xmlDept := to_clob(' ');
xmlDeptEmp := to_clob('');
for c1 in (select * from dept) loop
ctxDept := dbms_xmlgen.newContext('SELECT DEPTNO DNO,DNAME,LOC FROM dept where deptno = '|| c1.deptno);
dbms_xmlgen.setRowSetTag(ctxDept, 'DEPTMENT');
dbms_xmlgen.setRowTag(ctxDept, null);
xmlDept := dbms_xmlgen.getXML(ctxDept);
dbms_xmlgen.closeContext(ctxDept);
xmlc := replace(dbms_lob.substr(xmlDept,dbms_lob.getlength(xmlDept)),'','');
xmlc := replace(xmlc,'','');
if xmlc is not null then
dbms_lob.append(xmlDeptEmp,to_clob(xmlc));
end if;

ctx := dbms_xmlgen.newContext('SELECT * FROM emp WHERE deptno = '|| c1.deptno);
dbms_xmlgen.setRowSetTag(ctx, 'EMPLOYEES');
dbms_xmlgen.setRowTag(ctx, 'EMPLOYEE');
xml := dbms_xmlgen.getXML(ctx);
dbms_xmlgen.closeContext(ctx);
if dbms_lob.getlength(xml) > 0 then
xmlc := replace(dbms_lob.substr(xml,dbms_lob.getlength(xml)),'','');
else
xmlc := '';
end if;
xmlc := xmlc||'';
dbms_lob.append(xmlDeptEmp,to_clob(xmlc));
end loop;
dbms_lob.append(xmlDeptEmp,to_clob(''));
dbms_output.put_line(xmlDeptEmp);
return xmlDeptEmp;
END;
/