BFILE in Oracle

Here is a table I will use for the rest of this entry:

CREATE TABLE large_objects (
char_data CLOB,
binary_data BLOB,
ext_binary_data BFILE
)

A BFILE is a binary file stored outside the database. You may want to manage your pictures or documents externally and be able to reference them internally. In that case you would use a BFILE. In a BFILE column in the database, you actually store the locator to the BFILE rather than the BFILE itself.

To use a BFILE you need to create a database directory first. Thise database directory points to an OS directory on your SERVER (not your local drive):

create directory lob_data as 'c:\temp'

insert into large_objects (ext_binary_data) values (bfilename('LOB_DATA', 'logan.jpg'));

select dbms_lob.getlength(ext_binary_data) len_bin_data from large_objects

LEN_BIN_DATA
------------
64988

1 row selected.