Re: Correct settings in SQL*Loader control file

"Frank van Bortel" <frank.van.bortel_at_gmail.com> wrote in message news:d6i89l$uq6$1_at_news6.zwoll1.ov.home.nl...
> Randy Harris wrote:
> > "Frank van Bortel" <frank.van.bortel_at_gmail.com> wrote in message
> > news:d6hg6o$sv6$1_at_news6.zwoll1.ov.home.nl...
> >
> >>Randy Harris wrote:
> >>
> >>>"Randy Harris" <randy_at_SpamFree.com> wrote in message
> >>>news:iOUhe.860$VS6.18_at_newssvr19.news.prodigy.com...
> >>>
> >>>
> >>>>I'm having problems with SQL*Loader.
> >>>>
> >>>>This is the top of my control file.
> >>>>
> >>>>LOAD DATA
> >>>>INFILE mydata.dat "str X'7C7C'"
> >>>>INTO TABLE evals_tmp
> >>>>APPEND
> >>>>FIELDS TERMINATED BY X'0A'
> >>>>TRAILING NULLCOLS
> >>>>
> >>>>One of the fields corresponds to a VARCHAR2(1000) in the eval_tmp
table.
> >
> > I
> >
> >>>>was getting errors about the data being too long, if the column had
more
> >>>>than 255 characters, until I changed the line in the control file to:
> >>>>
> >>>>DELIVERYCOMMENTS char(1000) "SUBSTR(:deliverycomments,1,1000)",
> >>>>NRESP1,
> >>>>NRESP2,
> >>>>
> >>>>That stopped that particular error. Now, however, if the field data
in
> >>>
> >>>the
> >>>
> >>>
> >>>>data file is actually longer than 1000 characters, it gets an error on
> >
> > the
> >
> >>>>next field (NRESP1), saying that the data is too long. What should I
> >
> > put
> >
> >>>in
> >>>
> >>>
> >>>>the control file so that it will still import the record and simply
> >>>
> >>>discard
> >>>
> >>>
> >>>>any data beyond the 1000 characters for this field?
> >>>
> >>>
> >>>Can anyone tell me with certainty whether I've run into a limitation of
> >>>SQL*Loader in 8.1.7.4? The load works fine if there is no data longer
> >
> > than
> >
> >>>1000 characters to insert. If there is no way to do this in SQL*Loader
> >
> > I
> >
> >>>can pre-process the files with sed, but I would very much prefer to
find
> >
> > a
> >
> >>>way to get SQL*Loader to truncate the input fields.
> >>>
> >>
> >>Would DELIVERYCOMMENTS char(32000) "SUBSTR(:deliverycomments,1,1000)"
> >>do the trick? It allows for fields up to 32k, but uses only the first
> >>1000 characters.
> >>By 32000, the termination character should have come by,
> >>should it not?
> >>
> >>
> >>--
> >>Regards,
> >>Frank van Bortel
> >
> >
> > Ahhh! That might do the job. Yes, the termination character would
ALWAYS
> > be within 32k characters. You're suggesting that it loads the entire
data
> > into the buffer before it does the SUBSTR on it, correct? I was
thinking
> > backwards - the output from the SUBSTR function getting placed into the
> > input buffer.
> >

>

Voila! Setting the buffer size to 32k did the trick. Thank you very much. Received on Thu May 19 2005 - 10:36:48 CDT