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
Received on Thu May 19 2005 - 02:49:57 CDT