Re: sqlldr and large text feilds
Date: 1997/06/23
Message-ID: <33aeda55.346511_at_www.sigov.si>#1/1
On Thu, 19 Jun 1997 22:02:39 GMT, jurij.modic_at_mf.sigov.mail.si (Jurij Modic) wrote:
>On Thu, 19 Jun 1997 10:19:44 -0700, John Caputo <caputo1_at_llnl.gov>
>wrote:
>
>>Matt Southcott wrote:
>>>
>>> I have been trying to load a file that contains descriptions that are
>>> in excess of 900 characters. I have found that sqlldr won't import a
>>> field larger than 256 characters. Is there a setting for sqlldr to
>>> overcome this problem.
>>>
>>> Matt
>>
>>I have had no problem loading text files, delimited by tabs, with fields
>>up to 2000 characters. What release of sqlldr are you using? We are on
>>7.1.3. You must define the table you want to load with a column that is
>>VARCHAR2(2000). If the data type of the column is CHAR, you can only get
>>a max of 256 characters. This is all documented in the SQL Language
>>Reference. I don't think you have a sqlldr problem.
>
>I presume Matt has a delimited, vaiable length field that is causing
>him troubles. When the field is delimited (you can't specify its exact
>position) its maximum length can be only 255 characters.
After John Caputo send me a mail I checked again and found out that delimited text fields, larger than 255 bytes, can indeed be processed by SQL*Loader. The manual says "The *default* maximum length of delinited data is 255 bytes". So, this is a DEFAULT, not an ABSOLUTE maximum length!
To specify a larger maximum length, you have to to specify the field as type CHAR (VARCHAR fields can't be delimeted) with specified maximum length in your control file, e.g.:
LOAD DATA ......
INTO TABLE my_table ....
FIELDS TERMINATED BY '[delimiter]'
(first_column, large_column CHAR(900), ......)
Regards,
Jurij Modic Republic of Slovenia tel: +386 61 178 55 14 Ministry of Finance fax: +386 61 21 45 84 Zupanciceva 3e-mail: jurij.modic_at_mf.sigov.mail.si Ljubljana 1000
Received on Mon Jun 23 1997 - 00:00:00 CEST