Re: sql loader size constraint in oracle 8

Hi Alex,
The maximum length for a VARCHAR2 column in Oracle 8i is 2000 characters, that's why it's failing you when you try to save 4000 chars in it.
Just in case you ask, the datatype CHAR has also a maximum size of 2000.
However, apparently you need to give it a special treatment when loading lengths greater than 255.

You need to specify the length you need in the control file, for example, if your table was created as:

CREATE TABLE foo (x VARCHAR2(2000));

Then a sample control file should look like:

LOAD DATA
INFILE <dataFile>
INTO TABLE foo
FIELDS TERMINATED BY '|'
(x CHAR(2000))

The reference to the column x in the control file reads CHAR even if the column on the table is a VARCHAR2

The 2000 length was increased (for VARCHAR2) in Oracle 9i to 4000.

Hope it helps.

Patricio
"Alex Landsman" <landsman_at_esped.com> wrote in message news:<42113e20$0$6370$9a6e19ea_at_unlimited.newshosting.com>...
> I'm trying to move a delimited file into a table.
> One of the fields in my table is set to varchar(4000), however the input is
> sometimes longer than 4000.
> I'd like to truncate the field to 4000 characters.
> My control file looks like this;
> TRUNCATE
> INTO TABLE MYTABLE FIELDS TERMINATED BY "," optionally enclosed by
> '"'
> trailing nullcols
>
> (
> BIGFIELD CHAR(4000) "substr(:BIGFIELD,1,4000)"
> )
>
> This does not work. In fact doing a substr on anything over 255 chars does
> not seem to work even when you specify the size. The error looks like this;
> Rejected - Error on table MYTABLE, column BIGFIELD, Field in data file
> exceeds maximum length
>
>
> I've seen this question put forth a few times using a google search, but it
> never seems to get answered, so any help here would be very appreciated.
Received on Tue Feb 15 2005 - 07:25:47 CST