Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> SQLLDR: Record terminator question
Hello. I'm having trouble using SQLLDR to load records that have a
column containing control characters (such as carriage return/line
feed).
I've set up the control file shown below to read the data file as a
stream using the hex value of the following termination characters:
double quotes + carriage return + line feed. This sequence was verified
in the data file using a Hex editor. The last field in the sequence is
a date that is always present and is enclosed by double quotes as in the
following data sample:
0,68,"058742247","","2001-01-02-00.00.00.000000"
1,41,"021093833","R.P. ONLY
REQUIRES OFFICERS APPROVAL","2001-11-27-11.34.32.101000"
0,56,"021072663","USD DIS L - IC
R.P.
OVERNIGHT PLACEMENT","2001-11-27-11.34.32.243000"
0,71,"029876613","","2001-01-02-00.00.00.000000"
0,74,"026549479","","2001-01-02-00.00.00.000000"
The first and last 2 records are single-line, while the 2nd and 3rd
record have multiple lines based on the content of the 4th column that
has a carriage return/line feeds.
The problem I'm having is that by specifying the double quotes in the
termination character, they seem to be removed from the data being read
by the Loader; this results in the following error being repeated in the
Log file:
Record 1: Rejected - Error on table ACCTHLDR, column LASTUPDT. second enclosure string not present
My question is if there is any way to specify the termination character
(or any other approach altogether) that would allow me to successfully
load this data. Since my actual data file will be rather large, I
cannot easily modify it to hold a different termination character; I
would have to write some kind of script to automate this.
Thanks to all for any suggestions.
Control File:
LOAD DATA
INFILE "file1.txt" "str X'220d0a'"
INTO TABLE AcctHldr REPLACE
FIELDS TERMINATED BY ","
optionally enclosed by '"'
trailing nullcols
(
blocked char(10),
updtId char(20),
abaNumber CHAR(30) enclosed by '"',
standingInstr CHAR(3000) enclosed by '"',
LastUpdt DATE "yyyy-mm-dd-hh24.mi.ss"
enclosed by '"'
"SUBSTR(:LastUpdt,1,19)"
)
Received on Wed Apr 30 2003 - 15:40:56 CDT
![]() |
![]() |