Call now: 919-335-6342  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle External LOB BFILE Data types

Oracle Database Tips by Donald Burleson

External datatypes can be of any format, text, graphic, GIF, TIFF, MPEG, MPEG2, JPEG, etc. When read into the database they will have to be moved into a compatible format (BLOB, CLOB or NCLOB).

The BFILE datatype is used to act as a pointer or locator for the actual external data files. The BFILE datatype is a two part locator, the first part is the file name and the second part is the internal alias for the full path directory where the files reside. The directory alias is defined with the CREATE DIRECTORY command:

CREATE OR REPLACE DIRECTORY gif_dir AS '/usr/graphics/gif';

Once a directory alias is established, users are granted access through an object grant on the directory alias via the GRANT command:


BFILEs can be up to four gigabytes in length or up to the maximum size of files for your system whichever is smaller. BFILEs are capable of piece-wise read only. To perform any manipulations of BFILE data the BFILE locator is used to open the BFILE physical file and the DBMS_LOB package is then used to read the contents into an internal LOB. The Server Image Cartridge provides a method to write LOBs back out to physical files if you have the cartridge installed. Alternatively, JAVA or another language interface can be used to write the LOB data back to the system files.

The BFILENAME () function must be called as part of SQL INSERT to initialize a BFILE column or attribute for a particular row by associating it with a physical file in the server's filesystem.

The DIRECTORY object represented by the directory_alias parameter to the BFILENAME() function must already be defined using SQL DDL before this function is called in a SQL DML statement or a PL/SQL program.

However, BFILENAME() does not validate privileges on this DIRECTORY object, or check if the physical directory that the DIRECTORY object represents actually exists. These checks are performed only during file access using the BFILE locator that is initialized by the BFILENAME() function.

You can use BFILENAME() as part of a SQL INSERT and UPDATE statement to initialize a BFILE column. You can also use it to initialize a BFILE locator variable in a PL/SQL program, and use that locator for file operations. However, if the corresponding directory alias and/or filename does not exist, then PL/SQL DBMS_LOB routines that use this variable will generate errors.


Oracle BFILENAME tips


BFILENAME function returns a BFILE locator that is associated with a physical LOB binary file on the server file system. The syntax of the function is given as below.


BFILENAME('directory', 'filename')


·       ‘directory’ argument is a database object that serves as an alias for a full path name on the server file system where the files are actually located.

·       ‘filename’ is the name of the file in the server file system.

·       The directory object has to be created and a BFILE value has to be associated with a physical file before they are used as arguments to BFILENAME in a SQL statement, PL/SQL block, DBMS_LOG package or OCI operation.


This function can be used in two ways:


·       To initialize a BFILE column in a DML statement.

·       To access BFILE data by assigning a value to the BFILE locator in a programmatic interface.


The directory argument is case sensitive. The directory object name specified in the function and in the data dictionary must exactly be the same. The filename argument must be specified according to the case and punctuation conventions of the operating system. The following SQL statement depicts the usage of the BFILENAME function.


CREATE DIRECTORY Screenshots_Dir AS '/Screenshots';


Directory Created.


create table Screenshots (imgid number(3), screenimage  BFILE);


Table created.


insert into screenshots values (101, 


1 row created.


select BFILENAME('Screenshots_Dir','scrimg1.jpeg') from dual;




bfilename('Screenshots_Dir', 'scrimg1.jpeg')


Large Object Functions


Large Object (LOB) is a built-in data type in Oracle. The data types Binary Large Object (BLOB), Character Large Object (CLOB), National Character Large Object (NCLOB) and BFILE can be used to store large and unstructured data such as text, image, video, and spatial data. LOB columns contain LOB locators that can refer to internal (in the database) or external (outside the database) LOB values.  The LOB locator is returned by selecting a LOB from a table.  This locator is used for the DBMS_LOB package and Oracle Call Interface (OCI) operations on LOBs. The large object functions usually operate on the LOB data.



Advanced PL/SQL Programming
The Definitive Reference

Boobal Ganesan

Retail Price $49.95 /  £39.95

Buy Direct from Publisher for 30% off!    $32.95

  Buy it Now!






Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. 

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Please  e-mail:  

and include the URL for the page.



Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2023

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.