Architecture & Files

A SQL*Loader run touches six file types. Knowing what goes where saves hours when something goes wrong.

The file family

File	Default ext	Direction	What it holds
Control file	.ctl	input	Load specification — tells sqlldr what to do
Data file	.dat	input	The actual rows to load
Parameter file	.par	input	CLI flags pulled out of the command line
Log file	.log	output	Statistics, warnings, dictionary of what loaded
Bad file	.bad	output	Rows rejected by the database (data/constraint errors)
Discard file	.dis	output	Rows filtered by WHEN clause (must be enabled)

Bad and log files are always produced; the discard file is created only if you ask for one.

Flow

   employees.dat ──┐
                   │   sqlldr reads control + data
   employees.ctl ──┼──►  ┌─────────────┐
                   │     │  SQL*Loader │
   employees.par ──┘     └──┬──────────┘
                            │ accepted ── INSERT ──► hr.employees
                            │ rejected ───────────► employees.bad
                            │ filtered ───────────► employees.dis
                            └─ progress ──────────► employees.log

Default file naming

If you only specify a control file, sqlldr derives the rest:

sqlldr userid=hr/hr control=employees.ctl
# Reads:   employees.dat   (from INFILE inside the control file)
# Writes:  employees.log
# Writes:  employees.bad   (only if any rows are rejected)
# No discard file unless DISCARDFILE clause was used

Override any of them on the command line:

sqlldr userid=hr/hr \
       control=employees.ctl \
       data=jan_payroll.csv \
       log=/var/log/load/jan.log \
       bad=/tmp/jan.bad \
       discard=/tmp/jan.dis

Bad file format

The bad file is a verbatim copy of the rejected input rows in the original format — same delimiters, same line endings. So the workflow is:

1. Run the load
2. Open the log to see why rows were rejected
3. Fix the data
4. Feed the bad file back as the new data file: sqlldr … data=employees.bad bad=employees.bad2

Parameter file

Long sqlldr commands are easier to maintain in a .par:

# load.par
userid=hr/hr@//db.example.com:1521/orclpdb
control=employees.ctl
log=employees.log
bad=employees.bad
errors=50
direct=true

Then:

sqlldr parfile=load.par

CLI flags override values in the parameter file.

Best practices

• Pin file paths absolutely in the control file once you go to production — relative paths are evaluated against sqlldr's CWD
• Keep .bad and .dis out of source control; they're transient artefacts
• Rotate the log file per run (timestamped name) — overwrite is silent
• Use a parameter file when more than 4 CLI flags repeat across runs
• Direct path doesn't write to the bad file for some errors — they'll only surface in the log