RE: SQL*Loader Question

Yeah! That worked. I knew it would be something simple.

Thank you.

Scott Canaan ’88 (srcdco_at_rit.edu<mailto:srcdco_at_rit.edu>)
(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: Michael D O'Shea/Woodward Informatics Ltd [mailto:woodwardinformatics_at_strychnine.co.uk] Sent: Tuesday, June 20, 2017 2:59 PM
To: Scott Canaan
Cc: Mark J. Bobak; oracle-l_at_freelists.org Subject: Re: SQL*Loader Question

OK Scott, I'm asking for it but 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

On 20 Jun 2017, at 20:41, Scott Canaan <srcdco_at_rit.edu<mailto:srcdco_at_rit.edu>> 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_at_rit.edu<mailto:srcdco_at_rit.edu>)
(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_at_bobak.net] Sent: Tuesday, June 20, 2017 2:37 PM
To: Scott Canaan
Cc: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> 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_at_rit.edu<mailto:srcdco_at_rit.edu>> 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_at_rit.edu<mailto:srcdco_at_rit.edu>)
(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

--
http://www.freelists.org/webpage/oracle-l