Oracle SQLLDR Streaming Data Load

Oracle SQLLDR Streaming Data Option

This is an example of a data file with each line extending to next line and the line ending with a pipe (|). The SQLLDR control file will use the streaming option value designated by

INFILE "STR stream_value"

in the control file. The stream value is computed using the Oracle function UTL_RAW.cast_to_raw() as shown below. This load is tested using Windows ( OS ) based text file.

SELECT UTL_RAW.cast_to_raw('|'||chr(13)||chr(10)) stream_value FROM DUAL;

Control file - mfgwip.ctl

LOAD DATA
INFILE mfgwip.txt "STR X'7C0D0A'"  -- UTL_RAW.cast_to_raw('|'||chr(13)||chr(10))
INTO TABLE mfg_wip_status_lut
REPLACE
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(assembly,
 project_id,
 project_number,
 mfg_entity_id       "mfg_entity_id_seq.NEXTVAL",
 operation_seq_num,
 date_released       "TO_DATE(:date_released,'mm/dd/yyyy')",
 mfg_entity_desc
)

Data file - mfgwip.txt

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|

Load Script file - mfgwip.bat

c:\oracle11\bin\sqlldr user_id@schema_name CONTROL=mfgwip.ctl DATA=mfgwip.txt LOG=mfgwip.log BAD=mfgwip.bad

SQLLDR - Load Details

SQLLDR Load Log -> Details

Oracle registered trademark of Oracle Corporation.

Last Revised On February 23, 2012

72921