BFILE in Oracle and bfilename function

BFILE in oracle are powerful alternate to LOB data types. BFILE stands for binary file. The difference between BFILE and other LOB data types is it will not be stored in database. BFILE is a file stored in your operating system and is a read only object with in database. This will be useful to point supporting documents or any physical file. For example, in a insurance application we can store the copy of application stored in a image file to Oracle using BFILE data type. Advantage here is DBAs will get rid of space maintenance activities.

For using BFILE, we must have a database directory defined for the directory where our files reside.

Example

1) Create Directory Object
CREATE OR REPLACE DIRECTORY profile_dir AS 'c:\profile_photos';

GRANT ALL ON DIRECTORY profile_dir TO PUBLIC;

2) Create Table with BFILE Data Type

CREATE TABLE play_ground.people_prof
( p_key NUMBER(16) PRIMARY KEY,
p_name VARCHAR2(50),
p_short_name VARCHAR2(10),
city VARCHAR2(30),
remarks VARCHAR2(200),
photo_file BFILE

);

3) INSERT ROW with BFILE using BFILENAME function

INSERT
INTO play_ground.people_prof
VALUES (1, 'Selvi Rajan', 'SELVI', 'Chennai', 'Checkout the screenshot', BFILENAME('PROFILE_DIR', '1.jpg'));

4) Check the data in SQL Developer (Refer the screen Shots)
SELECT *
FROM play_ground.people_prof;


If you use SQL Developer, you can view the file using the browse/edit button in the BFILE column. You can view either a text or image using this option. I will explore in some other topic upon opening other file formats.