Below is what I have tested.
CREATE TABLE TEST1
(
COL1 VARCHAR2(10),
COL2 VARCHAR2(10),
COL3 VARCHAR2(10)
);
SELECT UTL_RAW.CAST_TO_RAW('|'||chr(10)) from dual;
RESULT
------
7C0A
My data file record delimiter is a "|" followed by newline.
Below are the contents of my data file and control file.
Control File
OPTIONS(SKIP=1)
LOAD DATA
INFILE 'J:\TEST.txt' "str X'7C0A'"
BADFILE 'J:\TEST.bad'
INTO TABLE TEST1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
COL1 CHAR,
COL2 CHAR,
COL3 CHAR
)
Data File
A,B,C|
AA,BB,EE|
CC,DD,|
Output
C:\>sqlldr user/pass CONTROL='J:\TEST_CTL.ctl'
SQL*Loader: Release 11.2.0.1.0 - Production on Tue Aug 11 17:55:10 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
C:\>
Log File
SQL*Loader: Release 11.2.0.1.0 - Production on Tue Aug 11 17:55:10 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Control File: J:\TEST_CTL.ctl
Data File: J:\TEST.txt
File processing option string: "str X'7C0A'"
Bad File: J:\TEST.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 1
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table TEST1, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
COL1 FIRST * , O(") CHARACTER
COL2 NEXT * , O(") CHARACTER
COL3 NEXT * , O(") CHARACTER
Table TEST1:
0 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 49536 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 1
Total logical records read: 0
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Tue Aug 11 17:55:10 2015
Run ended on Tue Aug 11 17:55:15 2015
Elapsed time was: 00:00:05.84
CPU time was: 00:00:00.03
If I remove the last pipe as a line terminator and only keep the new line (STR=0A) then the records are loaded fine.
It would be very helpful if you could tell me what is the problem in keeping the pipe plus newline as a record delimiter.
Thanks