使用大型 CLOB 从 C# 调用存储过程的问题 [英] 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 数据库).Web 服务器安装了 Oracle 9i 客户端，我使用的是 Microsoft 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 - 请求未实施或不合理的转换

我发现 this，this 和 这篇 博文.

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 KB 左右，这种方法现在就会失败，这在我的应用程序中仍然很可能发生.这次错误源于 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(因为它应该是 Microsoft 已弃用的数据库客户端中的错误)
• 将 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/11g 版本的范围内.)

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

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