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!

SQL Loader - problems with source data that has newline chars in a string

643707Feb 13 2009 — edited Apr 13 2012

I'm using SQL Loader on Oracle EE v10.2.0.4 to load an export file from DB2 (based on these guidelines: http://download.oracle.com/docs/html/B10229_01/ch4.htm). Some strings in my source files have newlines/hard returns embedded in the string, which causes SQLLDR to fail since it assumes a new row (well, that's my assumption - when I manually clean up the hard returns, all is well and load without error). I haven't been able to figure out how to get around this issue - AFAIK, there is no row terminator I can specify for export in my source file. On load, it doesn't simply reject the row since it's having problems figuring out where to start up again-

Here my ctl file:

load data
 infile 'attrib.del'
 badfile 'attrib.bad'
 discardfile 'attrib.dsc'
 append
 into table attrib
 fields terminated by X'07' optionally enclosed by '\'' 
   trailing nullcols		  
(ATTRIB,         
 METADATA,         
 ATTRIBVALUE CHAR(4000)
)

Here's my load command:

sqlldr control=attrib.ctl, log=attrib.log, BAD=attrib.bad, userid=USER/PASSWORD, errors=0, direct=true, discard=attrib.dsc

And here's my DB2 export file:

EXPORT TO attrib.del OF DEL
  MODIFIED BY chardel'' coldel0x07 timestampformat="YYYY/MM/DD HH:MM:SS"  striplzeros
  MESSAGES attrib.out
  SELECT ATTRIB,         
	 METADATA,         
	 ATTRIBVALUE FROM attrib;

I'd prefer to preserve the strings on load with the newlines if possible - my fallback would be to write a script to strip the rogue newlines if I can't get around the load issue.

TIA

Edited by: kpw on Feb 13, 2009 10:28 AM