Call now: 252-767-6166  
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 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.  Feel free to ask questions on our Oracle forum.

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. All legitimate Oracle experts publish their Oracle qualifications.

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.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational