Re: Correct settings in SQL*Loader control file

"Gerard H. Pille" <ghp_at_skynet.be> wrote in message news:428882a0$0$13325$ba620e4c_at_news.skynet.be...
> Randy Harris wrote:
> > 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?
> >
> I'd drop the "char(1000)", since fields are terminated by X'0A', no need
to
> specify their size.

I got the idea from "Expert One-on-One Oracle". It says that SQL*Loader uses a default buffer of CHAR(255) and I would be limited to 255 characters if I didn't specify. This definitely seems to be what I've experienced while experimenting. Without that setting, any record with more than 255 characters for that field fails. Now only records with more that 1000 characters fail. Received on Mon May 16 2005 - 16:48:11 CDT