Thanks for the replies.
I did a search on google and there are some pages that claim 2000 chars is
the limit for a varchar2 in 8i and others that claim its 4000 chars. On my
version of Oracle it is definitely 4000 chars, so that isn't an issue. I did
come up with a couple of work arounds for my problem for anyone who is
interested;
1. Set a temp field up as a "LONG" in the table and set the length longer
than 4000 chars in the control file...
TEMP CHAR(6000), is my control file setting.
This will bring in the whole field and I can then call substr on it in a
PL/SQL procedure to produce my desired field.
2. I used a small java program to parse the text file and keep the field
sizes below 4001 characters.
I'm still wondering if there is a way to do through sqlloader though.
<dhh_at_my.invalid> wrote in message news:cut0a5$li8$3_at_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 - 17:07:21 CST
