Oracle External LOB BFILE Data types Oracle Tips by Burleson Consulting

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:

GRANT READ ON DIRECTORY gif_dir TO system;

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.