Re: sqlldr problem

From: Morten Primdahl <morten_at_caput.com>
Date: Tue, 12 Feb 2002 10:28:34 +0100
Message-ID: <3C68E042.2090707@caput.com>

Sunil Pant wrote:

> I have a control file as below:
>
> load data
> infile 'zprod_rev.dmp'
> append into table zprod_rev
> FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY "*text"(
> reviewid,reviewbody char(64000),rating)
>
> The reviewbody has a lot of carriage returns and sql loader takes each one
> of them as new rows and eventually exceeds the error limits. How do I tell
> sql loader not to take carriage returns as new rows or define something like
> *row as row delimiter.

You can give a STR directive, read the following snippet:

Dealing with multi-lined entries with Oracle 8i's SQL*Loader (sqlldr)

---

Rather than use the CONTINUEIF clause, use the STR directive of the

INFILE clause. Basically what you do, is supply a hex valued string

which is to be interpreted as the separator for your logical records

(typically each line in the data file is a logical record). In the

case below, '7cOa' is the separator, ie. |\n (as any ASCII table

will reveal).

bash-2.03$ cat demo.ctl
OPTIONS (direct=true)
LOAD DATA
INFILE 'demo.dat' "STR X'7c0a'"
INTO TABLE demo_table
FIELDS TERMINATED BY '#' OPTIONALLY ENCLOSED BY '"' ( id sequence,
  startdate DATE "dd-mm-yyyy",
  enddate DATE "dd-mm-yyyy",
  title,
  column_x CONSTANT 'x value',
  column_y CONSTANT 'y value'
)

bash-2.03$ cat demo.dat

24-03-1973#25-03-1973#"Title 1"|
24-03-1974#25-03-1974#"Title 2"|
24-03-1975#25-03-1975#"Title num

ber 3 aka. breaker Kenny"|

HTH, Morten

-- 
Morten Primdahl         Caput A/S          Phone +45 70 12 24 42
System Integrator       Nygade 6           Fax   +45 70 11 24 42
morten@caput.com        DK-1164 Kbh K      http://www.caput.com/

Received on Tue Feb 12 2002 - 03:28:34 CST