"Mike Krolewski" <mkrolewski_at_rii.com> wrote in message
news:94asul$kvj$1_at_nnrp1.deja.com...
> In article <94asct$khj$1_at_nnrp1.deja.com>,
> Don't Bother <davidknollhoff_at_netscape.net> wrote:
> >
> >
> > I've created a control file and I've replaced all chr(13) and chr(10)
> > with a '][' in the data portion. I've modified the control file to
try
> > and import this way but get the following error. The tables contain
> > several chr(13)||chr(10) which get replaced fine in the begindata (
> > portion but can import. ;
> >
> > SQL*Loader-350: Syntax error at line 12.
> > Expecting "," or ")", found "(".
> > , replace(ADDRESS1,'][',chr(13))||chr(10)
> >
> > David Knollhoff
> >
> > My control file looks like;
> >
> > UNRECOVERABLE
> > LOAD DATA
> > INFILE *
> > INTO TABLE address
> > REPLACE
> > FIELDS TERMINATED BY '|' TRAILING NULLCOLS
> > (
> > LLSID
> > , COUNTER
> > , ADDRESSTYPE
> > , COUNTRYID
> > , replace(ADDRESS1,'][',chr(13))||chr(10)
> > , replace(ADDRESS2,'][',chr(13))||chr(10)
> > , replace(ADDRESS3,'][',chr(13))||chr(10)
> > , replace(ADDRESS4,'][',chr(13))||chr(10)
> > , replace(CITY,'][',chr(13))||chr(10)
> > , replace(DESCRIPTION,'][',chr(13))||chr(10)
> > , replace(POSTALCODE,'][',chr(13))||chr(10)
> > , PRIMARY
> > , replace(ZIP4,'][',chr(13))||chr(10)
> > , replace(STATEORPROVINCE,'][',chr(13))||chr(10)
> > , replace(LASTCHANGEDBY,'][',chr(13))||chr(10)
> > , LASTCHANGEDDATE date "DD-MON-YY:HH24MISS"
> > , replace(BILLNAME,'][',chr(13))||chr(10)
> > , BADADDRESS
> > , replace(BILLATTENTION,'][',chr(13))||chr(10)
> > , OMITFROMMAILINGS
> > , replace(BILLFORMATID,'][',chr(13))||chr(10)
> > , NUMBEROFCOPIES
> > , replace(PICARDUSERNAME,'][',chr(13))||chr(10)
> > )
> > BEGINDATA
> > 4|1|H|262|245 Union Ave. #1082||||Campbell||95008|P||CA|RUTHANNE|01-
JUL-
> > 00:152324||F||F|||
> > 5|1|H|262|P.O.Box 22265||||San Francisco||94122|P||CA|RUTHANNE|01-JUL-
> > 00:161347||F||F|||
> > 6|1|H|262|314 Watson Street||||Monterey||93940|P||CA|RUTHANNE|01-JUL-
> > 00:155337||F||F|||
> >
> > Sent via Deja.com
> > http://www.deja.com/
> >
>
>
> The correct format is:
>
> FIELDS TERMINATED BY '|' TRAILING NULLCOLS
> (
> LLSID
> , COUNTER
> , ADDRESSTYPE
> , COUNTRYID
> , ADDRESS1 "replace(:ADDRESS1,'][',chr(13))||chr(10)"
> , ADDRESS2 "replace(:ADDRESS2,'][',chr(13))||chr(10)"
>
>
> There are good examples in the manual on the syntax in Case examples
> and of course in chapter 5 in section "Applying SQL Operators to
> Fields". Reading is a good thing.
>
>
> --
> Michael Krolewski
> Rosetta Inpharmatics
> mkrolewski_at_rii.com
> Usual disclaimers
>
>
> Sent via Deja.com
> http://www.deja.com/
Just an addition:
IMHO it looks like
, ADDRESS1 "replace(:ADDRESS1,'][',chr(13))||chr(10)"
should be
, ADDRESS1 "replace(:ADDRESS1,'][',chr(13)||chr(10))"
Regards,
Sybrand Bakker, Oracle DBA
Received on Sat Jan 20 2001 - 17:03:39 CST
