Oracle SQL*Loader Cheat Sheet

Every SQL*Loader pattern you reach for daily, on one page: control file structure, command-line options, INTO TABLE load modes, field specifications, WHEN-clause filtering, and direct-path vs conventional-path loading. Bookmark it, or work through the underlying concepts in the free SQL*Loader tutorial.

Control file structure

The control file (.ctl) tells SQL*Loader where the data is, which table to load, and how to map input fields to columns.

LOAD DATA
INFILE 'employees.csv'
BADFILE 'employees.bad'
DISCARDFILE 'employees.dsc'
APPEND
INTO TABLE employees
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  employee_id,
  first_name,
  last_name,
  hire_date DATE 'YYYY-MM-DD',
  salary
)

Command-line options

Option	Purpose
userid=hr/pwd@orcl	connection credentials
control=employees.ctl	the control file to run
log=employees.log	override the default log file path
bad=employees.bad	override the default bad-file path
direct=true	use direct-path load instead of conventional path
errors=50	stop after this many rejected rows (default 50)
skip=1	skip N logical records before loading -- e.g. a header row
rows=1000	commit every N rows (conventional path only)
parallel=true	allow concurrent direct-path loads into the same table/partition

sqlldr userid=hr/pwd@orcl control=employees.ctl log=employees.log direct=true

INTO TABLE load modes

Mode	Behaviour
INSERT	the default -- table must be empty, or the load errors out
APPEND	adds new rows, keeping whatever is already in the table
REPLACE	deletes all existing rows (DELETE), then loads the new ones
TRUNCATE	truncates the table (faster than REPLACE, no undo generated), then loads

Field specifications

Clause	Meaning
TERMINATED BY ','	field delimiter
OPTIONALLY ENCLOSED BY '"'	quote character, only required when the field contains the delimiter
POSITION(1:5)	fixed-width field occupying byte positions 1 through 5
CHAR(10)	treat the field as fixed-length character data
INTEGER EXTERNAL	a number stored as readable text in the file
TRAILING NULLCOLS	treat missing trailing fields on a short row as NULL instead of erroring

Delimited vs fixed-width

-- Delimited (CSV-style)
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(employee_id, first_name, last_name)

-- Fixed-width (each field at an exact byte position)
(
  employee_id  POSITION(1:6)  INTEGER EXTERNAL,
  first_name   POSITION(7:26) CHAR,
  last_name    POSITION(27:46) CHAR
)

SQL functions on fields

Any field can be transformed with a SQL expression at load time, wrapped in double quotes.

(
  employee_id,
  email        "LOWER(:email)",
  full_name    "INITCAP(:first_name) || ' ' || INITCAP(:last_name)",
  load_date    "SYSDATE"
)

Conditional loading (WHEN)

WHEN filters which input records get loaded -- records that don't match are skipped, not rejected as bad.

INTO TABLE active_employees
WHEN (status = 'A')
FIELDS TERMINATED BY ','
(employee_id, first_name, status)

Direct path vs conventional path

	Conventional path (default)	Direct path (direct=true)
How it loads	generates normal INSERT statements through the SQL engine	writes formatted data blocks straight above the high-water mark
Speed	slower -- full SQL processing per row	much faster for large loads
Triggers & constraints	fire/enforced normally	most triggers don't fire; some constraints are deferred and checked after
Redo generation	normal	can be minimized with NOLOGGING/UNRECOVERABLE
Best for	small loads, or when triggers must fire	large bulk loads into a table with few or no active constraints/triggers

Log, bad & discard files

File	Contains
.log	a summary of the run -- rows read, loaded, rejected, timing
.bad	rows that failed to load (SQL error, constraint violation, format error)
.dsc	rows skipped because they didn't satisfy a WHEN clause

Practise what's on this page

Run any query on this page live against a real schema — free, no signup, results in under a second.

Open the SQL editor →

Frequently asked questions

Is this SQL*Loader cheat sheet free to use?

Yes. Every reference table here, the underlying tutorial, and the free SQL editor are completely free with no sign-up.

What's the difference between APPEND, REPLACE, and TRUNCATE?

APPEND adds rows to whatever is already in the table. REPLACE deletes all existing rows first (a logged DELETE), then loads. TRUNCATE empties the table with a TRUNCATE statement (faster, minimal undo), then loads -- but note TRUNCATE can't be rolled back.

When should I use direct path instead of conventional path?

Direct path is much faster for large bulk loads because it writes formatted blocks straight to the datafile instead of going through the full SQL INSERT engine. Use conventional path when you need every row to fire triggers, or when constraints must be actively enforced during the load rather than deferred.

Why did some rows end up in the .bad file?

The .bad file collects rows that failed to load — a data-type mismatch, a violated constraint, or a malformed delimiter/field. The .log file lists the specific error for each rejected row; the .dsc (discard) file is different — it's for rows skipped by a WHEN clause, not errors.

Where can I practise SQL*Loader?

The Oracle SQL*Loader tutorial covers every topic on this page in depth, and the free in-browser SQL editor lets you query the loaded results.