Of Interest
Business Intelligence and Analytics
Oracle Training
|
Oracle
»
PL-SQL
»
SQL-Loader
»
Text-Wrap-CRLF
SQL Loader: Using SQL Loader with Inline carriage returns
Using SQLLDR to Load Data with Embedded CRs in Text/Comment Fields
Oracle's SQLLDR is dead-set as having 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.
Data file should have || as the field separator, record separator would be '|\n'
e.g.
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.
Very challenging.|
12828929||1990||Worked with VAX VMS|
Sample Control File, for SQLLDR
LOAD DATA
INFILE 'resume_detail.dat'
CONTINUEIF LAST != "|"
INTO TABLE resume_detail
APPEND
FIELDS TERMINATED BY '||' TRAILING NULLCOLS
(
CANDIDATE_ID,
YEAR,
JOB_CMT
)
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
|
