Here is an example of the Oracle PL/SQL program to convert an external file to BLOB.

Oracle PL/SQL Function Example to Convert an External File to BLOB

In Oracle, you can reference an external file using the BFILENAME() function by specifying the Oracle Directory object and filename. Then Oracle looks for the file in the specified directory and returns the LOB locater (BFILE). Which further can be converted to BLOB data so that it can be saved in a table.

The following PL/SQL function takes two parameters, first, Oracle directory object name, and second, the file name. Then it gets the file from the location and converts it to BLOB, and returns the BLOB.

CREATE OR REPLACE FUNCTION file_to_blob (i_dir IN VARCHAR2, i_filename IN VARCHAR2)
   RETURN BLOB
AS
   l_bfile   BFILE;
   l_blob    BLOB;
BEGIN
   DBMS_LOB.createtemporary (l_blob, FALSE);
   l_bfile := BFILENAME (i_dir, i_filename);
   DBMS_LOB.fileopen (l_bfile, DBMS_LOB.file_readonly);
   DBMS_LOB.loadfromfile (l_blob, l_bfile, DBMS_LOB.getlength (l_bfile));
   DBMS_LOB.fileclose (l_bfile);

   RETURN l_blob;
EXCEPTION
   WHEN OTHERS
   THEN
      IF DBMS_LOB.fileisopen (l_bfile) = 1
      THEN
         DBMS_LOB.fileclose (l_bfile);
      END IF;

      DBMS_LOB.freetemporary (l_blob);
        RETURN EMPTY_BLOB();
END file_to_blob;

Below is an example of the above function to get the BLOB from a file and insert it into the table.

Example

Declare
  b_blob blob;
Begin
   b_blob := file_to_blob('MY_DIR', 'MY_FILE.PDF');
   insert into emp_files (empno, efile)
     values (1234, b_blob);
   Commit;
End;