SQLMentor // learn sql*loader

Introduction to SQL*Loader

SQL*Loader (sqlldr) is Oracle's command-line bulk-load utility. It reads flat data files (CSV, fixed-width, custom-delimited, even files containing LOBs) and loads them into existing Oracle tables far faster than equivalent INSERT statements — millions of rows per minute on direct path.

Why use it?

Goal Tool
Load 1 row from an app INSERT
Load 10s of millions of rows from a flat file SQL*Loader (direct path)
Query a flat file as if it were a table External table (ORACLE_LOADER)
Move data between Oracle databases Data Pump (expdp / impdp)

SQL*Loader is read-once into a single Oracle DB, source can be any flat file. It's been part of Oracle since v6 and remains the canonical answer for high-volume file ingestion.

End-to-end example

A 5-line CSV file employees.dat:

100,Steven,King,SKING,2003-06-17,24000
101,Neena,Kochhar,NKOCHHAR,2005-09-21,17000
102,Lex,De Haan,LDEHAAN,2001-01-13,17000
103,Alexander,Hunold,AHUNOLD,2006-01-03,9000
104,Bruce,Ernst,BERNST,2007-05-21,6000

A control file employees.ctl:

LOAD DATA
INFILE 'employees.dat'
INTO TABLE hr.employees
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  employee_id  INTEGER EXTERNAL,
  first_name   CHAR(20),
  last_name    CHAR(25),
  email        CHAR(25),
  hire_date    DATE "YYYY-MM-DD",
  salary       DECIMAL EXTERNAL
)

Run it:

sqlldr userid=hr/hr@//localhost:1521/orclpdb \
       control=employees.ctl \
       log=employees.log \
       bad=employees.bad

Result: 5 rows inserted, log file written, no bad/discard files. That's the whole pattern — every more-advanced use case is just options layered on top.

Three things to know up front

  1. Conventional vs Direct path — direct path (DIRECT=TRUE) bypasses the SQL layer and is dramatically faster, but skips triggers and most constraint checking.
  2. Bad and discard files — rejected rows go to the bad file; rows filtered by WHEN clauses go to the discard file. Both are in the original input format, so you can fix and re-feed them.
  3. The control file is the contract — it describes the file, not the table. Match field counts, datatypes, and terminators to the actual bytes on disk and the load just works.

Best practices

  • Always specify BAD= and LOG= explicitly so you know where output lands
  • Keep one control file per logical load — don't mix unrelated tables
  • Use DIRECT=TRUE when you can; fall back to conventional only when you need triggers or referential checks
  • Stage data in a dedicated load schema, then INSERT … SELECT into the real tables for transactional safety
  • Always test with a small sample (LOAD=100) before running on the full file