OK Scott, I'm asking for it but http://www.orafaq.com/wiki/SQL*Loader_FAQ ;
<http://www.orafaq.com/wiki/SQL*Loader_FAQ> and the reference Oracle docs. are
all littered caveats for loading delimited variable length data with comments
such as:
"NOTE: The default data type in SQL*Loader is CHAR(255). To load character
fields longer than 255 characters, code the type and length in your control
file. By doing this, Oracle will allocate a big enough buffer to hold the
entire column, thus eliminating potential "Field in data file exceeds maximum
length" errors. "
Put things another way, if you specify the column name and size of 3000 in the
control file, does the error go away and the csv file row load?
Mike
http://www.strychnine.co.uk ;<http://www.strychnine.co.uk/>
On 20 Jun 2017, at 20:41, Scott Canaan <srcdco@xxxxxxx> wrote:
Record 4: Rejected - Error on table ADVANCE.RIT_MATCHING_POLICY_2015, column
COMMENT1.
Field in data file exceeds maximum length
Scott Canaan ’88 (srcdco@xxxxxxx <mailto:srcdco@xxxxxxx>)
(585) 475-7886 – work (585) 339-8659 – cell
“Life is like a sewer, what you get out of it depends on what you put into
it.” – Tom Lehrer
From: Mark J. Bobak [mailto:mark@xxxxxxxxx] ;
Sent: Tuesday, June 20, 2017 2:37 PM
To: Scott Canaan
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: SQL*Loader Question
What error do you see in the SQL*Loader log file for the rows that fail to
load?
-Mark
On Tue, Jun 20, 2017 at 1:58 PM, Scott Canaan <srcdco@xxxxxxx
<mailto:srcdco@xxxxxxx>> wrote:
I am trying to load a CSV file into Oracle 12.1.0.2 using SQL*loader. I’ve
never run into an issue like the one I’m having now. The fields are all
variable length and comma-delimited. If the field has commas in it, then it
is enclosed in quotes. All looks good. In the CTL file, I start with:
load data
infile 'Advance2015.05_2017.csv'
badfile 'Advance2015.bad'
replace
into table advance.rit_matching_policy_2015
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
Then list the columns. Near the end is a very long column (comment1) which
is defined as varchar2(3000) in the database. SQL*Loader will load all of
the rows fine, except those that have quotes around the comment1 field. I
can’t figure out how to get it to load those rows and why it’s having trouble
with the quotes (“). I’m sure it’s something really simple, but I just can’t
see it.
Thank you,
Scott Canaan ’88 (srcdco@xxxxxxx <mailto:srcdco@xxxxxxx>)
(585) 475-7886 <tel:(585)%20475-7886> – work (585) 339-8659
<tel:(585)%20339-8659> – cell
“Life is like a sewer, what you get out of it depends on what you put into
it.” – Tom Lehrer
