Re: Unable to load a string of length 356 into VARCHAR2(2000) column

DA Morgan <damorgan_at_x.washington.edu> wrote in message news:<1098157499.52537_at_yasure>...
> scjfan122yahoo.com wrote:
>
> > Hi,
> >
> > I have been trying to load several text records into a table
> > consisting a VARCHAR2(2000) column using SQL*Loader with delimiter.
> > Most records with short text length have no problem being loaded into
> > the table. However, for those with longer text lengths I keep getting
> > the following error message in the log file:
> >
> > Record 1: Rejected - Error on table XXX, column YYY.
> > Field in data file exceeds maximum length
> >
> > I counted the text length of one of the rejected record which had only
> > 356 characters. I use "=" as the column delimiter. I do not
> > understand why VARCHAR2(2000) can not hold 356 characters. Where did
> > I miss?
> >
> > Thanks,
> >
> > Simon
>
> It can. What I can't understand is why you would use a reserved
> character as a delimiter? Are tilde's and pipes broken on your keyboard?
>
> But lets see ... no Oracle version and no control file so my suspicion
> as to what is wrong may be off-base.

Maybe the "=" is not the best choice as being the delimiter. As I stated in the original message, some records with short text length were loaded into the table using the same control file during the same loading. Anyway I change "=" to "|" and load it again which returned the same error message in the log.

After seeing you message I tried to use the insert statement on one of the long record, and the insert operation was successful. Your hunch is right that the problem is not in the table but somewhere in the loading process. The Oracle server release is 8.1.6. Here is the control file:

LOAD DATA INFILE 'xxx.dat'

APPEND
INTO TABLE yyy

FIELDS TERMINATED BY '|' (zzz "RTRIM(:zzz, ' ')"
,timestamp "TO_DATE(:timestamp, 'YYYYMMDD')" )

Where do I miss?

Simon Received on Tue Oct 19 2004 - 06:54:30 CDT