• Post author:
  • Post category:PLSQL
  • Reading time:2 mins read

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

Advertisement

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.

Advertisement
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:

Related:  Read And Import CSV Files In Oracle PL/SQL Efficiently

Vinish Kapoor

An Oracle Apex Consultant, Oracle ACE, and founder of foxinfotech.in and orclqa.com a question and answer forum for developers.

This Post Has 2 Comments

  1. himansu

    if i am running this process in apex application, it's not showing the image ..
    can you guide.

  2. Adriano Jose Dias

    Thank you Vinish. Solved my problem of sending a file to FTP. Excellent solution.

Comments are closed.