From C#, How to pass a clob value to oracle stored procedure

Question

My Oracle stored procedure is accepting a clob type parameter.

procedure  p_insert_data(xml_string in clob)

From C#, I am trying to call the procedure by passing clob data, which is an XML file.

Following is the way which I tried:

Converting XML to string

 XmlDocument xmlDoc = new XmlDocument();
 xmlDoc.Load(@"D:\Response_min.xml");

 string xml = xmlDoc.OuterXml;

Passing clob data to stored procedure from C#

OracleParameter p_data = new OracleParameter("p_xml_string", OracleDbType.Clob);
p_data.Direction = ParameterDirection.Input;
p_data.Value = xml; //xml is of string type 
dbCommand.Parameters.Add(p_data);
dbCommand.ExecuteNonQuery();

I am getting exception

operation not valid due to current state of object

Instead of xml string, I tried passing bytes[] to clob parameter but still no luck.

I also tried to use XML which has less content.
Anyone has any clue about how I should pass clob value ?

Possible duplicate of [Issues calling stored procedure from C# with large CLOB](http://stackoverflow.com/questions/3557995/issues-calling-stored-procedure-from-c-sharp-with-large-clob) — Mihai-Daniel Virna, Mar 02 '16 at 08:22
@NullException It's not related to that CLOB size issue, I tried XML with less content also. — subi_speedrunner, Mar 02 '16 at 08:25
Try `OracleParameter("p_xml_string", OracleDbType.XmlType);` — Wernfried Domscheit, Mar 02 '16 at 10:38

score 4 · Accepted Answer · answered Mar 03 '16 at 07:23

I got it working yesterday , Hope it will help some one.

Clob data we need to pass to stored procedure by creating an OracleClob object

OracleClob clob = new OracleClob(connection);
clob.Write(xmlContent.ToArray(), 0, xmlContent.Length);
clob_Param.Value = clob;

where clob_Param is of type OracleParameter.
After creating the OracleClob object, write xml content to it !!!

score 0 · Answer 2 · answered Jul 15 '21 at 14:28

Another way is to pass it as a byte array, the connection needed to be Open.

connection.Open();

byte[] arrayByte = System.Text.Encoding.Unicode.GetBytes(clobValue);
OracleClob oracleClob = new (connection);
oracleClob.Write(arrayByte, 0, arrayByte.Length);

var clobParam = new OracleParameter("param_clob", OracleDbType.Clob, oracleClob, ParameterDirection.Input);

From C#, How to pass a clob value to oracle stored procedure

2 Answers2