This discussion is archived
10 Replies Latest reply: Dec 6, 2012 3:42 AM by 905250 RSS

save clob to file

pj*433620*ng Newbie
Currently Being Moderated
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 Guru Moderator
    Currently Being Moderated
    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
    MahirM.Quluzade Guru
    Currently Being Moderated
    CREATE DIRECTORY DOCUMENTS AS 'C:\DOCUMENTS';
    GRANT READ, WRITE ON DIRECTORY DOCUMENTS TO <YOUR USERNAME>;
  • 3. Re: save clob to file
    BluShadow Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi, Odie
    Thanks for you info, the procedure done all the trick.

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

Legend

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