This discussion is archived
3 Replies Latest reply: Sep 25, 2013 4:10 AM by odie_63 RSS

Partial xml file with clob2file

user12020576 Newbie
Currently Being Moderated

Need to export XML files from XMLTYPE column in a loop.

When i executed the following stored proc with lot of rows, i found some of the files are partial,  why is that?

When i run the SP multiple times, failed/partial XML files are same every time.

Do i need to reset/flush with clob2file in the loop?

Any suggestions?

 

------------------------------------------------------------------------------------------------------------------------------------------------------------

procedure export_multi

is

     l_theCursor     integer default dbms_sql.open_cursor;

     l_id               INTEGER;

     l_xmlValue      XMLTYPE;

     l_status        integer;

     l_query         varchar2(2000)

                     default 'select rownum, xml_data from tablex';

     l_filename      varchar2(100);                 

    begin

          dbms_sql.parse(  l_theCursor,  l_query , dbms_sql.native );

          DBMS_SQL.DEFINE_COLUMN( L_THECURSOR, 1, l_id);

          DBMS_SQL.DEFINE_COLUMN( L_THECURSOR, 2, l_xmlValue);

          l_status := dbms_sql.execute(l_theCursor);

      

          while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop

             BEGIN

                DBMS_SQL.COLUMN_VALUE( L_THECURSOR, 1, l_id );

                DBMS_SQL.COLUMN_VALUE( L_THECURSOR, 2, l_xmlValue );

                l_filename := 'test' || l_id || '.xml' ;

               dbms_xslprocessor.clob2file(l_xmlValue.getClobVal(), '/folder',  l_filename);

             END;

          end loop;      

          dbms_sql.close_cursor(l_theCursor);

    exception

       when others then

        raise;

   end export_multi;

------------------------------------------------------------------------------------------------------------------------------------------------------------

  • 1. Re: Partial xml file with clob2file
    odie_63 Guru
    Currently Being Moderated

    A few questions to help us identifying the problem :

     

    What's the db version ?

    How large are your XML files ?

    Are they truncated to the same size ?

     

    Why are you using dynamic SQL here ? I don't see any reason for it. Why not just a simple CURSOR FOR LOOP ?

    BEGIN

      FOR r IN ( select rownum id, xml_data from tablex ) LOOP

        dbms_xslprocessor.clob2file(r.xml_data.getClobVal(), 'XML_DIR', 'test' || r.id || '.xml');

      END LOOP;

    END;

    /

  • 2. Re: Partial xml file with clob2file
    user12020576 Newbie
    Currently Being Moderated

    1. DB Version - Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit

    2. Failed XML file size varies -- 36077 bytes -  97484 bytes

    3, They are not truncated to same size, but noticed all of the failed files are loosing last few characters of the last line  Ex: "</rootEleme"

     

    I have tried with the CURSOR and i still see the issue.

    And i found files causing this issue, but not sure whats the issue..

  • 3. Re: Partial xml file with clob2file
    odie_63 Guru
    Currently Being Moderated

    Is there a way to reproduce the issue on our side ? (test case?)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points