Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Correct settings in SQL*Loader control file

Re: Correct settings in SQL*Loader control file

From: Gerard H. Pille <ghp_at_skynet.be>
Date: Tue, 17 May 2005 03:57:40 +0200
Message-ID: <42894eca$0$8229$ba620e4c@news.skynet.be>


Randy Harris wrote:
> "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.
>
>

You are right, as I've read in the manual. It seems SQL*Loader won't be able to manage this case. Received on Mon May 16 2005 - 20:57:40 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US