Re: How to load newlines with SQL*Loader direct?
Date: Fri, 07 May 1999 20:13:02 +0100
Message-ID: <37333B3D.A7C83323_at_maderich.demon.co.uk>
Try using the VAR format. Each logical record is preceded by a 5 digit number
which
gives the length of thr record. The five digit number represents the entire
length
of the physical record, i.e. a totally empty record would be
00001
because the terminating newline is 1 character.
e.g.
Control File
'
LOAD DATA
INFILE "" "VAR"
APPEND
INTO TABLE APP_CHEMICAL
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
(
"COL_1" CHAR(2) ,"COL_2" CHAR ,"COL_3" CHAR(2) ,"COL_4" CHAR ,"COL_5" CHAR(2) ,"LONG_COL" CHAR(64000) -- LONG format
)
'
Data File
'
00156"~~","847112","A1","0","EP","[01]
<001> 018 | P0000 | S9999 S1434 | S9999 S1649-R
<002> 018 | ND01 | ND07 | K9392 | N9999 N6440-R | Q9999 Q7330-R | K9416
"
00135"~~","9710289","A1","0","RN","200001U0610U0760S0822S0886U0927U1020S1278S1536U1547U1677U1681U1726U1732U1762U1794U1801U1903U1939U1966S
"
'
Anatol wrote:
> I'm looking for help with the following predicament:
>
> some values that I need to load into an Oracle 8 DB have newlines (\n,
> chr(10), that thing...) inside them, which need to be preserved. How do I
> tell the SQL*Loader to do that? I would like it to work in direct, rather
> than SQL mode, because the data volume is rather substantial.
>
> I did manage to make the SQL*Loader _ignore_ the newlines ( by using
> "OPTIONALLY DELIMITED BY" clause), but in that case it just concatenated the
> two lines. I need the newlines to be loaded into the DB, not ignored.
>
> Thanks in advance,
>
> --
> Anatol Zolotusky
> anatol_at_itn.net
> az1_at_best.com
>Received on Fri May 07 1999 - 21:13:02 CEST
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own