How to Read BLOB in PLSQL

Below code examples shows how to read a file in Oracle Directory into a BLOB in PLSQL.

Example 1:

Read file to a BLOB in plsql 1

.txt

Download TXT • 1KB

DECLARE
   dir_             VARCHAR2(100) := 'OUT_DIR';
   filename_        VARCHAR2(100) := 'Test e.pdf';
   blob_            BLOB;
   
   file_in_         UTL_FILE.File_Type;
   bytes_to_read_   PLS_INTEGER;
   buf_             RAW(32000);
   read_sofar_      PLS_INTEGER := 0;
   chunk_size_      CONSTANT PLS_INTEGER := 32000;
BEGIN
   file_in_  := UTL_FILE.FOPEN(dir_, filename_, 'rb');
   Dbms_Lob.Createtemporary(blob_, TRUE);
   
   BEGIN
      LOOP
         Utl_File.Get_Raw(file_in_, buf_, chunk_size_);
         bytes_to_read_ := LENGTH(buf_) / 2;
         Dbms_Lob.Write(blob_, bytes_to_read_, read_sofar_ + 1, buf_);
         read_sofar_ := read_sofar_ + bytes_to_read_;
         -- utl_file raises no_data_found when unable to read
      END LOOP;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;
   
   Utl_File.Fclose(file_in_);
   
   -- test save blob in a table
   UPDATE pdf_tab t
   SET    t.pdf = blob_
   WHERE  t.id = 4152;
END;

Example 2:

Read file to a BLOB in plsql 2

.txt

Download TXT • 1KB

DECLARE
   dir_        VARCHAR2(100) := 'OUT_DIR';
   filename_   VARCHAR2(100) := 'Test e.pdf';
   blob_       BLOB;
   bfile_      BFILE;
BEGIN
   bfile_ := BFILENAME(dir_, filename_);
   
   Dbms_LOB.Createtemporary(blob_, FALSE);
   Dbms_LOB.Fileopen(bfile_, Dbms_LOB.file_readonly);
   Dbms_LOB.Loadfromfile(blob_, bfile_, Dbms_LOB.Getlength(bfile_));
   Dbms_LOB.Fileclose(bfile_);
   
   -- test save blob in a table
   UPDATE pdf_tab t
   SET    t.pdf = blob_
   WHERE  t.id = 4152;
EXCEPTION
   WHEN OTHERS THEN
      IF (Dbms_LOB.Fileisopen(bfile_) = 1) THEN
         Dbms_LOB.Fileclose(bfile_);
      END IF;

      Dbms_LOB.Freetemporary(blob_);
      RAISE;
END;