Databases
StackOverflow2013
Postsdbo
POFailed to import LOB into Oracle using SQL Loader
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POFailed to import LOB into Oracle using SQL Loader
    primarykey
    data
    text
    <p>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).</p> <p><code>SQL Table -- CREATE TABLE MyStuff (MyID int, MyText nvarchar(max))<br> Oracle Table -- CREATE TABLE MyStuffImported (MyID int, MyText NCLOB)</code></p> <p>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: <b>"Physical record in data file (MyStuff.bcp) is longer than the maximum(1048576)"</b> and 0 records get imported.</p> <p>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?</p> <p>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?</p> <p>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 <code>\\</code> where I mean \ because I'm escaping it. </p> <pre><code>LOAD DATA INFILE "MyStuff.bcp" "str '\\\n'" APPEND INTO TABLE MyStuffImported ( MyID INTEGER EXTERNAL TERMINATED BY "\\", MyText CHAR TERMINATED BY "\\" ) </code></pre> <p>Any suggestions? Perhaps there's a better way to import into NCLOB via SQL Loader?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USKeith
    1. USKeith
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COI found that setting sqlldr's READSIZE and BINDSIZE params I could change that max limit of 1048576, but now I get a different problem. This time the log file shows "Record 1: Rejected - Error on table MYSTUFFIMPORTED, column MYTEXT. Field in data file exceeds maximum length". MYTEXT is a nclob, and the length of the text in Record 1 is 32kb, well below my new limits. I even set ROWS=1 but no luck...
      singulars
      1. POFailed to import LOB into Oracle using SQL Loader
      1. USKeith
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload