Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to handle carriage return/linefeed embedded in text field as first char

751641Feb 4 2010 — edited Feb 5 2010

Hi,

I am trying to use sqlldr to import text that is comma-separated and double quote-enclosed, but contains embedded carriage return/linefeed pairs. I have a control file which works for most cases and looks like this:

LOAD DATA
INFILE './foo.new'
APPEND
CONTINUEIF LAST PRESERVE (<>'"')
INTO TABLE <tablename>
FIELDS TERMINATED BY "," ENCLOSED BY '"'
TRAILING NULLCOLS

Unfortunately, this fails when the very first character of the text field is a carriage return/linefeed pair, e.g.,:

"11","22","33","\r\nThis is the fourth field","","","","",""

In that case, what is actually the opening double-quote for the fourth field, is interpreted by the CONTINUEIF clause as the end of that line. Can anyone please point me to a solution?

thanks.
stan.