Re: SQL Loader and carriage returns

[Quoted] joaquin_at_sbcglobal.net (Joaquin) wrote in message news:<46b362cb.0309291449.223bd449_at_posting.google.com>...
> wumutek_at_yahoo.com (Vincento Harris) wrote in message news:<2fa13ee7.0309260920.5f9aec0e_at_posting.google.com>...
> > Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1064514197.430051_at_yasure>...
> > > Joaquin wrote:
> > >
> > > >Hi All,
> > > >
> > > >I'm having a problem loading large files into Oracle 8.1.7 DB. If
> > > >there are embedded carriage returns SQL Loader make two records out of
> > > >one. I tried "STR X'7c0a'" but Loader bomded and did not like it. Any
> > > >help would be greatly apericated.
> > > >
> > > >Thanks,
> > > >Joaquin
> > > >
> > > >
> > > Clean up the file before loading.
> >
> > Did you ftp the file from a unix envioronment or how did you get
> > carriage returns in your file?
> > If you did make sure you use ascii rather than binary when transfering
> > your files.
> >
> > Vince
>
> The carriage returns come over to us from the client. I believe the
> problem starts with text fields on a web site. One problem we also
> have is the the data is going to have a hashing algorithm ( I hope I'm
> stating this right) so I'm worried that is we added any characters to
> the data it might pose a curoption problem with the algorithm. The
> files come zipped to us then we unzip them in Unix and run a program
> to split the batch. Also it's never the same field that the carriage
> return is in. Here is part of my .ctl file
>
> LOAD DATA
> INFILE 'ptltdr/InBound/DH.txt' "STR X'230a'"
> TRUNCATE
> INTO TABLE LTDR WHEN RecordTypeCode = "DH"
> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
>
> when I run this it rejects it
>
> Rejected - Error on table LTDR, column LASTUPDATEUSER.
> no terminator found after TERMINATED and ENCLOSED field
>
>
> Thanks for responses
> Joaquin

You could try the "dos2unix" unix command on the file. It will remove any Windows formatting characters. Received on Wed Oct 01 2003 - 10:06:41 CEST