Re: Correct settings in SQL*Loader control file

"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. Received on Thu May 19 2005 - 09:00:00 CDT