BFILE
Oracle BFILE datatype is used to store binary objects outside the database in the OS file system. The BFILE has read-only access/privilege and cannot pariticpate in transactions. The file properties, integrity, etc. are maintained by the OS. The BFILE variable stores the file locator, which points to the OS directory setup as the Oracle database directory and the file name. Althought the file size is dependent on the OS, the maximum size of the file cannot exceed (4 GB - 4 bytes). The DBA ensures the existence of the BFILE and Oracle read permissions. The maximum number of open BFILEs in Oracle it is managed by the session initialization parameter SESSION_MAX_OPEN_FILES and should be within the limits of the OS.

BFILE Setup and Management
-- Table to store data in BFILE datatype
CREATE TABLE lob_detail 
(lob_data_id      NUMBER,
 lob_bfile        BFILE,
 created_date     DATE DEFAULT SYSDATE NOT NULL
);

-- Insert a binary file (jpg image file) using BFILENAME() and database directory ITPLANNING
INSERT INTO lob_detail(load_data_id, lob_bfile)
VALUES(1, BFILENAME('ITPLANNING', 'ITRoadMap2014v1.jpg'));

-- Replacing file by Updating the filename
UPDATE lob_detail 
   SET lob_bfile = BFILENAME('ITPLANNING', 'ITRoadMap2014v2.jpg')
WHERE  lob_data_id = 1;

The function below uses DBMS_LOB to get BFILE location. The function can be called from front end to get the file location as "/planning/ITRoadMap2014v2.jpg", which can be used in web application image tag source to render the image on a web page.
 
Get BFILE Path
CREATE OR REPLACE get_bfile_loc(i_lob_id IN lob_detail.lob_data_id%TYPE)
RETURN VARCHAR2 AS
 v_bfile           BFILE := NULL;
 v_dir_alias       VARCHAR2(30);
 v_file_src        VARCHAR2(200);
 v_invalid_dir     EXCEPTION;
 PRAGMA  EXCEPTION_INIT(v_invalid_dir, -20001);

BEGIN
 SELECT lob_bfile INTO v_bfile
 FROM  lob_detail
 WHERE  lob_data_id = i_lob_id;

 IF DBMS_LOB.fileexists(v_bfile) THEN
    DBMS_LOB.filegetname(v_bfile,v_dir_alias,v_file_src);
    v_file_src := '/'||LOWER(v_dir_alias)||'/'||v_file_src;
 ELSE
   v_file_src := '';   
 END IF;
 RETURN v_file_src

EXCEPTION
  WHEN v_invalid_dir THEN
  RETURN NULL;
END get_bfile_loc;
/

Oracle registered trademark of Oracle Corporation.

Last Revised on: February 23, 2014

  1401