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

    Partial xml file with clob2file

    user12020576

      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

          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

            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

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