CODEWITHSUNDEEP
c#-4.0oracle11g
user3651810
user3651810

Reputation: 129

creating xml file using c# and oracle

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.

Upvotes: 0

Views: 799

Answers (2)

AleRV
AleRV

Reputation: 11

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;

Upvotes: 1

peter.hrasko.sk
peter.hrasko.sk

Reputation: 4141

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#.

Upvotes: 0

Related Questions