Control File Structure

The control file is a plain-text recipe that sqlldr reads top-to-bottom. It has a fixed skeleton with optional clauses you slot in as needed.

Skeleton

-- Comments use the SQL double-dash
OPTIONS ( ERRORS=50, ROWS=5000, DIRECT=TRUE )   -- optional, mirrors CLI flags

LOAD DATA                                       -- mandatory
CHARACTERSET AL32UTF8                           -- optional, file encoding
INFILE  'employees.dat'                         -- one or more INFILE clauses
BADFILE 'employees.bad'                         -- optional, override defaults
DISCARDFILE 'employees.dis'                     -- optional
DISCARDMAX 100                                  -- optional, cap on discards

APPEND                                          -- INSERT/APPEND/REPLACE/TRUNCATE

PRESERVE BLANKS                                 -- optional, keep leading/trailing spaces

INTO TABLE hr.employees                         -- one or more INTO TABLE blocks
WHEN dept_code <> 'XX'                          -- optional row filter
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  employee_id  INTEGER EXTERNAL,
  first_name   CHAR(20),
  last_name    CHAR(25),
  hire_date    DATE "YYYY-MM-DD",
  salary       DECIMAL EXTERNAL
)

Clause order

sqlldr is strict about ordering:

1. OPTIONS(...) — at the very top if used
2. UNRECOVERABLE / RECOVERABLE — direct path only, before LOAD DATA
3. LOAD DATA / CONTINUE_LOAD DATA — mandatory
4. CHARACTERSET — file encoding
5. INFILE — one or more, can repeat
6. BADFILE, DISCARDFILE, DISCARDMAX
7. Load mode keyword (INSERT, APPEND, REPLACE, TRUNCATE)
8. PRESERVE BLANKS
9. INTO TABLE … blocks (one or more)

LOAD DATA vs CONTINUE_LOAD DATA

CONTINUE_LOAD DATA is used when restarting a direct-path load that aborted partway. It tells sqlldr to pick up from where the previous attempt left off using internal state recorded in the table.

CONTINUE_LOAD DATA
INFILE 'big_orders.dat'
INTO TABLE warehouse.orders
APPEND
( ... )

For conventional path, just use SKIP=N on the command line instead.

OPTIONS clause

Anything you can pass on the command line can also live in OPTIONS(). CLI args override OPTIONS:

OPTIONS (
  ERRORS    = 200,
  SKIP      = 1,
  ROWS      = 10000,
  BINDSIZE  = 8388608,
  READSIZE  = 8388608,
  DIRECT    = TRUE
)
LOAD DATA
INFILE 'monthly.csv'
...

Comments

-- like SQL for line comments. There are no block comments — sqlldr doesn't recognise /* */.

CHARACTERSET

Tells sqlldr how the input file is encoded. The session's NLS_LANG controls how characters are converted into the database character set:

LOAD DATA
CHARACTERSET AL32UTF8        -- file is UTF-8
INFILE 'orders_utf8.csv'
...

Common values: AL32UTF8, WE8MSWIN1252, JA16SJIS, ZHS16GBK. Don't set this on a binary fixed-width file — leave it default.

Best practices

• Put all overridable knobs in OPTIONS(...) at the top so the file documents itself
• Use BADFILE and DISCARDFILE clauses inside the control file rather than the CLI; keeps the run reproducible
• Stick TRAILING NULLCOLS in unless you genuinely require every column to be present
• CHARACTERSET is a must for non-ASCII input — silent corruption otherwise
• One control file per logical load; resist the urge to multiplex unrelated tables