SQL Functions & SEQUENCE

sqlldr lets you transform field values at load time using Oracle SQL expressions and generate surrogate keys using SEQUENCE. This moves data-quality logic out of post-load SQL and into the load itself.

SQL expression syntax

Append a quoted Oracle expression after the field definition. The raw field value is referenced as :field_name:

column_name  datatype  "oracle_expression_using_:field_name"

The expression is evaluated as part of each INSERT statement (conventional path) or equivalent processing (direct path). You have access to any Oracle built-in function.

String transformations

FIELDS TERMINATED BY ','
(
  -- Case normalization
  first_name   CHAR(40)  "INITCAP(:first_name)",
  last_name    CHAR(40)  "UPPER(:last_name)",
  email        CHAR(100) "LOWER(TRIM(:email))",

  -- Trim whitespace
  product_code CHAR(20)  "TRIM(:product_code)",

  -- Pad with leading zeros
  zip_code     CHAR(10)  "LPAD(TRIM(:zip_code), 5, '0')",

  -- Substring
  iso_country  CHAR(2)   "SUBSTR(:country_code, 1, 2)",

  -- Replace
  phone        CHAR(20)  "REGEXP_REPLACE(:phone, '[^0-9]', '')"
)

Numeric transformations

(
  -- Unit conversion
  weight_kg    DECIMAL EXTERNAL  ":weight_lbs * 0.453592",

  -- Percentage to fraction
  tax_rate     DECIMAL EXTERNAL  ":tax_pct / 100",

  -- Absolute value (handle negative quantities)
  quantity     INTEGER EXTERNAL  "ABS(:quantity)",

  -- Round to 2 decimal places
  price        DECIMAL EXTERNAL  "ROUND(:price, 2)"
)

Date and time transformations

(
  -- Add days
  due_date    CHAR(10)  "TO_DATE(:order_date,'YYYY-MM-DD') + 30",

  -- Truncate to midnight
  trunc_date  CHAR(19)  "TRUNC(TO_DATE(:event_ts,'YYYY-MM-DD HH24:MI:SS'),'DD')",

  -- Unix epoch to DATE
  created_at  CHAR(15)  "DATE '1970-01-01' + :epoch_seconds/86400",

  -- Extract year
  load_year   CHAR(4)   "TO_CHAR(SYSDATE,'YYYY')"
)

CASE expressions

(
  status_code   CHAR(2),
  status_label  CHAR(20)  "CASE :status_code
                              WHEN 'A' THEN 'Active'
                              WHEN 'I' THEN 'Inactive'
                              WHEN 'P' THEN 'Pending'
                              ELSE 'Unknown'
                            END",
  priority      CHAR(1),
  priority_num  INTEGER EXTERNAL
                           "CASE WHEN :priority = 'H' THEN 1
                                 WHEN :priority = 'M' THEN 2
                                 WHEN :priority = 'L' THEN 3
                                 ELSE 9 END"
)

Concatenation

(
  first_name  CHAR(20),
  last_name   CHAR(25),
  full_name   CHAR(50)  ":first_name || ' ' || :last_name",

  area_code   CHAR(3),
  number      CHAR(7),
  full_phone  CHAR(12)  "'(' || :area_code || ') ' || :number"
)

Note: full_name and full_phone do not read a field — they are synthesised from other fields. There must be no corresponding field in the data file at that column position, or full_name must use a CONSTANT pattern instead.

SEQUENCE — generating surrogate keys

SEQUENCE generates an incrementing integer — useful for surrogate keys or row numbers when the source doesn't have a unique identifier.

(
  surrogate_id  SEQUENCE(MAX, 1),      -- MAX(col)+1, then +1 per row
  order_id      INTEGER EXTERNAL,
  amount        DECIMAL EXTERNAL
)

SEQUENCE forms

Form	Behaviour
SEQUENCE(1, 1)	Start at 1, increment by 1
SEQUENCE(1000, 10)	Start at 1000, increment by 10
SEQUENCE(MAX, 1)	Query MAX(col) at load start, then increment — safe for incremental loads
SEQUENCE(COUNT, 1)	Start at current row count + 1

-- Assign row numbers within the load
(
  load_seq    SEQUENCE(1, 1),          -- 1, 2, 3, 4 …
  order_id    INTEGER EXTERNAL,
  amount      DECIMAL EXTERNAL
)

Using a database sequence

To use an Oracle SEQUENCE object:

(
  id      "my_schema.my_seq.NEXTVAL",    -- call Oracle SEQUENCE
  name    CHAR(100),
  amount  DECIMAL EXTERNAL
)

The NEXTVAL call is inside a SQL expression, so it works with both conventional and direct path. Each row gets a unique sequence value.

CONSTANT — same value for all rows

(
  order_id    INTEGER EXTERNAL,
  amount      DECIMAL EXTERNAL,
  load_ts     CONSTANT "SYSDATE",               -- same timestamp for all rows
  source_sys  CONSTANT "'ORDERS_FEED'",          -- string literal (extra quotes required)
  batch_id    CONSTANT "42"                      -- numeric constant
)

String literals inside SQL expressions need an extra layer of single quotes because the outer quotes delimit the expression and the inner quotes delimit the string.

Combining features

-- Full transformation pipeline for a customer import
(
  raw_id       FILLER CHAR(20),
  customer_id  "my_schema.customer_seq.NEXTVAL",
  first_name   CHAR(40)  "INITCAP(TRIM(:first_name))",
  last_name    CHAR(40)  "UPPER(TRIM(:last_name))",
  email        CHAR(200) "LOWER(TRIM(:email))"
               NULLIF email = BLANKS,
  phone        CHAR(20)  "REGEXP_REPLACE(TRIM(:phone),'[^0-9+]','')"
               NULLIF phone = BLANKS,
  country_code CHAR(2)   "UPPER(SUBSTR(TRIM(:country_code),1,2))"
               NULLIF country_code = BLANKS,
  created_at   CONSTANT "SYSDATE",
  modified_at  CONSTANT "SYSDATE",
  load_batch   CONSTANT "'CUSTOMER_IMPORT_V2'"
)

Best practices

• Keep SQL expressions simple — anything more than a function call or two belongs in a post-load UPDATE or a view
• Test expressions with sqlldr ... ROWS=5 before a full run; a syntax error in an expression rejects every row
• SEQUENCE(MAX, 1) is safe for incremental loads but locks the table to read the MAX value; use a database sequence for high-concurrency scenarios
• CONSTANT "SYSDATE" captures the timestamp once per load, not once per row — if you need per-row timestamps from the source file, map the timestamp column instead
• Extra quoting rule: SQL string literals inside an expression need "'literal'" (double quotes wrap the expression, single quotes wrap the string)