Re: How to preserve newlines with SQL*Loader?

Hmmm...maybe newlines can't be preserved, per se, since the newline is interpreted as an end-of-physical-record delimiter. Is there any way to *reintroduce* a newline for every continued line?

I would truly appreciate any help. Thanks! :-)

John Peterson

"John Peterson" <johnp_at_azstarnet.com> wrote in message news:tg11gmbcbehv0c_at_corp.supernews.com...
> (Oracle 8.1.7 on Win2K)
>
> Hello, all!
>
> I've been struggling with a control file specification for SQL*Loader and
 am
> *close* to defining it with the exception of one issue: preserving
> "embedded" newlines/carriage returns.
>
> Consider the following table structure:
>
>
> create table Test (Field1 VARCHAR2(255) NOT NULL,
> Field2 VARCHAR2(4000) NOT NULL);
>
>
> My control file looks like:
>
>
> ----- Control File Start -----
> OPTIONS (ERRORS=999999999)
>
> LOAD DATA
> INFILE *
> BADFILE '.\test.bad'
> DISCARDFILE '.\test.dis'
>
> TRUNCATE
> CONTINUEIF LAST != ';'
>
> INTO TABLE Test
> WHEN (1) = '$'
> TRAILING NULLCOLS
> (
> Field1 CHAR(255) ENCLOSED BY "${'" AND "'} = ",
> Field2 CHAR(4000) ENCLOSED BY "'" AND "';"
> )
>
> BEGINDATA
> ${'Line1'} = 'This is a test.';
> ${'Line2'} = 'This is a test with some
>
> carriage returns.
>
> How do these get preserved?
> ';
> ${'Line3'} = 'This is the last line.';
> ----- Control File End -----
>
>
> This will seemingly successfully run, however when I execute the following
> to see which records have embedded newlines:
>
>
> select *
> from Test
> where (instr(Field2, chr(10)) > 0 or instr(Field2, chr(13)) > 0);
>
>
> No records are return, making me suspicious that embedded newlines were
 not
> preserved. From what I've read of ENCLOSED BY, it *should* embed the
> newlines/linefeeds.
>
> I would greatly appreciate any help/suggestions! Thanks! :-)
>
> John Peterson
>
>
Received on Tue May 15 2001 - 08:52:49 CDT