Samples of control files for Oracle SQL Loader

This is intended as a quick reference to provide a set of templates for creating SQL Loader control files for most frequently used scenarios.

Sample 1:
Loading data from a TAB-delimited file: FIELDS TERMINATED BY X'09'
The sample also demonstrates the following techniques:

• skip lines: OPTIONS (SKIP=1)  instructs the SQL Loader to skip the first line (the number specifies the number of lines)
• auto-generate values for a primary key column: SEQUENCE(MAX, 1)
• load data by adding more rows without truncating the existing data: APPEND
• load into DATE type columns: "TO_DATE(:TX_DATE,'MM/DD/YYYY')"

-- ============================================================
-- stocktx.ctl

-- SQLLDR Control File for data loading to STOCKTX table.
--
-- The input file has the TAB-separated format with a single line per table row.
--
-- To run (sample):
--   sqlldr batchjob/<pwd>@oradb control=stocktx.ctl errors=1000 log=logs/stocktx_load.log

OPTIONS (SKIP=1) load data
  infile 'data/stocktx_data.tab'
  badfile 'logs/stocktx_data.bad'
  discardfile 'logs/stocktx_data.discard'
  APPEND
  into table portfolio.stocktx
  FIELDS TERMINATED BY X'09'
  trailing nullcols
(
    TX_ID        SEQUENCE(MAX, 1),
    EXCHANGE,
    SYMBOL,
    QTY,
    PRICE,

    TX_DATE      "TO_DATE(:TX_DATE,'MM/DD/YYYY')"

)

Sample 2:
Loading data from a multicharacter-delimited file (^|^) file: FIELDS TERMINATED BY '^|^'
The sample also demonstrates the following techniques:

• delete existing data prior to the load: TRUNCATE
• specify the data file name on the command line: data=addr.dsv
• specify log files pathnames via command line parameters (as opposite to hard-coding in the control file).
• evaluate the source values and transform on a condition, e.g if the Postal Code is "N/A", replace it with NULL: ZIP_CODE "decode(:ZIP_CODE, 'N/A', '', :ZIP_CODE)"

• apply the current time/date to a DATE column: SYSDATE 

• CONSTANT - allows to set a column to a constant value; this value is hard-coded into the control script file, not from the input data file. The value is treated as a character string; SQLLDR will attempt to convert it to the column type if required.

-- ============================================================
-- addr.ctl
-- SQLLDR Control File for data loading to ADDR table.
--
-- The input file the '^|^' sequence of characters to separate values.
--
-- To run (sample):
-- sqlldr batchjob/@oradb control=addr.ctl errors=100 log=logs/addr_load.log bad=logs/addr.bad discard=logs/addr.discard data=addr.dsv
--
-- Note: the logs directory needs to be created prior to running the script.

OPTIONS (SKIP=0)

load data
  TRUNCATE
  into table ADDR
  FIELDS TERMINATED BY '^|^'
  trailing nullcols
(
    ADDR_ID SEQUENCE(MAX, 1),
    CITY,
    STREET_NUM,
    STREET_NAME,
    STREET_DIR,
    ZIP_CODE    "decode(:ZIP_CODE, 'N/A', '', :ZIP_CODE)",
    MODIFIED_ON SYSDATE,
    BATCH_ID    CONSTANT 1
)

This is a table structure for the sample above:

CREATE TABLE ADDR (
  addr_id      NUMBER(10)     NOT NULL,
  city         VARCHAR2(40)   NOT NULL,
  street_num   NUMBER         NOT NULL,
  street_name  VARCHAR2(40)   NOT NULL,
  street_dir   VARCHAR2(4)    NOT NULL,
  zip_code     VARCHAR2(7),
  modified_on  DATE           NOT NULL,
  batch_id     NUMBER,
--  
  CONSTRAINT addr_id_pk PRIMARY KEY (addr_id)
)
/

