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

Question

Environment:

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

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

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

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.

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)

C# Code for invoking stored procedure:

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:

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.

Thanks in advance.

Answer 1

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

C# Code

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