About
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 data to load
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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 2
The controle files
Depending of your platform (Windows, Unix, Mac-Os), the end of line changes: See Characters - Newline - End of Line ( EOL ) - Line Separators - Line Break
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)
)
There is also a “next” word
CONTINUEIF NEXT(1:1) = '#'
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