Field Specifications & Datatypes

Every column listed inside the parentheses of an INTO TABLE block needs a datatype (how to interpret the bytes) and either a position (fixed-width) or a terminator (delimited). Get these right and the load works; get them wrong and you'll see ORA-01400 / ORA-12899 in the bad file.

Common datatypes

Datatype	Example	What it means
`CHAR(n)`	`name CHAR(40)`	Text, up to n bytes; default 255
`VARCHAR2(n)`	`comments VARCHAR2(4000)`	Same as CHAR for delimited input
`INTEGER EXTERNAL`	`id INTEGER EXTERNAL`	Digits in the file (`"1234"`), parsed as integer
`DECIMAL EXTERNAL`	`amount DECIMAL EXTERNAL`	Digits+`.` in the file (`"123.45"`)
`FLOAT EXTERNAL`	`pi FLOAT EXTERNAL`	Floating-point text (`"3.14e0"`)
`INTEGER`	`id INTEGER`	Binary integer (4 bytes by default)
`DECIMAL`	`amt DECIMAL(10,2)`	Packed decimal (binary, COBOL-style)
`ZONED(n,s)`	`n ZONED(7,2)`	Zoned decimal (mainframe BCD)
`DATE "fmt"`	`dt DATE "YYYY-MM-DD"`	Date with explicit format mask
`TIMESTAMP "fmt"`	`ts TIMESTAMP "YYYY-MM-DD HH24:MI:SS.FF6"`	Timestamp with mask
`RAW(n)`	`bin RAW(16)`	n bytes binary, hex-decoded
`LOBFILE(...)`	`c LOBFILE(...) TERMINATED BY EOF`	Each row's LOB is a separate file

Rule of thumb: anything followed by EXTERNAL is text in the data file. Without EXTERNAL it's a binary representation.

Field positions (fixed-width)

POSITION(start:end) uses 1-based, inclusive byte ranges:

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 )

Relative form — POSITION(*+n) — picks up after the previous field:

( first_name  POSITION(1:20)   CHAR,
  last_name   POSITION(*+1:45) CHAR,    -- starts 1 byte after first_name ends
  hire_date   POSITION(*+1)    DATE "YYYY-MM-DD" )

Delimited fields

Specify the terminator and (optional) enclosing character once at the INTO TABLE level:

INTO TABLE hr.employees
FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY '"'
( employee_id, first_name, last_name, hire_date DATE "YYYY-MM-DD", salary )

Different terminator per field:

( name        TERMINATED BY '|',
  description TERMINATED BY x'09' OPTIONALLY ENCLOSED BY '"' )

Useful constants: WHITESPACE (any space/tab), EOF (end of stream).

Field length hints

For CHAR, the length in (n) is the maximum to read. With delimited data it's usually optional; with fixed-width it's redundant if you've used POSITION. Specify it when the column is wider than the default 255.

( comments CHAR(4000) )

Sign and decimal handling

( amount  DECIMAL EXTERNAL,                                -- "123.45"
  balance INTEGER EXTERNAL "RR(",                          -- trailing-sign style
  price   DECIMAL(10,2) "to_number(:price,'9999.99')" )    -- SQL post-process

Best practices

Always INTEGER EXTERNAL / DECIMAL EXTERNAL for text input — plain INTEGER means binary and is almost never what you want
Match CHAR(n) to the largest expected source length, not to the column width
Pin date and timestamp masks per-column; don't rely on NLS_DATE_FORMAT
Use OPTIONALLY ENCLOSED BY '"' for any CSV that might quote strings — costs nothing if no quotes
For fixed-width files, count bytes with care; one-byte miscount invalidates every subsequent column