Loading CSV Into Oracle With SQL Loader Control Files

SQL*Loader is the oldest of the bulk loaders in this series and still one of the most capable. It has been moving files into Oracle tables since long before CSV was a household word, and its style shows: you do not give it options on a command line so much as write a small program, a control file, that says where the data is and how to cut it into fields. The reward is precision. You decide the type and the date mask for each column, how to treat blanks and what to do with bad rows, all in a file that lives in version control. This article builds a control file for a quoted CSV, runs through the files SQL*Loader writes, covers the exit codes and sets out when to prefer an external table. It belongs with the other engine articles in loading CSV into cloud warehouses, and the export direction is in exporting an Oracle query to CSV with SQLcl.

The table, the file and the control file

A table with a date, a decimal and a text column that can be empty:

CREATE TABLE orders (
  order_id  NUMBER(10)    PRIMARY KEY,
  customer  VARCHAR2(100) NOT NULL,
  zip       VARCHAR2(5)   NOT NULL,
  placed_on DATE          NOT NULL,
  total     NUMBER(10,2)  NOT NULL,
  note      VARCHAR2(200)
);

order_id,customer,zip,placed_on,total,note
1001,Acme Ltd,02134,2026-10-01,120.50,
1002,"Beta, Corp",90210,2026-10-01,89.90,"said ""hi"""

and the control file that describes how to read it:

OPTIONS (SKIP=1, ERRORS=50)
LOAD DATA
CHARACTERSET UTF8
INFILE 'orders.csv'
BADFILE 'orders.bad'
DISCARDFILE 'orders.dsc'
APPEND
INTO TABLE orders
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  order_id,
  customer,
  zip,
  placed_on DATE "YYYY-MM-DD",
  total,
  note
)

Reading it from the top. OPTIONS (SKIP=1, ERRORS=50) skips one header line and allows 50 rejected rows before aborting (the default is 50). CHARACTERSET UTF8 says the file's encoding, and a mismatch with the database's character set is a classic source of garbled text, so name it (detecting the encoding of a CSV file helps when it is unknown). INFILE names the data, BADFILE and DISCARDFILE name where rejected and filtered-out records go. APPEND adds rows; INSERT requires an empty table, REPLACE deletes the existing rows first and TRUNCATE empties the table, so choose with care.

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' is the CSV dialect: comma-separated, and a field may be wrapped in double quotes, inside which a comma is data and a doubled quote is one quote. TRAILING NULLCOLS treats missing fields at the end of a line as NULL, which handles the empty note that some exporters write as nothing and others omit. The column list gives the order of fields in the file, and for the date, a mask: DATE "YYYY-MM-DD". Without the mask, Oracle uses the session's NLS_DATE_FORMAT and the same file loads on one server and fails on another, which is the problem in exporting an Oracle query to CSV with SQLcl viewed from the load side.

The columns are read as text and converted to the target types on insert, so zip loads as VARCHAR2 with its leading zero intact, which is the reason the target column is character data and not a number.

Running it

sqlldr userid=loader@//db.example.test:1521/ORCLPDB1 control=orders.ctl log=orders.log

The password is left off so sqlldr prompts for it; for a script, use a wallet or an environment-supplied credential and keep it out of process listings, where a userid=user/password argument is visible to other users of the machine. The run prints a summary and writes three files, and reading them is the whole skill.

The log (orders.log) records the control file as parsed, the table and column definitions, the counts of rows read, loaded, rejected and discarded, and the elapsed time. The line to look for is the count of rows successfully loaded, and then the section listing each rejected record with the Oracle error and the field.

The bad file (orders.bad) holds each rejected record in its original form, which is what you fix and reload. A row fails for a reason Oracle gives: ORA-01722: invalid number for a total like eighty, ORA-01843: not a valid month for a date such as 2026-13-02, ORA-12899: value too large for column for a customer name longer than 100 characters, ORA-01400: cannot insert NULL for an empty required field, and ORA-00001: unique constraint violated for a duplicate key.

The discard file (orders.dsc) holds records that were skipped by a WHEN clause in the control file, such as a footer line. If you do not use WHEN, it is empty.

Rejected rows count against ERRORS. When the limit is passed, the load stops, and in a conventional path load, the rows loaded so far are committed in batches according to ROWS, so a stopped load leaves a partial result unless you wrapped it in your own transaction control or load to a staging table. For all-or-nothing behavior, load into a staging table, check the counts, then insert into the real table in one statement (loading a CSV into a staging table, then merging).

Exit codes for scripts

sqlldr returns a code that tells a script how it went: 0 means all rows loaded, 2 means the load finished with a warning, usually because some rows were rejected or discarded, and 1 and 3 mean failure, either of the run or a fatal error such as being unable to open the data file. A wrapper should treat 0 as success, 2 as "loaded, look at the bad file" (and alert if the bad file is non-empty, or if the rejected count exceeds a threshold), and anything else as failure. Treat a 2 as its own state: the load finished, and some rows did not make it, which is neither a clean success nor a failure to run.

sqlldr userid=... control=orders.ctl log=orders.log
rc=$?
case $rc in
  0) echo "loaded cleanly" ;;
  2) echo "loaded with rejects: see orders.bad" >&2; [ -s orders.bad ] && exit 2 ;;
  *) echo "sqlldr failed with $rc" >&2; exit 1 ;;
esac

Conventional or direct path

By default, sqlldr uses a conventional path load: it builds INSERT statements and sends them in arrays, and the database applies constraints and fires triggers as usual. direct=true switches to the direct path, which formats data blocks itself and writes them above the table's high-water mark, bypassing much of the SQL layer. It is substantially faster on large loads, and it changes the rules: it locks the table against other writers, does not fire insert triggers, and handles some constraints (foreign keys and checks) by disabling and re-enabling them. A failed direct path load can leave indexes in an unusable state that you must rebuild. Test it on a copy of the table before using it on a live one, and read the documentation of the version you run, since details have changed.

For loads of millions of rows, direct path with parallel=true and several files is how people get speed. For a daily file of tens of thousands of rows, the conventional path is fast enough and safer.

When an external table or SQLcl is the better loader

An external table declares a file as a table, with the same field definition syntax as a control file, and lets you load with INSERT INTO orders SELECT ... FROM orders_ext. It brings the file into SQL, so you can filter, join, convert and reject with queries. It needs a directory object on the database server, so the file has to be where the server can read it, which is the same constraint as BULK INSERT on SQL Server (loading CSV into SQL Server with BULK INSERT). Use it when the load needs logic and the file is on the server.

**SQLcl's LOAD command** reads a CSV from the client machine and inserts it, and can create the table when asked, which makes it a quick path for a one-off from a laptop, with less control than a control file. And for repeated, audited feeds, put the control file in the repository, call sqlldr from a script with the ledger and the exit-code handling above and use the patterns in automating CSV imports into a database. Oracle documents every control file clause in the SQL*Loader chapter of the Utilities guide, and the external table alternative in its external tables concepts chapter.