Business Intelligence and Analytics
Using SQL Loader to load data with embedded CRs / Line Feeds
Using SQLLDR to Load Data with Embedded CRs in Text/Comment Fields
Oracle's SQLLDR is designed for files having a carriage return as the record separator.
Most of the documentation refers to a line in a file as a 'record' .. which is not true
in many cases. A line in a file is a "line" ... a row in a table is a "record"!
The classic case where you need to vary the record terminator is when you are importing
paragraphs of data, non-HTML text. You want to be able to pull the data from the
database and display it to the screen (or report) with the line feeds intact.
Here's how to load the data into a table via SQL LDR.
Data file should have || as the field separator, record separator would be '|\n'
12389289||1995||Worked at ABC company as an analyst
using Oracle and SQLLDR.|
12389289||1996||Worked at Acme Inc as a DBA using Oracle
control files with embedded cr in the text.
12828929||1990||Worked with VAX VMS|
Sample Control File, for SQLLDR
CONTINUEIF LAST != "|"
INTO TABLE resume_detail
FIELDS TERMINATED BY '||' TRAILING NULLCOLS
You will see something like this in the log file.
Continuation: Last non-white character != 0X7c(character '|')
Preserving continuation characters as data
Get the latest Rocket99 news and tech tips via