You may struggle to load data with embedded line separator (new lines of in-line carriage return). sqlloader offer two possibilities :

• if you have a special character at the end of each line: the stream record format

Using the STR attribute, we can specify a new end-of-line character (or sequence of characters). This allows us to create an input data file that has some special character at the end of each line. The newline is no longer special.

• if you don't have a special character at the end of each line: the CONTINUEIF statement

## Articles Related

The target.dat file with the data as csv format with double quote and a vertical bar at the end of each line. Suppress the vertical bar when you test with the CONTINUEIF statment.

"1","My texte with
a of
several carriage return
in the de description. If you add to this

text that it wil be a
BIGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG

text with moooooooooooooooooooooooore than
255 character, you must then
explicitly give the size
of the          buffer. If you count it, you will then find 330 characters"|
"2","A second row but only to not let alone the first one"|

## The target table

 CREATE TABLE target
(
ID integer,
DESCRIPTION varchar2(500)
);

## The SQLLDR command syntax

When you have create the control file (see section below), just run this command:

C:\data_to_load>sqlldr userid=gerardnico/gerardnico control=target.ctl log=target.log

SQL*Loader: Release 10.2.0.3.0 - Production on Fri Jan 8 01:16:00 2010

Commit point reached - logical record count 2

## The controle files

Depending of your platform (Windows, Unix, Mac-Os), the end of line changes: See Data Storage - (Newline|End of Line (EOL)|Line Separators) by OS and language

### STR

Stream Record format Documentation “str X'220D0A'” is equivalent to “str '|\r\n'” for the windows platform.

LOAD DATA
INFILE 'target.dat' "str X'220D0A'"
INTO TABLE target
REPLACE
FIELDS TERMINATED BY ','
(
"ID",
"DESCRIPTION" CHAR(500)
)
The terminator_string is specified as either 'char_string' or X'hex_string' where:
• 'char_string' is a string of characters enclosed in single or double quotation marks
• X'hex_string' is a byte string in hexadecimal format that you can get with the cast_to_raw function as:
SELECT utl_raw.cast_to_raw( '|'||chr(10) ) FROM dual;
UTL_RAW.CAST_TO_RAW('|'||CHR(10))
-------------------------------------------------------------------------------
7C0A

Nonprintable characters should be specified as an X'hex_string' however some nonprintable characters can be specified as

• Carriage returns = \r = chr(13)
• Line Feeds = \n = chr(10)
• Vertical tab = \v =
• Horizontal tab = \t = chr(9)

### CONTINUEIF LAST

The controle file with the clause CONTINUEIF LAST

LOAD DATA
INFILE 'target.dat'
CONTINUEIF LAST != '"'
INTO TABLE target
APPEND
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
"ID",
"DESCRIPTION" CHAR(500)
)

## The result

SET pagesize 9999
SET linesize 105
COLUMN description format A100
COLUMN id format 99
SELECT id, description FROM target;

### With the stream format

SQL> SELECT id, description FROM target;

ID DESCRIPTION
--- -----------------------------------------------------------------------------------
1 My texte with
a of
several carriage return
in the de description. If you add to this

text that it wil be a
BIGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG

text with moooooooooooooooooooooooore than
255 character, you must then
explicitly give the size
of the          buffer. If you count it, you will then find 330 characters

2 A second row but only to not let alone the first one

### With CONTINUEIF

No carriage return are still visible. You lost the format.

 ID DESCRIPTION
--- ----------------------------------------------------------------------------------------------------
1 My texte witha ofseveral carriage return in the de description. If you add to thistext that it wil b
e aBIGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGtext with moooooooooooooooooooooooore than255 charac
ter, you must then explicitly give the size  of the          buffer. If you count it, you will then
find 330 characters

2 A second row but only to not let alone the first one

## Support

• SQL*Loader-457: Comparison text of CONTINUEIF LAST must have length 1 not 2