Re: write blob to file

From: kevin jernigan <kevin.jernigan_at_oracle.com>
Date: Tue, 21 Aug 2012 10:12:35 -0700
Message-ID: <5033C183.4050003_at_oracle.com>

If you're on 11.2, you can use DBFS (and dbfs_client) to access your files. If you're on linux or Solaris 11, you can mount the files as though they're in a POSIX-style file system and access them that way. See here
<http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_fs.htm#BABDHGGJ> for more info.
-KJ

Kevin Jernigan
Senior Director Product Management
Advanced Compression, Hybrid Columnar
Compression (HCC), Database File System
(DBFS), SecureFiles, Database Smart Flash Cache, Total Recall, Database Resource
Manager (DBRM), Direct NFS Client (dNFS), Continuous Query Notification (CQN),
Index Organized Tables (IOT), Information Lifecycle Management (ILM)
(650) 607-0392 (o)
(415) 710-8828 (m)

On 8/21/2012 8:45 AM, Jeff Chirco wrote:
> Oh and I forgot to mention that I have 400,000 files to write!
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jeff Chirco
> Sent: Tuesday, August 21, 2012 8:43 AM
> To: oracle-l_at_freelists.org
> Subject: write blob to file
>
> 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;
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Aug 21 2012 - 12:12:35 CDT