The BFILE data type stores unstructured binary data in operating system files outside the database. A BFILE column or attribute stores a file locator that points to the external file containing the data. The file to be loaded as a BFILE does not have to exist at the time of loading; it can be created later. SQL*Loader assumes that the necessary directory objects have already been created (a logical alias name for a physical directory on the server's file system). For more information, see the Oracle Database SecureFiles and Large Objects Developer's Guide.
A control file field corresponding to a BFILE column consists of a column name followed by the BFILE clause. The BFILE clause takes as arguments a directory object (the server_directory alias) name followed by a BFILE name. Both arguments can be provided as string constants, or they can be dynamically loaded through some other field. See the Oracle Database SQL Language Reference for more information.
In the next two examples of loading BFILEs, Example 11-22 has only the file name specified dynamically, while Example 11-23 demonstrates specifying both the BFILE and the directory object dynamically.
Example 11-22 Loading Data Using BFILEs: Only File Name Specified Dynamically
Control File Contents
LOAD DATA
INFILE sample.dat
INTO TABLE planets
FIELDS TERMINATED BY ','
(pl_id CHAR(3),
pl_name CHAR(20),
fname FILLER CHAR(30),
1 pl_pict BFILE(CONSTANT "scott_dir1", fname))
Data File (sample.dat)
1,Mercury,mercury.jpeg, 2,Venus,venus.jpeg, 3,Earth,earth.jpeg,
Example 11-23 Loading Data Using BFILEs: File Name and Directory Specified Dynamically
Control File Contents
LOAD DATA
INFILE sample.dat
INTO TABLE planets
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(pl_id NUMBER(4),
pl_name CHAR(20),
fname FILLER CHAR(30),
1 dname FILLER CHAR(20),
pl_pict BFILE(dname, fname) )
Data File (sample.dat)
1, Mercury, mercury.jpeg, scott_dir1, 2, Venus, venus.jpeg, scott_dir1, 3, Earth, earth.jpeg, scott_dir2,