BLOB File operations - Sample Code

Dear Reader

Many of us come across scenarios where we need to perform file operations with BLOB data

This may be for reading a BLOB/CLOB into a database table or to write the contents into the file system

To make things simple, below is the sample code which can be used for both the scenarios.

PS: We need to make sure that the directory into which we are writing/reading the file is valid for utl_file operations


Writing a LOB data into a file in server

DECLARE

   lv_file              UTL_FILE.FILE_TYPE;

   lv_file_dir          VARCHAR2(240) := '/usr/tmp';

   lv_blob_data         BLOB;

   lv_start_pos         NUMBER := 1;

   lv_byte_length       NUMBER := 32000;

   lv_blob_data_length  NUMBER;

   lv_trim_blob_data    RAW(32000);

   lv_temp_pos          NUMBER;

BEGIN

   lv_file := utl_file.fopen(lv_file_dir, 'Write_BLOB_file.pdf','wb', 32760);

   lv_start_pos := 1;

   lv_byte_length := 32000;

   SELECT dbms_lob.getlength(file_data)

   INTO   lv_blob_data_length

   FROM   FND_LOBS

   WHERE  file_id = 7868798;

   lv_temp_pos := lv_blob_data_length;

   SELECT file_data

   INTO   lv_blob_data

   FROM   FND_LOBS

   WHERE  file_id = 7868798;

   IF lv_blob_data_length < 32760 THEN

      utl_file.put_raw(lv_file, lv_blob_data);

      utl_file.fflush(lv_file);

   ELSE

      lv_start_pos := 1;

      WHILE lv_start_pos < lv_blob_data_length and lv_byte_length > 0 LOOP

         dbms_lob.read(lv_blob_data, lv_byte_length, lv_start_pos, lv_trim_blob_data);

         utl_file.put_raw(lv_file, lv_trim_blob_data);

         utl_file.fflush(lv_file);

         lv_start_pos := lv_start_pos + lv_byte_length;

         lv_temp_pos := lv_temp_pos - lv_byte_length;

         IF lv_temp_pos < 32000 THEN

            lv_byte_length := lv_temp_pos;

         END IF;

      END LOOP;

   END IF;

   utl_file.fclose(lv_file);

END;


Reading and Loading a LOB data from a file in server to a database table

DECLARE

   lv_file_dir    VARCHAR2(100) := '/usr/tmp';

   dest_loc       BLOB;

   src_loc        BFILE;

   v_content_type VARCHAR2(256);

   v_language     VARCHAR2(4);

   v_charset      VARCHAR2(30);

   v_format       VARCHAR2(10);

   v_file_name    VARCHAR2(240);

   v_file_id      NUMBER;

BEGIN

   SELECT   FND_LOBS_S.NEXTVAL

   INTO     v_file_id

   FROM     DUAL;

   v_content_type := 'application/pdf';

   v_language := 'en';

   v_charset := 'utf-8';

   v_format := 'PDF';

   v_file_name := 'Read_BLOB_PDF.pdf';

   src_loc := BFILENAME(lv_dir_name, v_file_name);

   INSERT INTO fnd_lobs

   (

      file_id

      ,file_name

      ,file_content_type

      ,file_data

      ,upload_date

      ,expiration_date

      ,program_name

      ,program_tag

      ,language

      ,oracle_charset

      ,file_format

   )

   VALUES

   (

       v_file_id

      ,v_curr_label

      ,v_content_type

      ,EMPTY_BLOB()

      ,SYSDATE

      ,NULL

      ,'FNDATTCH'

      ,NULL

      ,v_language

      ,v_charset

      ,v_format

   ) RETURNING file_data INTO dest_loc;

   DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);

   DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);

   DBMS_LOB.LOADFROMFILE

   (

         dest_lob => dest_loc

      ,  src_lob  => src_loc

      ,  amount   => DBMS_LOB.getLength(src_loc)

   );

   DBMS_LOB.CLOSE(dest_loc);

   DBMS_LOB.CLOSE(src_loc);

END;

I hope this helps you all

Cheers
A