Oracle SQLLDR

This is an example of loading data using SQLLDR into Oracle database where the header line is skipped using OPTIONS (SKIP=1). This also shows the use of CONTINUEIF LAST != '"' to load multi-line data that starts with a " and extends into next line. If other line separators are used in the data file, the streaming option
INFILE "STR stream_value"
is recommended in the control file. The hexadecimal value (for Unix/Linux) has to be determined by the query
SELECT utl_raw.cast_to_raw( '"'||chr(10) ) stream_value FROM dual;

SQLLDR Control File (test.ctl) 
OPTIONS (SKIP=1)
LOAD DATA
INFILE testdata.txt
CONTINUEIF LAST != '"' 
INTO TABLE mfg_schedule_status
REPLACE
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(assembly,
 project_id,
 project_number,
 operation_seq,
 date_released        "TO_DATE(:date_released,'mm/dd/yyyy')",
 mfg_operation_desc   "TRIM(:mfg_operation_desc)",
 mfg_operation_id     "mfg_operation_seq.NEXTVAL"
)
Data File (testdata.txt) 
ASSEMBLY,PROJECT_ID,PROJECT NUMBER,OP_SEQ,"DATE RELEASED","MFG OPERATION DESCRIPTION"
10000,100,P100,45,"11/12/2010","In WIP (CNC) data to
be revised"
10001,100,P100,55,"09/10/2011","In WIP (Assembly) to
be deleted"
10002,100,P100,46,"03/02/2011","In WIP (PCB) to
be Updated"
10002,100,P100,49,"05/03/2011","In WIP FPGA assembly to
be revised"
Windows Script File (testload.bat) 
c:\oracle\bin\sqlldr user_name@instance CONTROL=test.ctl DATA=testdata.txt LOG=test.log BAD=test.bad

Note: On execution of the script (batch file), password entry will be prompted

Refer SQLLDR Output -> log file (test.log)

Oracle registered trademark of Oracle Corporation.
Last Revised on: February 23, 2012

  72953