Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004

    Unanswered: Oracle stored proc and XML

    Hi gurus,
    I want to insert bulk [say,1000 records] from UI to oracle DB in a single hit.

    I did the same in MS SQL server, it was amazing.

    Build xml string and pass the xml as parameter to Stored proc.

    Is there a similar(optimal) way in Oracle?,if yes please post the sample code.
    plz tell me the pros and cons


  2. #2
    Join Date
    Oct 2002
    Cape Town, South Africa


    create or replace procedure testxmlinsert(p_xmlstring in clob) as
       l_hnd dbms_xmlsave.ctxtype;
       l_rowcount number(10, 0);
       l_hnd := dbms_xmlsave.newcontext('TABLE_NAME');
       dbms_xmlsave.setbatchsize(l_hnd, 10000); -- If you don't set this, then you may experience performance problems.... I did
       dbms_xmlsave.setdateformat(l_hnd, 'yyyy-MM-dd HH:mm:ss'); -- must be any java date format
       -- dbms_xmlsave.setxslt(l_hnd, l_ss_clob);   -- set this if you need an XSLT to get the xml into Oracle format
       -- dbms_xmlsave.setxsltparam(l_hnd, 'my_param_name', '"my_param_value"'); -- set this if you need XSLT parameters
       l_rowcount := dbms_xmlsave.insertxml(l_hnd, l_clob);
    end testxmlinsert;
    If your xml contains no rows, you will get an exception.

    There is another package called dbms_xmlstore which is the Oracle recommended package, but I have found that there were one or two little bugs hiding in the xmlstore package. For instance, having a single quote in your data could cause the package to crash or in the difference between <tag></tag> and <tag/>. Maybe it's fixed, maybe I was smoking something. I just remember I decided to stick with dbms_xmlsave.

    Oh yes, with dbms_xmlstore, you cannot set a date format. You have to use an alter session command before to change the system date format. With dbms_xmlsave, you are able to set a date format, however, it needs to be a valid Java date format.

    Also your xml has to be in Oracle format:
           <COL>any value</COL>
    Check out dbms_xmlsave and dbms_xmlstore packages for more.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts