Bad File, Discard File & Log File

Every sqlldr run produces up to three output files: a log (always), a bad file (when rows are rejected due to format or constraint errors), and a discard file (when rows are filtered out by WHEN conditions). Understanding these three files is essential for diagnosing load problems.

The three output files

File	Extension	Contains	Controlled by
Log	.log	Execution summary, errors, statistics	LOG= or -log
Bad	.bad	Rows rejected for data/format errors	BADFILE= or BAD=
Discard	.dsc	Rows skipped by WHEN conditions	DISCARDFILE= or DISCARD=

Log file

Always produced. Contains:

• sqlldr version and invocation command
• Control file text
• For each INTO TABLE block: rows loaded, rejected, discarded
• Rejected row details with Oracle error codes
• Total elapsed time and rate (rows/sec)

sqlldr userid=hr/hr control=orders.ctl log=/data/logs/orders_load.log

Reading the log summary

Table SALES.ORDERS:
  50000 Rows successfully loaded.
  3 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were false.
  0 Rows not loaded because all fields were null.

Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:         50003
Total logical records rejected:         3
Total logical records discarded:        0

Run began on Tue Jan 16 09:45:10 2024
Run ended on Tue Jan 16 09:45:43 2024
Elapsed time was:     00:00:33.21
CPU time was:         00:00:08.51

The log also lists each rejected row with its error:

Record 1032: Rejected - Error on table SALES.ORDERS, column ORDER_DATE.
ORA-01843: not a valid month

Bad file

Contains the exact raw bytes of each rejected row, in the original data file format. You can fix the data and reload just the bad file.

-- In the control file
BADFILE '/data/logs/orders.bad'

# Or on the command line
sqlldr userid=hr/hr control=orders.ctl bad=/data/logs/orders.bad

Reloading from the bad file

# Fix orders.bad, then reload it
sqlldr userid=hr/hr control=orders.ctl data=/data/logs/orders.bad \
       bad=/data/logs/orders_retry.bad \
       log=/data/logs/orders_retry.log

If the bad file is empty at the end of a run, it is deleted automatically.

What causes rows to go to bad file

• Format errors: date mask mismatch, non-numeric data in a numeric field
• Constraint violations: NOT NULL, CHECK constraint (conventional path only)
• Unique key violations: duplicate primary key (conventional path only)
• String too long: field value exceeds column width (ORA-12899)
• Invalid character set conversion

Discard file

Contains rows that matched no WHEN condition in any INTO TABLE block. These are not data errors — the data was valid but the load rule excluded it.

-- In the control file
DISCARDFILE '/data/logs/orders.dsc'
DISCARDMAX 100                    -- abort if more than 100 rows are discarded

sqlldr userid=hr/hr control=orders.ctl discard=/data/logs/orders.dsc discardmax=50

If DISCARDMAX is exceeded, sqlldr stops the load with an error. Use it to catch accidental misconfiguration of WHEN conditions.

Controlling error tolerance

Parameter	Meaning	Default
ERRORS=n	Max bad rows before aborting	50
DISCARDMAX=n	Max discard rows before aborting	unlimited
ROWS=n	Commit interval (conventional)	64

sqlldr userid=hr/hr control=orders.ctl errors=1000 discardmax=0

DISCARDMAX=0 is strict: any discarded row aborts the load. Use during initial validation to catch WHEN-clause bugs.

Default file naming

If you don't specify bad/discard/log names, sqlldr derives them from the control file name:

Control file	Default log	Default bad	Default discard
orders.ctl	orders.log	orders.bad	orders.dsc

All three default to the same directory as the control file.

Diagnosing a failed load

# 1. Check the summary at the bottom of the log
tail -40 /data/logs/orders.log

# 2. Look at the first few bad rows
head -20 /data/logs/orders.bad

# 3. Match bad rows to their error in the log
grep "Record [0-9]*: Rejected" /data/logs/orders.log | head -20

# 4. Check discard count — unexpected discards mean WHEN condition bugs
grep "Rows not loaded because all WHEN clauses" /data/logs/orders.log

Full example with explicit file control

-- orders_load.ctl
OPTIONS ( ERRORS=500, DISCARDMAX=10, ROWS=5000 )
LOAD DATA
CHARACTERSET AL32UTF8
INFILE '/data/feeds/orders_20240116.csv'
BADFILE '/data/logs/orders_20240116.bad'
DISCARDFILE '/data/logs/orders_20240116.dsc'
APPEND
INTO TABLE sales.orders
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  order_id    INTEGER EXTERNAL,
  customer_id INTEGER EXTERNAL,
  order_date  DATE "YYYY-MM-DD",
  amount      DECIMAL EXTERNAL,
  status      CHAR(1)
)

sqlldr userid=sales_load/pw@prod \
       control=/data/ctl/orders_load.ctl \
       log=/data/logs/orders_20240116.log

Best practices

• Always specify explicit BADFILE and DISCARDFILE paths in the control file — avoid having them default next to the control file in production
• Archive the log, bad, and discard files for every run; they are your audit trail
• Set ERRORS=0 in production to make the load fail-fast on the first bad row, then investigate
• Set DISCARDMAX=0 during initial testing to catch WHEN clause bugs immediately
• After a partial load that was aborted, check whether a partial commit occurred and decide whether to roll back or reload with SKIP=n
• Automate log parsing in your pipeline: check "Rows not loaded due to data errors" ≠ 0 to trigger an alert