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

"scjfan122yahoo.com" <scjfan12_at_yahoo.com> wrote in message news:1638bead.0410190354.76b906c2_at_posting.google.com...
> 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

I have never seen the problem be anywhere except in the data itself. Look for embeded carriage returns. Received on Tue Oct 19 2004 - 09:11:18 CDT