Record Formats (stream, fixed, variable)

sqlldr understands three ways of framing records inside a data file. The default — stream — handles 99% of CSV / TSV inputs; the other two exist for fixed-byte and length-prefixed files commonly produced by mainframes.

Stream (default)

Each record ends with a delimiter character (typically LF on Unix, CRLF on Windows). sqlldr auto-detects line endings on most platforms.

LOAD DATA
INFILE 'employees.csv'                  -- stream, default LF/CRLF terminator
INTO TABLE hr.employees
FIELDS TERMINATED BY ','
( id, name, hire_date DATE "YYYY-MM-DD" )

To force a specific record terminator:

INFILE 'pipes.dat'   "STR '|*|'"        -- record ends at literal "|*|"
INFILE 'unix.dat'    "STR x'0a'"        -- LF
INFILE 'win.dat'     "STR x'0d0a'"      -- CRLF
INFILE 'eot.dat'     "STR x'04'"        -- ASCII EOT (0x04)

Fixed length

Every record is exactly n bytes — no terminator, no length prefix:

LOAD DATA
INFILE 'fixed_employees.dat'  "FIX 80"
INTO TABLE hr.employees
( employee_id POSITION(1:5)   INTEGER EXTERNAL,
  first_name  POSITION(6:25)  CHAR,
  last_name   POSITION(26:50) CHAR,
  hire_date   POSITION(51:60) DATE "YYYY-MM-DD",
  salary      POSITION(61:70) DECIMAL EXTERNAL,
  filler      POSITION(71:80) FILLER )

Two records would be exactly 160 bytes, contiguously. Any byte count over 80 is an error.

Variable length

The first 5 bytes of each record (default; configurable) hold the record length in ASCII digits:

LOAD DATA
INFILE 'varlen.dat'  "VAR 5"
INTO TABLE hr.events
( event_id   INTEGER EXTERNAL TERMINATED BY ',',
  payload    CHAR TERMINATED BY ',',
  created_at DATE "YYYY-MM-DD" TERMINATED BY ',' )

A record 00043100,login,2024-01-15 has bytes 0,0,0,4,3 first → 43 bytes follow. The size of the length prefix is the integer after VAR (default 5).

CONTINUEIF — splitting one logical record across multiple lines

If your input has continuation lines (e.g. lines starting with a space mean "continue the previous record"):

LOAD DATA
INFILE 'continued.dat'
CONTINUEIF NEXT(1:1) = ' '
INTO TABLE hr.notes
( id, body CHAR(2000) )

Variants:

Form	Means
CONTINUEIF THIS(1:1) = '+'	Current line is a continuation if its 1st char is +
CONTINUEIF NEXT(1:1) = ' '	Current line continues into next line if next line's 1st char is space
CONTINUEIF LAST = '\\'	Current line continues if its last char is \

The continuation column itself is consumed (not loaded).

CONCATENATE

Glue together a fixed number of physical records into one logical record:

LOAD DATA
INFILE 'two_per_logical.dat'
CONCATENATE 2
INTO TABLE hr.combined
( a POSITION(1:40) CHAR,
  b POSITION(41:80) CHAR )

Every two physical records (40 bytes each) become one 80-byte logical record.

Best practices

• Stream is right for 99% of CSV inputs — only reach for FIX/VAR for mainframe extracts
• STR x'…' is unambiguous across OSes; prefer it over relying on default newline detection
• Don't use FIX for files that might have variable-byte UTF-8 chars in fixed positions — UTF-8 multibyte characters break alignment; use single-byte charsets or VAR
• Continuation logic (CONTINUEIF) is fragile — clean the file with a script first when you can
• Check the log's "logical record count" vs "physical record count" to confirm CONCAT/CONTINUE behaviour matched expectations