调用的问题从C＃大CLOB存储过程 [英] Issues calling stored procedure from C# with large CLOB

问题描述

我不是第一个拥有了这些问题，并列出以下一些参考的职位，但我还在寻找一个妥善的解决办法。

I'm not the first to have these issues, and will list some reference posts below, but am still looking for a proper solution.

我要打电话从C＃Web服务存储过程（Oracle 10g数据库）。该网站的服务器的Oracle 9i客户机安装和我使用微软的 System.Data.OracleClient的。

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.

程序将XML作为CLOB。当在 XML超过4000字节（这是很可能在正常使用的情况下），我偶然发现了以下错误：

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:

ORA-01460 - 未实现的要求或不合理的转换

我发现的这个，的这个并的这个帖子。

I've found this, this and this post.

另外我发现了一个有前途的解决方法不直接从C＃调用存储过程，但定义一张匿名的PL / SQL代码来代替。此代码运行作为的OracleCommand。 XML是文字嵌入一个字符串，程序呼叫从这段代码中完成的：

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:

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();

不幸的是，的这个的办法，现在一旦失败的 XML超过32千字节或左右，这还是我的应用程序很可能。这一次的错误从PL / SQL编译器源于它说：

Unfortunately, this approach now fails as soon as the XML is over 32 KBytes or so, which still is very likely in my application. This time the error stems from the PL/SQL compiler which says:

ORA-06550：行1，列87： PLS- 00172：字符串太长

ORA-06550: line1, column 87: PLS-00172: string literal too long

经过一番研究，我得出结论，这是不可行的我的第二个要解决的问题的方法。

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.

• 的切换到ODP.NET （因为它被认为是微软弃用DB客户端错误）


• 的将CLOB到一个表，并从那里

• Switch to ODP.NET (because it is supposed to be a bug in Microsoft's deprecated DB client)
• Insert the CLOB into a table and make the stored proc read from there

的（第一个帖子说，有些客户是越野车，但我（9i中）不会在10G /11克版本的上述范围下降。 ）的

(The first post said some clients are buggy, but mine (9i) does not fall in the mentioned range of 10g/11g versions.)

你能否证实这些留下的只有两个选择？还是有另一种方式来帮助我

只是为了澄清：该XML的不会的最终中任何保存表，但它是由插入基于XML内容的一些表中的一些记录存储过程处理

Just to clarify: the XML won't eventually 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.

我有关这两个选项的考虑：

My considerations about the two options:

• 切换到ODP.NET是困难的，因为我必须在我没有系统的访问，到目前为止在Web服务器上安装它，因为我们可能同时要部署的一块上的客户端代码，所以每个客户端必须安装ODP.NET作为部署的一部分。


• 在一个表中的弯路，使客户端代码颇有几分更复杂，也需要对数据库适配大量的努力/扩展的PL / SQL例程。

推荐答案

我发现有是的另一种方式来解决问题！我的同胞的员工救了我的一天指着我的这个博客，它说：

I found that there is another way to work around the problem! My fellow employee saved my day pointing me to this blog, which says:

设置参数值时，
的BeginTransaction已是
呼吁的DbConnection。

Set the parameter value when BeginTransaction has already been called on the DbConnection.

这可能是更简单呢？该博客涉及到 Oracle.DataAccess ，但它的工作原理也同样适用于 System.Data.OracleClient的。

Could it be simpler? The blog relates to Oracle.DataAccess, but it works just as well for System.Data.OracleClient.

在实践中，这意味着：

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();
}

这篇关于调用的问题从C＃大CLOB存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！