Using SQL Loader with delimited files
Hi
I have created a SQL Loader control file as follows:
OPTIONS (ROWS = 1,ERRORS = 5000)
LOAD DATA
-- Header record
INTO TABLE nfc_int_headers
APPEND
WHEN header = 'HDR'
FIELDS TERMINATED BY "," TRAILING NULLCOLS
( header FILLER
, interface_data_type
, source_system_code
, interface_run_id "TO_NUMBER(:interface_run_id * -1)"
, previous_interface_complete "TO_DATE(REPLACE(:previous_interface_complete,' ','0'),'YYYYMMDDHH24MISS')"
, expiry_date "TO_DATE(:expiry_date, 'YYYYMMDD')"
, site_id
, timestamp SYSDATE
, file_id "nfc_int_data_files_seq.nextval"
)
-- Detail record
INTO TABLE nfc_int_as_data
APPEND
WHEN detail = 'D'
FIELDS TERMINATED BY "," TRAILING NULLCOLS
I have created a SQL Loader control file as follows:
OPTIONS (ROWS = 1,ERRORS = 5000)
LOAD DATA
-- Header record
INTO TABLE nfc_int_headers
APPEND
WHEN header = 'HDR'
FIELDS TERMINATED BY "," TRAILING NULLCOLS
( header FILLER
, interface_data_type
, source_system_code
, interface_run_id "TO_NUMBER(:interface_run_id * -1)"
, previous_interface_complete "TO_DATE(REPLACE(:previous_interface_complete,' ','0'),'YYYYMMDDHH24MISS')"
, expiry_date "TO_DATE(:expiry_date, 'YYYYMMDD')"
, site_id
, timestamp SYSDATE
, file_id "nfc_int_data_files_seq.nextval"
)
-- Detail record
INTO TABLE nfc_int_as_data
APPEND
WHEN detail = 'D'
FIELDS TERMINATED BY "," TRAILING NULLCOLS
0