10 Replies Latest reply: Dec 6, 2012 5:42 AM by 905250 RSS

    save clob to file

    pj*433620*ng
      Hi,
      I use the below plsql to save clob field into file, the code save the first 8-10 files in about 2 seconds and after that,
      it run slower and slower, about 10 file in 1 minutes, anyone can help to point out what could be the cause?

      Thanks
      Vincent


      create or replace procedure save_xml_log_to_file(vso in varchar2,vstation in varchar2)
      is
      l_file utl_file.file_type;
      l_buffer raw(32767);
      -- l_buffer varchar2(32767);
      l_amount binary_integer := 32767;
      l_pos integer := 1;
      l_blob blob;
      l_blob_len integer;
      myfile_name varchar2(250);

      cursor cl is
      select c.filename from cust_test_log c,tracking t where t.sn=c.sn and t.so=vso and t.ticket=c.ticket and t.station=vstation;

      begin

      for i in cl loop
      -- Get LOB locator
      select clob_to_blob(c.test_log_text), c.filename
      into l_blob, myfile_name
      from cust_test_log c
      where c.filename=i.filename;
      -- l_blob := i.test_log;
      -- myfile_name := i.filename;

      l_blob_len := dbms_lob.getlength(l_blob);
      l_pos := 1; --re-init l_pos var
      -- Open the destination file.
      l_file := utl_file.fopen('XML_LOG', myfile_name, 'wb', 32767);

      -- Read chunks of the BLOB and write them to the file
      -- until complete.
      while l_pos < l_blob_len
      loop
      dbms_lob.read(l_blob, l_amount, l_pos, l_buffer);
      utl_file.put_raw(l_file, l_buffer, true);
      l_pos := l_pos + l_amount;
      end loop;

      -- Close the file.
      utl_file.fclose(l_file);

      end loop;
      exception
      when others then
      -- Close the file if something goes wrong.
      if utl_file.is_open(l_file) then
      utl_file.fclose(l_file);
      end if;
      raise;
      end save_xml_log_to_file;
        • 1. Re: save clob to file
          BluShadow
          Why are you converting the CLOB to a BLOB?
          DECLARE
            l_file    UTL_FILE.FILE_TYPE;
            l_clob    CLOB;
            l_buffer  VARCHAR2(32767);
            l_amount  BINARY_INTEGER := 32767;
            l_pos     INTEGER := 1;
          BEGIN
            SELECT col1
            INTO   l_clob
            FROM   tab1
            WHERE  rownum = 1;
          
            l_file := UTL_FILE.fopen('DOCUMENTS', 'Sample2.txt', 'w', 32767);
          
            LOOP
              DBMS_LOB.read (l_clob, l_amount, l_pos, l_buffer);
              UTL_FILE.put(l_file, l_buffer);
              l_pos := l_pos + l_amount;
            END LOOP;
          EXCEPTION
            WHEN OTHERS THEN
              -- Do not really use WHEN OTHERS exceptions, this is just as an example.
              DBMS_OUTPUT.put_line(SQLERRM);
              UTL_FILE.fclose(l_file);
              RAISE;
          END;
          The process will always finish with a NO_DATA_FOUND exception when the end of the CLOB is reached. For simplicity I've not trapped any of the possible UTL_FILE exceptions.
          • 2. Re: save clob to file
            Mahir M. Quluzade
            CREATE DIRECTORY DOCUMENTS AS 'C:\DOCUMENTS';
            GRANT READ, WRITE ON DIRECTORY DOCUMENTS TO <YOUR USERNAME>;
            • 3. Re: save clob to file
              BluShadow
              Mahir M. Quluzade wrote:
              CREATE DIRECTORY DOCUMENTS AS 'C:\DOCUMENTS';
              GRANT READ, WRITE ON DIRECTORY DOCUMENTS TO <YOUR USERNAME>;
              Yes, I know how to create directory objects thank you. That has nothing to do with the OP's issue and isn't of any importance in this case.
              • 4. Re: save clob to file
                pj*433620*ng
                Hi,
                Is there any 32K limitation while saving clob to file?
                The content in my clob is larger than 32K.

                Thanks for your reply

                Vincent
                • 5. Re: save clob to file
                  Saubhik
                  Problem is here:
                  while l_pos < l_blob_len
                  loop
                  dbms_lob.read(l_blob, l_amount, l_pos, l_buffer);
                  utl_file.put_raw(l_file, l_buffer, true);
                  l_pos := l_pos + l_amount;
                  end loop;
                  
                  -- Close the file.
                  utl_file.fclose(l_file);
                  You need to reinitialize l_amount also (I saw you have reinitialized the other two variables).
                  • 6. Re: save clob to file
                    BluShadow
                    pj**** wrote:
                    Hi,
                    Is there any 32K limitation while saving clob to file?
                    The content in my clob is larger than 32K.
                    No, there's no 32K limit, only that UTL_FILE can only write out in 32K chunks at any one time, so you have to loop and keep writing out chunks until it's all written out.
                    • 7. Re: save clob to file
                      pj*433620*ng
                      Hi,
                      I tried your code but only 32K of the clob is saved..
                      Btw, I managed to improve the code by extract the step of saving blob to file into one procedure (save_blob_to_file)
                      and call this procedure in a loop.

                      Thanks

                      Vincent
                      • 8. Re: save clob to file
                        odie_63
                        Hi Vincent,

                        What about using DBMS_XSLPROCESSOR.clob2file?
                        It'll do all the job for you, with a single call.
                        DBMS_XSLPROCESSOR.clob2file(
                          cl => l_clob
                        , flocation => 'XML_LOG'
                        , fname => myfile_name
                        );
                        • 9. Re: save clob to file
                          pj*433620*ng
                          Hi, Odie
                          Thanks for you info, the procedure done all the trick.

                          Vincent Pek
                          • 10. Re: save clob to file
                            905250
                            perfect, thanks for posting this, highly appreciate.