Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: sql loader size constraint in oracle 8
,Patricio <pecolombo_at_yahoo.com> wrote:
: 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.
Excuse me - the max size of a VARCHAR2 in Oracle8i is 4000.
: 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$6
: 370$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.
Helen (qq45 liverpool ac uk) Received on Tue Feb 15 2005 - 08:17:41 CST