Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: sql loader size constraint in oracle 8

Re: sql loader size constraint in oracle 8

From: <dhh_at_my.invalid>
Date: 15 Feb 2005 14:17:41 GMT
Message-ID: <cut0a5$li8$3@news.liv.ac.uk>


,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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US