How to preserve newlines with SQL*Loader?

[Quoted] (Oracle 8.1.7 on Win2K)

Hello, all!

I've been struggling with a control file specification for SQL*Loader and am [Quoted] *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)

INFILE      *
BADFILE     '.\test.bad'

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.';
[Quoted] ${'Line2'} = 'This is a test with some

carriage returns.

How do these get preserved?
';
[Quoted] ${'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 [Quoted] 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 - 03:27:41 CEST