oracle 使用大型 CLOB 从 C# 调用存储过程的问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3557995/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Issues calling stored procedure from C# with large CLOB
提问by chiccodoro
I'm not the first to have these issues, and will list some reference posts below, but am still looking for a proper solution.
我不是第一个遇到这些问题的人，我会在下面列出一些参考帖子，但我仍在寻找合适的解决方案。
I need to call a stored procedure (Oracle 10g database) from a C# web service. The web server has an Oracle 9i client installed and I am using Microsofts System.Data.OracleClient.
我需要从 C# Web 服务调用存储过程（Oracle 10g 数据库）。Web 服务器安装了 Oracle 9i 客户端，我使用的是 Microsofts System.Data.OracleClient。
The procedure takes an XML as a CLOB. When the XML was over 4000 Bytes(which is likely in a normal use case), I stumbled over the following error:
该过程将 XML 作为 CLOB。当XML 超过 4000 字节（这很可能在正常用例中）时，我偶然发现了以下错误：
ORA-01460 - unimplemented or unreasonable conversion requested
ORA-01460 - 请求的转换未实现或不合理
I've found this, thisand thispost.
Further I found a promising workaround which doesn't call the stored procedure directly from C# but defines a piece of anonymous PL/SQL code instead. This code is run as an OracleCommand. The XML is embedded as a string literal and the procedure call is done from within that piece of code:
此外，我发现了一个有前途的解决方法，它不直接从 C# 调用存储过程，而是定义了一段匿名 PL/SQL 代码。此代码作为 OracleCommand 运行。XML 嵌入为字符串文字，过程调用是从该段代码中完成的：
private const string LoadXml =
"DECLARE " +
" MyXML CLOB; " +
" iStatus INTEGER; " +
" sErrMessage VARCHAR2(2000); " +
"BEGIN " +
" MyXML := '{0}'; " +
" iStatus := LoadXML(MyXML, sErrMessage); " +
" DBMS_OUTPUT.ENABLE(buffer_size => NULL); " +
" DBMS_OUTPUT.PUT_LINE(iStatus || ',' || sErrMessage); " +
"END;";
OracleCommand oraCommand = new OracleCommand(
string.Format(LoadXml, xml), oraConnection);
oraCommand.ExecuteNonQuery();
Unfortunately, thisapproach now fails as soon as the XML is over 32 KBytesor so, which still is very likely in my application. This time the error stems from the PL/SQL compiler which says:
不幸的是，一旦XML 超过 32 KB左右，这种方法就会失败，这在我的应用程序中仍然很有可能。这次错误源于 PL/SQL 编译器，它说：
ORA-06550: line1, column 87: PLS-00172: string literal too long
ORA-06550：第 1 行，第 87 列：PLS-00172：字符串文字太长
After some research I conclude that it's simply not feasible to solve the problem with my second approach.
经过一些研究，我得出结论，用我的第二种方法解决问题根本不可行。
Following the above-mentioned posts I have the following two options.
根据上述帖子，我有以下两个选择。
- Switch to ODP.NET(because it is supposed to be a bug in Microsoft's deprecated DB client)
- Insert the CLOB into a tableand make the stored proc read from there
- 切换到 ODP.NET（因为它应该是 Microsoft 已弃用的 DB 客户端中的错误）
- 将 CLOB 插入表中，并从那里读取存储过程
(The first postsaid some clients are buggy, but mine (9i) does not fall in the mentioned range of 10g/11g versions.)
（第一篇文章说一些客户端有问题，但我的 (9i) 不属于上述 10g/11g 版本的范围。）
Can you confirm that these are the only two options left? Or is there another way to help me out?
你能确认这是唯一剩下的两个选项吗？或者有其他方法可以帮助我吗？
Just to clarify: the XML won'teventually be saved in any table, but it is processed by the stored procedure which inserts some records in some table based on the XML contents.
只是为了澄清：该XML不会最终被保存在任何表，但它是由插入基于XML内容的一些表中的一些记录存储过程的处理。
My considerations about the two options:
我对这两种选择的考虑：
- Switching to ODP.NET is difficult because I have to install it on a web server on which I don't have system access so far, and because we might also want to deploy the piece of code on clients, so each client would have to install ODP.NET as part of the deployment.
- The detour over a table makes the client code quite a bit more complicated and also takes quite some effort on the database adapting/extending the PL/SQL routines.
- 切换到 ODP.NET 很困难，因为我必须将它安装在我目前没有系统访问权限的 Web 服务器上，而且因为我们可能还想在客户端上部署这段代码，所以每个客户端都必须安装 ODP.NET 作为部署的一部分。
- 绕过表使客户端代码相当复杂，并且还需要在数据库适应/扩展 PL/SQL 例程上付出相当多的努力。
采纳答案by chiccodoro
I found that there isanother way to work around the problem! My fellow employee saved my day pointing me to this blog, which says:
我发现，是另一种方式来解决这个问题！我的同事挽救了我的一天，将我指向这个博客，它说：
Set the parameter value when BeginTransaction has already been called on the DbConnection.
在 DbConnection 上已调用 BeginTransaction 时设置参数值。
Could it be simpler? The blog relates to Oracle.DataAccess, but it works just as well for System.Data.OracleClient.
能不能简单点？该博客与 相关Oracle.DataAccess，但对于System.Data.OracleClient.
In practice this means:
在实践中，这意味着：
varcmd = new OracleCommand("LoadXML", _oracleConnection);
cmd.CommandType = CommandType.StoredProcedure;
var xmlParam = new OracleParameter("XMLFile", OracleType.Clob);
cmd.Parameters.Add(xmlParam);
// DO NOT assign the parameter value yet in this place
cmd.Transaction = _oracleConnection.BeginTransaction();
try
{
// Assign value here, AFTER starting the TX
xmlParam.Value = xmlWithWayMoreThan4000Characters;
cmd.ExecuteNonQuery();
cmd.Transaction.Commit();
}
catch (OracleException)
{
cmd.Transaction.Rollback();
}
回答by Kemalettin Erbak?rc?
In my case, chiccodoro's solution did not work. I'm using ODP.NET ( Oracle.DataAccess).
就我而言，奇科多罗的解决方案不起作用。我正在使用 ODP.NET ( Oracle.DataAccess)。
For me the solution is using OracleClobobject.
对我来说，解决方案是使用OracleClobobject.
OracleCommand cmd = new OracleCommand("LoadXML", _oracleConnection);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter xmlParam = new OracleParameter("XMLFile", OracleType.Clob);
cmd.Parameters.Add(xmlParam);
//connection should be open!
OracleClob clob = new OracleClob(_oracleConnection);
// xmlData: a string with way more than 4000 chars
clob.Write(xmlData.ToArray(),0,xmlData.Length);
xmlParam.Value = clob;
try
{
cmd.ExecuteNonQuery();
}
catch (OracleException e)
{
}
回答by Vincent
chiccodoro is right.
奇科多罗是对的。
public static int RunProcedure(string storedProcName, IDataParameter[] parameters)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
int rowsAffected;
OracleCommand command = new OracleCommand(storedProcName, connection);
command.CommandText = storedProcName;
command.CommandType = CommandType.StoredProcedure;
foreach (OracleParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
connection.Open();
try
{
// start transaction
command.Transaction = connection.BeginTransaction();
rowsAffected = command.ExecuteNonQuery();
command.Transaction.Commit();
}
catch (System.Exception ex)
{
command.Transaction.Rollback();
throw ex;
}
connection.Close();
return rowsAffected;
}
}
回答by x0n
I guess I just googled this for you to get cheap points, but there's a great explanation here:
我想我只是在谷歌上搜索了这个以获得便宜的积分，但这里有一个很好的解释：
http://www.orafaq.com/forum/t/48485/0/
http://www.orafaq.com/forum/t/48485/0/
Basically you cannot use more than 4000 chars in a string literal, and if you need to do more, you must use a stored procedure. Then, you are limited to 32KB at max so you have to "chunk" the inserts. Blech.
基本上你不能在一个字符串文字中使用超过 4000 个字符，如果你需要做更多，你必须使用存储过程。然后，最大限制为 32KB，因此您必须“分块”插入。布莱克。
-Oisin
-Oisin
