Custom Search
www.rocket99.com : Technical Guides Sybase Oracle UNIX Javascript


Technical Guides
Sybase
Oracle
UNIX
Javascript




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












Oracle : Related Topics


Sybase Web Site
Sybase iAnywhere Mobile Web Site
Oracle Enterprise Web Site



Get the latest Rocket99 news and tech tips via






Site Index About this Guide to Sybase, Oracle, and UNIX Contact Us Advertise on this site




Copyright © 2013 Stoltenbar Inc All Rights Reserved.