NULLIF, DEFAULTIF, and FILLER

These three directives give you fine-grained control over how individual field values are interpreted before they are inserted into the database.

FILLER — consume without loading

FILLER reads the field from the data file but does not load it into any column. Use it to:

Skip fields you don't need
Reference a field in LOBFILE(), WHEN, or a SQL expression without creating a target column

-- Skip columns 3 and 5 from a CSV
FIELDS TERMINATED BY ','
(
  employee_id,
  first_name,
  middle_name   FILLER,          -- column 3 discarded
  last_name,
  maiden_name   FILLER,          -- column 5 discarded
  hire_date     DATE "YYYY-MM-DD"
)

FILLER fields must still be consumed (they advance the field pointer in delimited mode, or occupy their byte range in fixed-width mode).

FILLER for LOBFILE filenames

(
  doc_id    INTEGER EXTERNAL,
  file_path FILLER CHAR(400),         -- read the filename but don't store it
  content   LOBFILE(file_path) TERMINATED BY EOF
)

The file_path is consumed and used by LOBFILE(), but nothing is inserted into a file_path column.

NULLIF — convert a specific value to NULL

NULLIF col = literal sets the column to NULL when the raw field value equals the literal.

Common patterns

-- Empty string → NULL (also done automatically for empty delimited fields)
manager_id   INTEGER EXTERNAL  NULLIF manager_id = BLANKS

-- Placeholder zero → NULL
discount_pct DECIMAL EXTERNAL  NULLIF discount_pct = '0.00'

-- Legacy "not applicable" sentinel → NULL
status_code  CHAR(3)           NULLIF status_code = 'N/A'

-- MySQL-style null sentinel
shipped_date DATE "YYYY-MM-DD" NULLIF shipped_date = '0000-00-00'

-- All-spaces string → NULL
country_code CHAR(3)           NULLIF country_code = BLANKS

BLANKS is a special keyword meaning "the field contains only spaces" — it doesn't require a string literal.

NULLIF with POSITION

-- Fixed-width: null if bytes 10–12 are spaces
dept_id  POSITION(10:12) INTEGER EXTERNAL  NULLIF dept_id = BLANKS

NULLIF with compound conditions

-- NULL if either of two sentinels
bonus  DECIMAL EXTERNAL  NULLIF bonus = '0' NULLIF bonus = BLANKS

Multiple NULLIF clauses are OR'd: the column is NULL if any of them match.

DEFAULTIF — substitute a default value

DEFAULTIF col = condition sets the column to a default when the condition is true. Paired with an SQL_STRING (a SQL expression) to specify the default value.

Note: DEFAULTIF alone does not insert a value — it acts as a gate. The actual default comes from a SQL expression on the column.

-- If the field is blank, use the string 'UNKNOWN'
country_code  CHAR(3)  DEFAULTIF country_code = BLANKS  ":country_code := 'UNKNOWN'"

This is less common than NULLIF; in practice, most teams either use NULLIF or rely on the column's DEFAULT clause in DDL.

SQL expressions on fields

The most flexible way to transform a value during load is a SQL expression appended after the column spec:

(
  employee_id   INTEGER EXTERNAL,
  first_name    CHAR(20)  "INITCAP(:first_name)",        -- capitalise each word
  last_name     CHAR(25)  "UPPER(:last_name)",            -- force uppercase
  email         CHAR(100) "LOWER(:email)",                -- force lowercase
  salary        DECIMAL EXTERNAL ":salary * 1.1",         -- 10% raise on load
  hire_date     CHAR(10)  "TO_DATE(:hire_date,'YYYY-MM-DD')",
  dept_code     CHAR(4)   "CASE WHEN :dept_code = 'IT' THEN '0010'
                                 WHEN :dept_code = 'HR' THEN '0020'
                                 ELSE :dept_code END"
)

The :field_name notation refers to the raw string value read from the file.

Combining NULLIF and SQL expressions

commission_pct  DECIMAL EXTERNAL
  NULLIF commission_pct = BLANKS
  ":commission_pct / 100"           -- convert percentage to decimal fraction

sqlldr evaluates NULLIF first — if the field is blank, the SQL expression is skipped and the column is set to NULL. If the field has a value, the expression is applied.

CONSTANT — supply a fixed value

(
  order_id    INTEGER EXTERNAL,
  load_date   CONSTANT "SYSDATE",         -- same value for every row
  source_file CONSTANT "'orders_jan.csv'" -- literal string, same for all rows
)

CONSTANT ignores the data file entirely — the value comes from the expression, not from a field position.

SEQUENCE — generate row numbers

(
  surrogate_key SEQUENCE(MAX, 1),   -- starts at MAX(surrogate_key)+1, increments by 1
  order_id      INTEGER EXTERNAL,
  amount        DECIMAL EXTERNAL
)

Form	Meaning
`SEQUENCE(n, inc)`	Start at `n`, increment by `inc`
`SEQUENCE(MAX, inc)`	Start at current MAX+1, increment by `inc`
`SEQUENCE(COUNT, inc)`	Start at row count+1

Full example

-- products_load.ctl
OPTIONS ( ERRORS=100, ROWS=5000 )
LOAD DATA
INFILE 'products.csv'
APPEND
INTO TABLE catalog.products
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  product_id        INTEGER EXTERNAL,
  name              CHAR(200)  "TRIM(:name)",
  sku               CHAR(50)   "UPPER(TRIM(:sku))",
  category_raw      FILLER CHAR(30),
  category_code     CONSTANT "'GENERAL'",     -- default until categories are loaded
  list_price        DECIMAL EXTERNAL          NULLIF list_price = BLANKS,
  cost_price        DECIMAL EXTERNAL          NULLIF cost_price = '0'
                              ":cost_price * 1.0",
  is_active         CHAR(1)   NULLIF is_active = BLANKS
                              "CASE WHEN :is_active = 'Y' THEN '1' ELSE '0' END",
  created_at        CONSTANT "SYSDATE",
  surrogate_id      SEQUENCE(MAX, 1)
)

Best practices

Use NULLIF col = BLANKS as the default for all nullable columns — it's free and saves bad-file noise
Don't chain NULLIF more than 2–3 times; if you need complex null logic, write a SQL expression
FILLER is preferable to simply leaving a positional gap — explicit is clearer and prevents mis-alignment in fixed-width files
CONSTANT is ideal for audit columns like load_date, source_system, or batch_id
Test SQL expressions with sqlldr ... ROWS=10 before a full load — a typo in an expression rejects every row