CODEWITHSUNDEEP
c#asp.netoracle-databasec#-4.0oracle10g
subi_speedrunner
subi_speedrunner

Reputation: 921

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

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 ?

Upvotes: 3

Views: 16053

Answers (2)

Glenn Andrés P. Rosario
Glenn Andrés P. Rosario

Reputation: 173

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

Upvotes: 0

subi_speedrunner
subi_speedrunner

Reputation: 921

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 !!!

Upvotes: 4

Related Questions