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.
>
Well - if that doesn't work, up it to 256000, or
whatever you fancy.
I believe I had one of two CLOBS coming in with SQL*LOader
where I had to up them to 512k
--
Regards,
Frank van Bortel
Received on Thu May 19 2005 - 09:41:07 CDT
