Re: Storing blobs in database vs filesystem

From: Sandeep Dubey <dubey.sandeep_at_gmail.com>
Date: Fri, 29 Sep 2006 14:02:50 -0400
Message-ID: <bf2f74740609291102k5239f50t1ff3b882f6e3ba87@mail.gmail.com>

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

Received on Fri Sep 29 2006 - 13:02:50 CDT