The bfilename built-in function accepts string

Chapter 12 ■ Datatypes

Both SecureFiles and BasicFiles have a full storage clause you can employ to control the physical storage characteristics. It should be noted that this storage clause applies to the LOBSEGMENT and the LOBINDEX equally—a setting for one is used for the other.

The last of the LOB types to talk about is the BFILE type. A BFILE type is simply a pointer to a file in the operating system. It is used to provide read-only access to these operating system files.

■Note the built-in package UTL_FILE provides read and write access to operating system files, too. It does not use

EODA@ORA12CR1> insert into t values ( 1, bfilename( 'MY_DIR', 'test.dmp' ) ); 1 row created.

For this example, I’ll create a test.dmp file in the /tmp directory using the UNIX/Linux dd command:

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.

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.

578