3 Replies Latest reply: Sep 25, 2013 6:10 AM by odie_63 RSS

    Partial xml file with clob2file


      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


           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);                 


                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


                      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 loop;      



             when others then


         end export_multi;


        • 1. Re: Partial xml file with clob2file

          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 ?


            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;



          • 2. Re: Partial xml file with clob2file

            1. DB Version - Oracle Database 11g Enterprise Edition Release - 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

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