How to Get BLOB from File in PL/SQL?

Below I am giving a function example to get the BLOB from a file in PL/SQL.

PL/SQL Function to Get BLOB from a File

The following function will read a file and will return the BLOB data type of the file. The function GET_BLOB takes two parameters: (1) Oracle directory object name (2) file name.

CREATE OR REPLACE FUNCTION get_blob (i_dir IN VARCHAR2, i_file IN VARCHAR2)
   RETURN BLOB
AS
   l_bfile   BFILE;
   l_blob    BLOB;
BEGIN
   DBMS_LOB.createtemporary (l_blob, FALSE);
   l_bfile := BFILENAME (i_dir, i_file);
   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);
      RAISE;
END get_blob;

Test

Suppose you have a file myimage.jpg in C:\Pics folder, then create an Oracle directory object, for example, MY_PICS for folder C:\Pics and call the below function by passing the directory name MY_PICS and the file name myimage.jpg. If you don't know how to create a directory object in Oracle, check this link: Create Oracle Directory Object.

DECLARE
   f_blob   BLOB;
BEGIN
   f_blob := get_blob ('MY_PICS', 'myimage.jpg');
END;

Now you have the BLOB of file myimage.jpg in variable f_blob.

See also:

• How to Insert a File in Oracle Database?