Sample 3:
SQLLDR control file to load data from a comma-separated file (,) where field values may be enclosed in double-quotes (").


Control file explanation:

• FIELDS TERMINATED BY ',' - specifies that the fields are delimited by comma.
• OPTIONALLY ENCLOSED BY '"' - specifies that field values may (or may not) be enclosed in double quotes, for example: "2269 Lake Shore Blvd. W., Toronto, ON"
  This is often required if the data can contain characters that are used for field separation, the comma character in this particular example.
• SEQUENCE(COUNT, 1) - this is usually used to generate unique values for the table primary key. The COUNT parameter specifies that the 1st value to be the number of records in the table before the load plus 1.
• --  INTO TABLE ADDR.STREET_VALUES_BAK - a commented out line; it is handy during the development phase when the control file can be re-used to load data into different tables.
• "UPPER(:ALT_STREET_NAME)" - convert the values to the upper case.
• CREATE_DATE "TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD'), 'YYYYMMDD')" - this is useful to load only the date part of the current time into DATE type columns.
  SYSDATE returns the current time which is formatted into a string containing Year, Month, and Day which in turn converted back to DATE type.
• BATCH_ID EXPRESSION "(SELECT MAX(BATCH_ID) FROM ADDR.BATCH_ID WHERE TABLE_NAME = 'STREET_ALTERNATIVE')" - useful to insert a constant value for each data load, for a example a Batch ID or Run ID.
  A separate table is required, of course, to maintain load data IDs. A new record can be inserted into this table from a shell script prior to invoking the SQLLDR script. See a DDL and INSERT statement below.

-- =============================================================
-- street_alternative.ctl
-- Loading alternative street names.
-- SQLLDR Control File for data loading into ADDR.STREETS_ALTERNATIVE table.
--
-- The input file is |-separated in double quotes.
-- sqlldr vit1@devdb1 control=street_alternative.ctl errors=1 log=logs/street_alternative.log bad=logs/street_alternative.bad discard=logs/street_alternative.discard data=street_alternative.txt
--

OPTIONS (SKIP=1)

LOAD DATA
  APPEND
  INTO TABLE ADDR.STREET_ALTERNATIVE
--  INTO TABLE ADDR.STREET_VALUES_BAK
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  TRAILING NULLCOLS (
  REC_ID            SEQUENCE(COUNT, 1),
  STREET_ID,
  ALT_STREET_NAME   "UPPER(:ALT_STREET_NAME)",
  CREATE_DATE       "TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD'), 'YYYYMMDD')",
  BATCH_ID          EXPRESSION "(SELECT MAX(BATCH_ID) FROM ADDR.BATCH_ID WHERE TABLE_NAME = 'STREET_ALTERNATIVE')"
)

DDL for the tables used in the sample #3 above:

CREATE TABLE ADDR.STREET_ALTERNATIVE (
  rec_id          NUMBER       NOT NULL,
  street_id       NUMBER       NOT NULL,
  alt_street_name VARCHAR2(40) NOT NULL,
  create_date     DATE         NOT NULL,
  batch_id        NUMBER       NOT NULL,
--  
  CONSTRAINT rec_id_pk PRIMARY KEY (rec_id)
)
/

CREATE TABLE ADDR.BATCH_ID (
  rec_id          NUMBER        NOT NULL,
  table_name      VARCHAR2(255) NOT NULL,
  batch_id        NUMBER        NOT NULL,
  create_date     DATE          NOT NULL,
 --  
  CONSTRAINT batch_id_pk PRIMARY KEY (rec_id)
)
/

-- A statement to insert a record into the BATCH_ID table for
-- the next value to be used in a data load:
insert into ADDR.BATCH_ID values (1, 'STREET_ALTERNATIVE', 
  (select nvl(max(batch_id), 0) from ADDR.BATCH_ID
    where table_name = 'STREET_ALTERNATIVE') + 1, sysdate)
/