Problem importing LOB into Oracle using SQL Loader

Question

I'm trying to export from SQL Server to Oracle a table that consists of an ID and a really long XML string stored as nvarchar(max).

SQL Table -- CREATE TABLE MyStuff (MyID int, MyText nvarchar(max))
Oracle Table -- CREATE TABLE MyStuffImported (MyID int, MyText NCLOB)

I'm using the SQL Server bcp utility to export to a text file (MyStuff.bcp) which I then import into Oracle via SQL Loader. But SQL Loader fails with this message: "Physical record in data file (MyStuff.bcp) is longer than the maximum(1048576)" and 0 records get imported.

I'm not sure what the exact problem is. The most obvious possibility is that MyText is longer than the maximum 1048576, and indeed some records are, but not all of them, so why didn't at least some records get imported? And is there a way around this limit?

The other possibility is something that I've read in some discussion boards, that there are line breaks in MyText that Oracle can't handle. I was hoping I had already worked around this problem by (1) removing \r and \n while exporting from SQL, and (2) using \n in the record delimiter in the SQL Loader control file (see below for the full file). Am I doing something incorrect, or not handling all cases?

Here is the text of my SQL Loader control file. Please note that I'm using \ as the delimiter, which seems odd but it's the only character not used in the MyText field. Hence you'll see \\ where I mean \ because I'm escaping it.
LOAD DATA
INFILE "MyStuff.bcp" "str '\\\n'"
APPEND INTO TABLE MyStuffImported
(
MyID INTEGER EXTERNAL TERMINATED BY "\\",
MyText CHAR TERMINATED BY "\\"
)

Any suggestions? Perhaps there's a better way to import into NCLOB via SQL Loader?

Answer 1

I bet you are not committing after every record so once you get the error and SQL Loader fails it is rollingback your previous inserts.

Answer 2

I was able to fix the problem by doing the following:

1) setting the READSIZE and BINDSIZE params on sqlldr to change the max limit of 1048576 (to get around the "Physical record in data file ... is longer than the maximum" error)
2) in my control file, setting a specific length on CHAR (to get around the "Field in data file exceeds maximum length" error). See below:

LOAD DATA
INFILE "MyStuff.bcp" "str '\\\n'"
APPEND INTO TABLE MyStuffImported
(
MyID INTEGER EXTERNAL TERMINATED BY "\\",
MyText CHAR(10000000) TERMINATED BY "\\"
)