write blob to file
- From: Jeff Chirco <JChirco@xxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 21 Aug 2012 15:43:06 +0000
We have pdf's stored in our database as blob as a secure file which is
encrypted and we want to write these blobs out to the file system. I have
written this procedure below but it is very slow. The files range from about
90kb to 500kb. I did a test with 10 files and it writes the first two files
instantly which happened to be the biggest files 440kb and 180kb but then
starts to slow down for each of the next files (which are all around 90kb) and
by the time it gets to the 10 file it is crawling. It took about 10 minutes
to all 10 files. Am I doing something wrong in this procedure? Is there a
better way?
Thanks
create or replace procedure SAVE_TO_FILE_FILE
IS
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob_len INTEGER;
CURSOR C1 IS SELECT dbID || '.pdf' file_name, dbdoc From scanner_user.documents
Where dbid IN (345710,
668760,
738977,
333767,
372326,
335241,
627734,
782436,
535041,
472812);
BEGIN
FOR I IN C1 LOOP
-- Get LOB locator
--SELECT dbdoc, dbid || '.pdf' INTO l_blob,MYFILE_NAME FROM
scanner_user.documents where dbID= I.dbID;
l_blob_len := DBMS_LOB.getlength(i.dbdoc);
l_pos:= 1;
-- Open the destination file.
l_file := UTL_FILE.fopen('DIR_EXPORT',i.FILE_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(i.dbdoc, 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_TO_FILE_FILE;
--
//www.freelists.org/webpage/oracle-l
Other related posts:
