Press "Enter" to skip to content

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:

Vinish Kapoor

I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me, it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub and get notifications for new posts.

You may also like:

Be First to Comment

    Leave a Reply

    Your email address will not be published. Required fields are marked *