Re: Storing blobs in database vs filesystem

Reading and writing to filesyatem is done using oracle dbms_lob
package. Java application (using JDBC) calls oracle stored procedure
to read or write to file system. To read and write blob to table Java
uses prepared statement to do so.

To write to file system belwo is the relevant part

blob_length := DBMS_LOB.GETLENGTH(p_blob);
  out_file := UTL_FILE.FOPEN(v_dir_name, p_file_name, 'wb', chunk_size);

  WHILE blob_position <= blob_length LOOP
     IF blob_position + chunk_size - 1 > blob_length
     THEN
       chunk_size := blob_length - blob_position + 1;
     END IF;
     DBMS_LOB.READ(p_blob, chunk_size, blob_position, v_buffer);
     UTL_FILE.PUT_RAW(out_file, v_buffer, TRUE);
     blob_position := blob_position + chunk_size;
   END LOOP;
   UTL_FILE.FCLOSE(out_file);


To read from the file

src_file := bfilename(v_dname, p_global_pedigree_id);
 -- open the file
 dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
 -- determine length
 length_file := dbms_lob.getlength(src_file);
 -- read the file
 dbms_lob.loadfromfile(p_blob, src_file, length_file);


Sandeep


You need to describe you test better for us to understand (and explain)
this performance delta. What are the mechanics for getting to
the filesystem data and what (in pseudo code) are the points in
which you are taking you measurements?

Very intersting work!
--
http://www.freelists.org/webpage/oracle-l



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


Other related posts: