通过 CLOB 参数将巨大的 XML 从 C#/.Net 传递到 Oracle 存储过程 - ORA-01008：并非所有变量都绑定

Passing huge XML from C#/.Net to Oracle Stored Procedure via CLOB parameter - ORA-01008: not all variables bound

提问by Moiz Tankiwala

Environment:

环境：

Server:Oracle 11.2g server on a 64-bit windows 2008

服务器：64 位 Windows 2008 上的 Oracle 11.2g 服务器

Client:Oracle 11g client on Windows XP SP3, ASP.Net 4.0, Visual Studio 2010, C#

客户端：Windows XP SP3、ASP.Net 4.0、Visual Studio 2010、C# 上的 Oracle 11g 客户端

Input size of XML ~ 1,206,500 characters(Calculated based on the maximum data that I would have).

XML 的输入大小 ~ 1,206,500 个字符（根据我拥有的最大数据计算）。

Scenario:

设想：

The web application generates the XML that the oracle stored procedure uses to update a table in the database. Since the XML size is pretty large, the Stored Procedure Parameter type chosen is CLOB instead of LONG as LONG has a limitation of 32760 characters.

Web 应用程序生成 XML，oracle 存储过程使用该 XML 更新数据库中的表。由于 XML 大小非常大，因此选择的存储过程参数类型是 CLOB 而不是 LONG，因为 LONG 有 32760 个字符的限制。

Problem:

问题：

Using CLOB as the parameter type throws up the error "ORA-01008: not all variables bound" for the same stored procedure code which works perfectly for the parameter type as LONG (and XML length < 32760)

对于相同的存储过程代码，使用 CLOB 作为参数类型会引发错误“ORA-01008：并非所有变量都绑定”，这对于参数类型为 LONG（并且 XML 长度 < 32760）非常有效

C# Code for invoking stored procedure:

调用存储过程的C#代码：

OracleCommand DbUpdateCommand = null;
OracleLob tempLOB = null;

DbUpdateCommand.CommandText = "declare xx clob; begin dbms_lob.createtemporary(xx, false, 0); :tempclob := xx; end;";
DbUpdateCommand.Parameters.Add(new OracleParameter("tempclob", OracleType.Clob)).Direction = ParameterDirection.Output;
DbUpdateCommand.ExecuteNonQuery();

//Assign the value to the LOB
tempLOB = (OracleLob)DbUpdateCommand.Parameters[0].Value;
tempLOB.BeginBatch(OracleLobOpenMode.ReadWrite);

//Convert the string to byte array to write to LOB
UnicodeEncoding encoding = new UnicodeEncoding();
byte[] renewalDetailXMLBytes = encoding.GetBytes(renewalDetailXML);
tempLOB.Write(renewalDetailXMLBytes, 0, renewalDetailXMLBytes.Length);
tempLOB.EndBatch();

DbUpdateCommand.CommandText = "P_WEB_PRDCR_RNEW_UPDT";
DbUpdateCommand.CommandType = System.Data.CommandType.StoredProcedure;
DbUpdateCommand.Parameters.Add("PN_KEY_AGNT_RNEW_HDR", 
System.Data.OracleClient.OracleType.Number, 12).Value = agentRenewalHeader;
DbUpdateCommand.Parameters.Add("PN_KEY_CO", 
System.Data.OracleClient.OracleType.Number, 12).Value = companyCode;
DbUpdateCommand.Parameters.Add("PC_RNWL_DETL_XML", 
    System.Data.OracleClient.OracleType.Clob).Value = tempLOB;
DbUpdateCommand.Parameters.Add("PS_USR_NM",
System.Data.OracleClient.OracleType.VarChar,255).Value = userName;

DbUpdateCommand.ExecuteNonQuery();

Oracle Stored Procedure Code:

Oracle 存储过程代码：

CREATE OR REPLACE PROCEDURE DOIADMIN.P_WEB_PRDCR_RNEW_UPDT (
    PN_KEY_AGNT_RNEW_HDR IN NUMBER,
    PN_KEY_CO            IN NUMBER,
    PC_RNWL_DETL_XML     IN CLOB,
    PS_USR_NM            IN VARCHAR2
)
AS
        lx_rnew_detl_xml    XMLTYPE;
    lct_rnew_detl_cntx  DBMS_XMLSAVE.ctxtype;

    --Construct the complete xml for financial data
    lx_rnew_detl_xml := XMLTYPE(PC_RNWL_DETL_XML);

    --table to be updated with the xml
    lct_rnew_detl_cntx := DBMS_XMLSAVE.newcontext('IL_AGNT_RNEW_DETL');

    --Set the key column list
    DBMS_XMLSAVE.SETKEYCOLUMN(lct_rnew_detl_cntx, 'KEY_AGNT_RNEW_HDR');
    DBMS_XMLSAVE.SETKEYCOLUMN(lct_rnew_detl_cntx, 'KEY_CO');
    DBMS_XMLSAVE.SETKEYCOLUMN(lct_rnew_detl_cntx, 'KEY_INDVDL_LIC');

    --Set the udpate column
    DBMS_XMLSAVE.SETUPDATECOLUMN(lct_rnew_detl_cntx, 'FLG_MARKED_FOR_CANCEL');

    --update the table from the rows
    ln_cntr := DBMS_XMLSAVE.UPDATEXML(lct_rnew_detl_cntx, lx_rnew_detl_xml.getCLOBVal());

    DBMS_XMLSAVE.closecontext(lct_rnew_detl_cntx);
END p_web_prdcr_rnew_updt;

Anyone who has worked with passing large XML via CLOB parameter and converted that CLOB to XML in the stored procedure can please help? Any alternate approach to this problem would also be highly appreciated.

任何曾经通过 CLOB 参数传递大型 XML 并在存储过程中将该 CLOB 转换为 XML 的人都可以帮忙吗？任何解决此问题的替代方法也将受到高度赞赏。

Thanks in advance.

提前致谢。

采纳答案by Moiz Tankiwala

Here is the C# code that fixed the issue. I was missing to clear the parameters before reusing the same command object.

这是解决此问题的 C# 代码。在重用相同的命令对象之前，我没有清除参数。

C# Code

C# 代码

OracleCommand DbUpdateCommand = null;
OracleLob tempLOB = null;

DbUpdateCommand.CommandText = "declare xx clob; begin dbms_lob.createtemporary(xx, false, 0); :tempclob := xx; end;";
DbUpdateCommand.Parameters.Add(new OracleParameter("tempclob", OracleType.Clob)).Direction = ParameterDirection.Output;
DbUpdateCommand.ExecuteNonQuery();

//Assign the value to the LOB
tempLOB = (OracleLob)DbUpdateCommand.Parameters[0].Value;
tempLOB.BeginBatch(OracleLobOpenMode.ReadWrite);

//Convert the string to byte array to write to LOB
UnicodeEncoding encoding = new UnicodeEncoding();
byte[] renewalDetailXMLBytes = encoding.GetBytes(renewalDetailXML);
tempLOB.Write(renewalDetailXMLBytes, 0, renewalDetailXMLBytes.Length);
tempLOB.EndBatch();

DbUpdateCommand.CommandText = "P_WEB_PRDCR_RNEW_UPDT";
DbUpdateCommand.CommandType = System.Data.CommandType.StoredProcedure;

//Missing line - start
DbUpdateCommand.Parameters.Clear();
//Missing line - end

DbUpdateCommand.Parameters.Add("PN_KEY_AGNT_RNEW_HDR", 
    System.Data.OracleClient.OracleType.Number, 12).Value = 
        agentRenewalHeader;
DbUpdateCommand.Parameters.Add("PN_KEY_CO", 
    System.Data.OracleClient.OracleType.Number, 12).Value = 
        companyCode;
DbUpdateCommand.Parameters.Add("PC_RNWL_DETL_XML", 
    System.Data.OracleClient.OracleType.Clob).Value =
        tempLOB;
DbUpdateCommand.Parameters.Add("PS_USR_NM",
    System.Data.OracleClient.OracleType.VarChar,255).Value = 
        userName;

DbUpdateCommand.ExecuteNonQuery();