Loading LOBs (CLOB, BLOB, BFILE)

Large Object columns — CLOB (character), BLOB (binary), and BFILE (file-pointer) — require special handling in sqlldr. The data may be inline in the CSV, or (more commonly) stored in separate files on disk with the main data file containing just filenames.

Inline CLOB from the data file

For small CLOBs (< ~64 KB) that live directly in the data file:

LOAD DATA
INFILE 'articles.csv'
APPEND
INTO TABLE cms.articles
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  article_id  INTEGER EXTERNAL,
  title       CHAR(200),
  body        CHAR(65535)      -- CLOB stored inline in CSV
)

CHAR(n) with a large n will hold CLOB data up to the specified byte limit. For data bigger than 65535 bytes, use LOBFILE.

LOBFILE — each row's LOB is a separate file

When each LOB value is stored in its own file on disk, use LOBFILE:

LOAD DATA
INFILE 'articles_meta.csv'
APPEND
INTO TABLE cms.articles
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
  article_id  INTEGER EXTERNAL,
  title       CHAR(200),
  filename    FILLER CHAR(300),
  body        LOBFILE(filename) TERMINATED BY EOF
)

The control file reads the CSV, and the body column loads its data from the file named in filename. TERMINATED BY EOF means "read to the end of the file".

Example articles_meta.csv:

1001,Introduction to Oracle,/data/lobs/article_1001.txt
1002,Advanced SQL Tuning,/data/lobs/article_1002.txt
1003,PL/SQL Best Practices,/data/lobs/article_1003.txt

LOBFILE with a fixed path

When all LOB files are in the same directory:

(
  doc_id     INTEGER EXTERNAL,
  doc_name   CHAR(100),
  lob_fname  FILLER CHAR(200),
  content    LOBFILE(lob_fname) TERMINATED BY EOF NULLIF lob_fname = BLANKS
)

Or hardcode a path prefix using a SQL expression:

(
  doc_id    INTEGER EXTERNAL,
  fname     FILLER CHAR(100),
  content   LOBFILE(fname) TERMINATED BY EOF
)

BLOB — binary files

Loading images, PDFs, or other binary content works the same way:

LOAD DATA
INFILE 'images.csv'
APPEND
INTO TABLE media.images
FIELDS TERMINATED BY ','
(
  image_id    INTEGER EXTERNAL,
  image_name  CHAR(200),
  mime_type   CHAR(50),
  file_path   FILLER CHAR(500),
  image_data  LOBFILE(file_path) TERMINATED BY EOF
)

Example images.csv:

101,logo.png,image/png,/data/images/logo.png
102,banner.jpg,image/jpeg,/data/images/banner.jpg

Multiple LOB columns

(
  doc_id        INTEGER EXTERNAL,
  html_file     FILLER CHAR(300),
  pdf_file      FILLER CHAR(300),
  html_content  LOBFILE(html_file) TERMINATED BY EOF,
  pdf_content   LOBFILE(pdf_file)  TERMINATED BY EOF
)

Each LOB column gets its own FILLER for the filename. sqlldr opens and reads each file per row.

BFILE — storing file pointers

BFILE columns store a directory alias + filename, not the data itself:

LOAD DATA
INFILE 'docs.csv'
APPEND
INTO TABLE media.documents
FIELDS TERMINATED BY ','
(
  doc_id    INTEGER EXTERNAL,
  doc_name  CHAR(200),
  dir_alias CHAR(50),
  fname     CHAR(200),
  doc_ref   BFILENAME(:dir_alias, :fname)   -- SQL expression builds the BFILE locator
)

The dir_alias must correspond to an Oracle DIRECTORY object created by the DBA.

Inline CLOB with enclosures

For CSV files that wrap CLOB data in quotes (may contain commas and newlines within the quoted value):

LOAD DATA
INFILE 'feedback.csv' "STR X'1E'"     -- use ASCII record separator, not newline
APPEND
INTO TABLE crm.feedback
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
  feedback_id  INTEGER EXTERNAL,
  customer_id  INTEGER EXTERNAL,
  rating       INTEGER EXTERNAL,
  comments     CHAR(32767) OPTIONALLY ENCLOSED BY '"'
)

Best practices

• Use LOBFILE for CLOBs/BLOBs larger than 64 KB — trying to inline them in a delimited file is impractical
• Always use TERMINATED BY EOF with LOBFILE unless you need to embed multiple LOBs per file with a custom delimiter
• Set NULLIF lob_fname = BLANKS on the LOBFILE column to handle rows where the LOB is optional
• LOB loading is inherently slower than scalar loads — batch by file size if performance matters
• BFILE requires Oracle DIRECTORY objects — confirm they exist and the load account has READ privilege before the run
• For large BLOB loads, consider DIRECT=TRUE and PARALLEL=TRUE to saturate I/O bandwidth