I have to generate set of xml files by querying oracle db table based on some condition. the xml file structure little complex and their is a limit on file size if limit croees new file need to be created. what is the best approch to this. using select XMLElemnt in oracle plsql or doin it in C# after querying data from db using cursor? performamce also to be considered and this process need to be done faster.
2 Answers
In oracle 11.2 you can transform the results from a ref cursor into a XML(CLOB) using dbms_xmlgen.
EXAMPLE:
PROCEDURE GetXMLByRefCursor
(
irfCursor in REF CURSOR,
oclXMLDocument out nocopy clob,
isbRootTag in varchar2 default null,
isbResultTag in varchar2 default null,
inuNullHandling in number default dbms_xmlgen.DROP_NULLS
)
IS
nuContext dbms_xmlgen.ctxHandle;
BEGIN
nuContext:= dbms_xmlgen.newContext(irfCursor);
dbms_xmlgen.setPrettyPrinting(nuContext, false);
dbms_xmlgen.setNullHandling(nuContext, inuNullHandling);
if (isbRootTag is not null) then
dbms_xmlgen.setRowSetTag(nuContext, isbRootTag);
end if;
if (isbResultTag is not null) then
dbms_xmlgen.setRowTag(nuContext, isbResultTag);
end if;
oclXMLDocument := dbms_xmlgen.getXML(nuContext);
dbms_xmlgen.closeContext(nuContext);
END GetXMLFromRefCursor;
Comments
In PLSQL you cannot easily manage resulting XML size.
Performance-wise, it can be better either in PLSQL or in C#, that depends on the speed of the .NET-side machine and the speed of the OracleDB-side machine.
Also, if you create XML in PLQSL and then return in to the caller C# tier, you'll have to do it via CLOBs. Since this would involve temporary CLOBs, some copying in the Oracle's temp tablespace might be involved, thus decreasing performance.
All in all, I suggest doing it rather in C#.
