You may struggle to load data with embedded line separator (new lines of in-line carriage return). sqlloader offer two possibilities :
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.
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"|
create table target
(
ID integer,
DESCRIPTION varchar2(500)
);
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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 2
Depending of your platform (Windows, Unix, Mac-Os), the end of line changes: See Characters - Newline - End of Line ( EOL ) - Line Separators - Line Break
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:
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
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)
)
There is also a “next” word
CONTINUEIF NEXT(1:1) = '#'
set pagesize 9999
set linesize 105
column description format A100
column id format 99
select id, description from target;
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
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