How do I insert more than 4000 characters into XmlType using OracleClientFactory?

Question

I am trying to insert a chunk of Xml that is larger than 4000 characters into an XmlType field in an Oracle table.

Originally my code worked like this:

DbParameter parameter = new DbParameter;
parameter = clientFactory.CreateParameter(":p_xml_data", DbType.AnsiString, messageToLog.Length, ParameterDirection.Input, messageToLog);

However as soon as I started trying to insert Xml blocks larger than 4000 bytes I got:

ORA-01461: can bind a LONG value only for insert into a LONG column

This is the same issue as this question 2508987/insert-xml-with-more-than-4000-characters-into-a-oracle-xmltype-column, however I do not have DbType.Clob as an option (it doesn't exist).

Next I tried changing the type to DbType.Object, hoping it would just convert it to whatever it needed, but I get this message:

Cannot bind type System.String as Blob

Then I tried using DbType.XML, I modified my code to move the messageToLog into a SqlXml object:

SqlXml sx;
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(messageToLog);
using (XmlNodeReader xnr = new XmlNodeReader(xmlDoc))
{
    sx = new SqlXml(xnr);
}

And changed the parameter accordingly:

parameter = providerFactory.CreateParameter(":p_xml_data", DbType.Xml, messageToLog.Length, ParameterDirection.Input, sx);

Now I get:

Value is not valid for DbType: Xml

Truely I just want to store larger blocks of XML in my column.

Answer 1

You should the the Oracle Docs for the ODP.NET library, specifically for setting XMLType information. The OracleXmlType class and its uses are also described separately. I'm not really a C# developer myself, so I can't give you exact code. This is what I found searching around, but I haven't tried it myself:

OracleCommand orainscmd = new OracleCommand("INSERT INTO employees(empinfo) " +
" VALUES (:empinfoxml)", con);
orainscmd.Parameters.Add(":empinfoxml", OracleDbType.XmlType);
OracleXmlType xmlinfo = new OracleXmlType(con,doc);
orainscmd.Parameters[0].Value=xmlinfo;
orainscmd.ExecuteNonQuery();