Re: Storing blobs in database vs filesystem
- From: "Sandeep Dubey" <dubey.sandeep@xxxxxxxxx>
- To: kevinc@xxxxxxxxxxxxx
- Date: Fri, 29 Sep 2006 14:02:50 -0400
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!
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
