The right way to write large number of blobs to a directory

Question

I am using Oracle 11g Express Edition (XE) and SQL Developer. I wrote the following Stored Procedure as a proof of concept to write multiple BLOBs to my image_dir which is C:\TEMP. It works properly. I wonder if this is a proper way to write blobs in a very large scale (1 billion images) to a directory? What has been your experience? How long did it take for how many images?

create or replace PROCEDURE write_blob_to_file_Dcloud_v1
AS
   v_lob_image_name   VARCHAR (100);
   v_lob_image_id     NUMBER;
   v_blob             BLOB;
   v_buffer           RAW (32767);
   v_buffer_size      BINARY_INTEGER;
   v_amount           BINARY_INTEGER;
   v_offset           NUMBER (38) := 1;
   v_chunksize        INTEGER;
   v_out_file         UTL_FILE.file_type;

BEGIN
-- +-------------------------------------------------------------------------+
-- |            SELECT THE BLOBS FROM THE DATABASE TABLE                     |
-- +-------------------------------------------------------------------------+

   FOR i IN (SELECT 
             employee_id v_lob_image_id,
            (first_name || ' ' || last_name) AS v_lob_image_name,
             DBMS_LOB.GETLENGTH(v_blob) v_len,
             employee_picture v_blob
   FROM HR.employees)
   LOOP
-- +-------------------------------------------------------------------------------------+
-- |                      FIND OUT THE CHUNKSIZE FOR THIS BLOB COLUMN.                   |
-- | BUFFERSIZE IS USED TO PARSE CHUNKS OF DATA/BYTES FROM THE IMAGE.                    |
-- | IF NOT SPECIFIED, THE SYSTEM LEVEL MODULE WOULD READ MUCH SMALLER CHUNKS AT A TIME, |
-- | AND EACH READ AND WRITE IS AN EXPENSIVE OPERATION. SO IT IS BEST TO READ IN CHUNKS. |
-- | THE PURPOSE OF THE OUTER LOOP IS TO LOOP THRU ALL THE BLOBS IN THE TABLE. THE CODE  |
-- | WILL NEED TO READ THE IMAGE FROM THE BEGINNING SO INITIALIZING THE V_OFFSET TO 1,   |
-- | TAKE CARE OF THAT.                                                                  |
-- +-------------------------------------------------------------------------------------+
    v_offset := 1;
    v_chunksize := DBMS_LOB.getchunksize (i.v_blob);

    IF (v_chunksize < 32767)
      THEN
         v_buffer_size := v_chunksize;
      ELSE
         v_buffer_size := 32767;
     END IF;

     v_amount := v_buffer_size;

-- +---------------------------------------------------------------------------------------+
-- |OPEN THE BLOB. TO PREVENT NOT YET CLOSE OR ALREADY OPENED BLOBS, USE THE FOLLOWING CODE|
-- +---------------------------------------------------------------------------------------+   

    IF (dbms_lob.isopen(i.v_blob) = 0)
    THEN
       dbms_lob.open(i.v_blob,dbms_lob.lob_readonly); 
    END IF;
-- +---------------------------------------------------------------------------------------+
-- |                     WRITE CONTENTS OF THE BLOB TO A FILE                              |
-- +---------------------------------------------------------------------------------------+  

      v_out_file :=
         UTL_FILE.fopen (
            location       => 'IMAGE_DIR',
            filename       => (   ''
                               || i.v_lob_image_id
                               || '_'
                               || i.v_lob_image_name
                               || '.JPG'),
            open_mode      => 'wb',
            max_linesize   => 32767);
-- +---------------------------------------------------------------------------------------+
-- |THIS INNER LOOP (WHILE LOOP) IS TO TAKE EACH BLOB AND PROCESS IT. V_AMOUNT IS SET TO   |
-- |BUFFERSIZE. BUFFERSIZE IS USED TO PARSE CHUNKS OF DATA/BYTES FROM IMAGE. IT LOOPS THRU |
-- | UNTIL IT READS ALL THE CHUNKS OF AN IMAGE (READ THE ENTIRE IMAGE).                    |
-- +---------------------------------------------------------------------------------------+ 

      WHILE v_amount >= v_buffer_size
      LOOP
         DBMS_LOB.read (i.v_blob,
                        v_amount,
                        v_offset,
                        v_buffer);
         v_offset := v_offset + v_amount;
         UTL_FILE.put_raw (v_out_file, v_buffer, TRUE);
         UTL_FILE.fflush (v_out_file);
      END LOOP;

      UTL_FILE.fflush (v_out_file);
      UTL_FILE.fclose (v_out_file);

-- +---------------------------------------------------------------------------------------+
-- |CLOSE THE BLOB. TO PREVENT NOT YET CLOSE OR ALREADY OPENED BLOBS, USE THE FOLLOWING CODE|
-- +---------------------------------------------------------------------------------------+       

    IF ( dbms_lob.isopen(i.v_blob) = 1 )
    THEN 
       dbms_lob.close(i.v_blob); 
    END IF;
   END LOOP;
END;

Answer 1

In answer to:

I wonder if this is a proper way to write blobs in a very large scale

Sure. Proper. Whatever.

I think any architectural design of an application with a billion images, would decide either these are going to be stored in the database (as BLOB) or as references to a file system (as BFILE).

Never BOTH ways.

So, I believe your question about the efficacy of your posted code is a red herring. You already know it is fine. There are no silver bullets.

You are asking an architectural question without explaining in any meaningful detail what the requirements are - which is a solicitation of pure speculative opinion.