Datatypes - Expert Oracle Database Architecture - page 574

Now the BFILE can be treated as if it were a LOB—because it is. For example:

EODA@ORA12CR1> select dbms_lob.getlength(os_file) from t;
DBMS_LOB.GETLENGTH(OS_FILE)
---------------------------
1056768

We can see the file pointed to is 1MB in size. Note that the use of MY_DIR in the INSERT statement was intentional.
If we use mixed case or lowercase, we would get the following:

EODA@ORA12CR1> update t set os_file = bfilename( 'my_dir', 'test.dmp' );
1 row updated.
EODA@ORA12CR1> select dbms_lob.getlength(os_file) from t;
select dbms_lob.getlength(os_file) from t
*
ERROR at line 1:
ORA-22285: non-existent directory or file for GETLENGTH operation
ORA-06512: at "SYS.DBMS_LOB", line 850

This example points out that DIRECTORY objects in Oracle are identifiers, and identifiers are stored in uppercase
by default. The BFILENAME built-in function accepts a string, and this string's case must match the case of the
DIRECTORY object exactly as stored in the data dictionary. So, we must either use uppercase in the BFILENAME function
or use quoted identifiers when creating the DIRECTORY object:

EODA@ORA12CR1> create or replace directory "my_dir" as '/tmp/';
Directory created.
EODA@ORA12CR1> select dbms_lob.getlength(os_file) from t;
DBMS_LOB.GETLENGTH(OS_FILE)
---------------------------
1056768

I recommend against using quoted identifiers; rather, use the uppercase name in the BFILENAME call. Quoted
identifiers are not usual and tend to create confusion downstream.
A BFILE (the pointer object in the database, not the actual binary file on disk) consumes a varying amount of
space on disk, depending on the length of the DIRECTORY object name and the file name. In the preceding example,
the resulting BFILE was about 35 bytes in length. In general, you'll find the BFILE consumes approximately 20 bytes of
overhead plus the length of the DIRECTORY object name plus the length of the file name itself.

■ BFILE data is not read consistent as other LOB data is. since the BFILE is managed outside of the database,
whatever happens to be in the file when you dereference the BFILE is what you will get. so, repeated reads from the
same BFILE may produce different results—unlike a LOB locator used against a CLOB , BLOB , or NCLOB .

Note