Transferring Huge XML from C # /. NET to Oracle Stored Procedure with CLOB Parameter - ORA-01008: Not All Variables Bound

Question

Environment:

Server: Oracle 11.2g Server on 64-bit Windows 2008

Client: Oracle 11g Client on Windows XP Service Pack 3, ASP.Net 4.0, Visual Studio 2010, C #

The size of the input XML file is ~ 1 206 500 characters (calculated based on the maximum data that I would have).

Scenario:

The web application generates XML, which uses the oracle stored procedure to update the table in the database. Since the XML size is quite large, the selected type of stored procedure is CLOB instead of LONG, since LONG has a limit of 32760 characters.

Problem:

Using the CLOB as a parameter type causes the error "ORA-01008: not all related variables" for the same stored procedure code, which is fine for a parameter type like LONG (and XML length <32760)

C # code to call a 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 through the CLOB parameter and converting CLOB to XML into a stored procedure can help? Any alternative approach to this problem would also be greatly appreciated.

Thanks in advance.

Answer 1

Here is the C # code that fixed the problem. I was unable 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();