How to preserve newlines with SQL*Loader?

From: John Peterson <johnp_at_azstarnet.com>
Date: Mon, 14 May 2001 18:27:41 -0700
Message-ID: <tg11gmbcbehv0c@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 Mon May 14 2001 - 20:27:41 CDT