External LOBs (BFILEs), 14 of 41

Loading Data Into External LOB (BFILE)

Figure 12-10 Use Case Diagram: Loading Initial Data into External LOB (BFILE)

Text description of adl12bfa.gif follows

Text description of the illustration adl12bfa.gif

See Also:

"Use Case Model: External LOBs (BFILEs)" for all basic operations of External LOBs (BFILES).

Purpose

This procedure describes how to load initial data into a BFILE and the BFILE data into a table.

Usage Notes

The BFILE datatype stores unstructured binary data in operating-system files outside the database.

A BFILE column or attribute stores a file locator that points to a server-side external file containing the data.

Note:

A particular file to be loaded as a BFILE does not have to actually exist at the time of loading.

The SQL Loader assumes that the necessary DIRECTORY objects (a logical alias name for a physical directory on the server's filesystem) have already been created.

See Also:

Oracle9i Application Developer's Guide - Fundamentals for more information on BFILES.

A control file field corresponding to a BFILE column consists of column name followed by the BFILE directive.

The BFILE directive takes as arguments a DIRECTORY object name followed by a BFILE name. Both of these can be provided as string constants, or they can be dynamically sourced through some other field.

Syntax

Use the following syntax references:

SQL Loader (Oracle9i Database Utilities)
Chapter 4, "Managing LOBs", Using SQL*Loader to Load LOBs

Scenario

The following two examples illustrate the loading of BFILES. In the first example only the file name is specified dynamically. In the second example, the BFILE and the DIRECTORY object are specified dynamically.

Note:
You may need to set up the following data structures for certain examples to work:
CONNECT system/manager GRANT CREATE ANY DIRECTORY to samp; CONNECT samp/samp CREATE OR REPLACE DIRECTORY detective_photo as '/tmp'; CREATE OR REPLACE DIRECTORY photo_dir as '/tmp';

Examples

The following examples load data into BFILES:

Loading Data Into BFILES: File Name Only is Specified Dynamically

Control File

LOAD DATA
INFILE sample9.dat
INTO TABLE Multimedia_tab
FIELDS TERMINATED BY ','
(Clip_ID     INTEGER EXTERNAL(5),
 FileName    FILLER CHAR(30),
 Photo       BFILE(CONSTANT "DETECTIVE_PHOTO", FileName))

Data file (sample9.dat)

007, JamesBond.jpeg,
008, SherlockHolmes.jpeg,
009, MissMarple.jpeg,







Note:



Clip_ID defaults to (255) if a size is not specified. It is mapped to the file names in the datafile. DETECTIVE_PHOTO is the directory where all files are stored. DETECTIVE_PHOTO is a DIRECTORY object created previously.

Loading Data into BFILES: File Name and DIRECTORY Object Dynamically Specified

Control File

LOAD DATA
INFILE sample10.dat
INTO TABLE Multimedia_tab
replace
FIELDS TERMINATED BY ','
(
 Clip_ID   INTEGER EXTERNAL(5),
 Photo     BFILE (DirName, FileName),
 FileName  FILLER CHAR(30),
 DirName   FILLER CHAR(30)
)

Data file (sample10.dat)

007,JamesBond.jpeg,DETECTIVE_PHOTO,
008,SherlockHolmes.jpeg,DETECTIVE_PHOTO,
009,MissMarple.jpeg,PHOTO_DIR,

Note:

DirName FILLER CHAR (30) is mapped to the datafile field containing the directory name corresponding to the file being loaded.