Loading data with embedded Newlines -- that is something that has been problematic for SQLLDR historically – how to load free form data that may include a newline in it. The newline character is the default “end of line” character to SQLLDR and the ways around this did not offer much flexibility in the past. Fortunately, in Oracle8i 8.1.6 and up, we have some new options. I'll list them out with some examples and tell you which work in which versions. I see you have version 8.1.5 so the "easiest" option (the STR option below) is not available.

The options for loading data with embedded newlines are now as follows:

o Load the data with some other character in the data that represents a newline (e.g. put the string \n in the text where a newline should appear) and use a SQL function to replace that text with a chr(10) during load time. This works for string data of 4000 bytes or less only! This works in all releases (although in 7.x, the limit is 2000 bytes)

o Use the FIX attribute on the infile directive and load a fixed length flat filed. This works in all releases.

o Use the VAR attribute on the infile directive and load a varying width file that uses a format such that the first few bytes of each line are the LENGTH of the line to follow. This also works in all releases.

o Use the STR attribute on the infile directive to load a varying width file with some sequence of characters that represent the end of line – as opposed to just the newline character representing that. This is new in Oracle8i release 8.1.6 (it appears to work in 8.1.5 but it is not documented nor supported and you get funny results in the log file about the number of records actually loaded and processed. In 8.1.6 -- it works as expected and is documented/supported)

We will demonstrate each in turn.

Use some character other then a newline:

This is an easy method if you have control over how the input data is produced. If it is easy enough to convert the data when creating the data file – this will work fine. The idea is to apply a SQL function to the data on the way into the database, replacing some string of characters with a newline. An example control file with inline data could be:

load data
infile *
into table T
TRUNCATE
fields terminated by ',' optionally enclosed by '"'
(
TEXT "replace(:text,'\\n',chr(10))"
)
begindata
How Now\nBrown Cow

Notice how in the call to replace we had to use “\\n” not just “\n”. This is because \n is recognized by SQLLDR as a newline and it would have converted it into a newline – not a 2 character string. In SQLLDR -- \\n is how to get the character string constant \n into the control file. When we execute SQLLDR with the above control file, the table T is loaded with:

ops$tkyte@DEV816> select * from t;

TEXT
----------
How Now
Brown Cow

Use the FIX attribute:

The FIX attribute is another method available to us. If you use this – the input data must appear in fixed length records. Each record will be exactly the same number of bytes as any other record in the input data set. When using POSITIONAL data – this is especially valid, those files are typically fixed length input files to begin with. When using “free form” data, this is less flexible as those files are generally varying length files to begin with.

When using the FIX attribute, you will code a control file similar to this:

load data
infile fix.dat "fix 25"
into table T
TRUNCATE
fields terminated by ',' optionally enclosed by '"'
(
TEXT
)

That specifies an input data file that will have records that are 25 bytes each. This includes the trailing newline that may or may not be there – in this case, the newline is nothing special in the input datafile. It is just another character to be loaded or not. So, if you take a datafile such as:

$ cat fix.dat
123456789012345678901234
how now
brown cow
this
is
another
line

Where each and every line is exactly 25 bytes long and load it using the above control file, you will get:

ops$tkyte@DEV816> select '"' || text || '"' text, dump(text,16) dumptext from t
2 /

TEXT DUMPTEXT
------------------------------ ----------------------------------------
"123456789012345678901234 Typ=1 Len=25:
" 31,32,33,34,35,36,37,38,39,30,31,32,33,3
4,35,36,37,38,39,30,31,32,33,34,a

"how now Typ=1 Len=25:
brown cow 68,6f,77,20,6e,6f,77,a,62,72,6f,77,6e,20
" ,63,6f,77,20,20,20,20,20,20,20,a

"this Typ=1 Len=25:
is 74,68,69,73,a,69,73,a,61,6e,6f,74,68,65,
another 72,a,6c,69,6e,65,20,20,20,20,a
line
“

Notice how each row loaded ends with a newline – that is because my input data always had a newline in the 25’th byte. If this is not desired, you should enclose your data in quotes (and account for that additional space on each line in the FIX attribute). In that fashion, the trailing newline will not be loaded as part of the input data.

Using the VAR attribute:

Another method of loading data with embedded newline characters is to use the VAR attribute. When using this format, each record will begin with some fixed number of bytes that represent the total length of the incoming record. Using this format – I can load varying length records that contain embedded newlines – but only if I have a record length field at the beginning of each and every record. So, if I use a control file such as:

load data
infile var.dat "var 6"
into table T
TRUNCATE
fields terminated by ',' optionally enclosed by '"'
(
TEXT
)

The “VAR 6” says that the first 6 bytes of each input record will be the length of that input record. If I take a datafile such as:

$ cat var.dat
000028"1234567890123456789012345"
000020"How now
brown cow"
000023"this
is
another
line"

I can load it using that control file. In my input datafile – I have 3 lines of data. The first line starts with 000028 – meaning that the next 28 bytes represent the first input record. As you can see I have 25 characters of text (the number 123…345 plus 2 characters for the quotes plus 1 character for the newline). The next line starts with 000020 – it has 20 bytes of text and so on. Using this format datafile, we can easily load our data with embedded newlines.

Using the STR attribute:

This is perhaps the most flexible method of loading data with embedded newlines. Using the STR attribute – I can specify a new end of line character (or sequence of characters). This allows you to create an input datafile that has some special character at the end of each line – the newline is no longer “special”.

I prefer to use a sequence of characters – typically some special “marker” and then a newline. This makes it easy to see the end of line character when viewing the input data in a text editor or some utility as each record still has a newline at the end of it. To use this, we might have a control file like:

load data
infile str.dat "str X'7c0a'"
into table T
TRUNCATE
fields terminated by ',' optionally enclosed by '"'
(
TEXT
)

The above usings the “STR X’7c0a’” to specify that each input record will end with a | (pipe character) and a newline. To construct that string – made of the hex characters X’7c0a’ – I used SQLPlus. For example to see that a PIPE is 7c, and a newline 0a, I simply:

ops$tkyte@DEV816> select to_char( ascii( '|' ), 'xx' ) from dual
2 /

TO_
---
7c

ops$tkyte@DEV816> select to_char( ascii( '
2 ' ), 'xx' ) from dual;

TO_
---
a

So, if your input data looks like:

$ cat str.dat
123456789012345678901234|
how now
brown cow|
this
is
another
line|

The above control file will load it correctly.