Re: Question: using SQLLoader to load a VARCHAR(2000) field

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/02/17
Message-ID: <34ea043d.7279709@www.sigov.si>#1/1

On 17 Feb 1998 16:16:04 GMT, qiang01_at_aol.com (QIANG01) wrote:

>Dear Oracle experts,
>
>I have a question on SQLLoader (Release 7.3.2.2.0).
>
>I want to load a data file into a table, which is defined as:
>
>temp_table
>(
> long_string VARCHAR2(2000)
>)
>
>As long as the field in the file is longer than 258 characters, I got a "Field
>in data file exceeded maximum specified length".
>
>Oracle defines VARCHAR2's maximum length as 2000, so I don't understand how
>this could happen. I tried to use 'PIECED' option as well as setting BINDSIZE
>to a big number using Direct Load, but it just doesn't work. Does this have
>anything to do with CHAR's maximum length is 255?

The problem is not related with table's column datatype. The cause of your problem lies within your SQL*Loader controll file. In loader's ctrl file the default maximum length of delimited CHAR field is 255 bytes. If your file have fields longer then that, you must explicitely specify the maximum length of those fields.

So, in your example, your controll file should look something like this:

LOAD DATA ......
INTO TABLE temp_table ....
FIELDS TERMINATED BY '[delimiter]'
(large_string CHAR(2000))

>Your help is greatly appreciated!!!
>
>Please email chan9055_at_cs.nyu.edu. Thanks.
>Song Chang
>auto parts

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Tue Feb 17 1998 - 00:00:00 CST