无法使用 SQL Loader 将 LOB 导入 Oracle

发布于 2024-08-06 14:33:30 字数 1037 浏览 7 评论 0原文

我正在尝试将一个表从 SQL Server 导出到 Oracle，该表包含一个 ID 和一个存储为 nvarchar(max) 的非常长的 XML 字符串。

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

我正在使用 SQL Server bcp 实用程序导出到一个文本文件 (MyStuff.bcp)，然后通过 SQL Loader 将其导入到 Oracle 中。但 SQL Loader 失败并显示以下消息：“数据文件 (MyStuff.bcp) 中的物理记录长于最大值 (1048576)” 并且导入了 0 条记录。

我不确定确切的问题是什么。最明显的可能性是 MyText 比最大值 1048576 长，并且确实有一些记录，但不是全部，那么为什么至少没有导入一些记录呢？有没有办法绕过这个限制？

另一种可能性是我在一些讨论板上读到的，MyText 中存在 Oracle 无法处理的换行符。我希望我已经解决了这个问题，方法是（1）在从 SQL 导出时删除 \r 和 \n，以及（2）在 SQL Loader 控制文件的记录定界符中使用 \n（完整文件请参见下文）。我是否做错了什么，或者没有处理所有情况？

这是我的 SQL Loader 控制文件的文本。请注意，我使用 \ 作为分隔符，这看起来很奇怪，但它是 MyText 字段中唯一未使用的字符。因此你会看到 \\ 我的意思是 \ 因为我正在转义它。

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

有什么建议吗？也许有更好的方法通过 SQL Loader 导入 NCLOB？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

坏尐絯℡ 2024-08-13 14:33:30

我能够通过执行以下操作来解决该问题：

1）在 sqlldr 上设置 READSIZE 和 BINDSIZE 参数以更改最大限制 1048576 （以解决“数据文件中的物理记录...比最大值长”错误)
2）在我的控制文件中，设置 CHAR 的特定长度（以解决“数据文件中的字段超出最大长度”错误）。见下文：

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

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 "\\"  
)

回复收藏 0 原文