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.

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 (DOT) com> wrote

,Patricio <pecolombo (AT) yahoo (DOT) 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 (DOT) com> wrote

: 370$9a6e19ea (AT) unlimited (DOT) 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)

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 (DOT) invalid> wrote

Alex Landsman wrote:
Both could be correct, as the limit is 4000 *byte* - which may
be equivalent to 4000 characters in a single-byte character set,
or *less* than 2000, in a multi-byte character set.

--
Regards,
Frank van Bortel