Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> SQLLDR: Record terminator question

SQLLDR: Record terminator question

From: jcmon1 <jcmon1_at_aol.com>
Date: Wed, 30 Apr 2003 16:40:56 -0400
Message-ID: <3EB034D8.20D00294@aol.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US