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

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.Inp开发者_运维问答ut, 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.

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

bind it like this: .OracleDbType = OracleDbType.XmlType;

.DBType = DbType.Xml doesn't work and will throw a runtime error because the enumerated value is out of range.

see below:

    public void insertSimRun(OracleConnection conn, SliceDataExchange.ServiceReference1.SimulationRun simRun)
    {
        string sqlInsert = "INSERT INTO slice_sim (runid, first_int_start, simulation_run) ";
        sqlInsert += "values (:p_runid, :p_interval_start, :p_simxml)";

        OracleCommand cmdInsertSR = new OracleCommand();
        cmdInsertSR.CommandText = sqlInsert;
        cmdInsertSR.Connection = conn;

        OracleParameter runID = new OracleParameter();
        runID.DbType = DbType.Int32;
        runID.Value = simRun.RunId;
        runID.ParameterName = "p_runid";

        OracleParameter first_interval_start = new OracleParameter();
        first_interval_start.DbType = DbType.DateTime;
        first_interval_start.Value = simRun.FirstIntervalStartUtc;
        first_interval_start.ParameterName = "p_interval_start";

        var serializer = new XmlSerializer(typeof(SliceDataExchange.ServiceReference1.SimulationRun));
        StringWriter writer = new StringWriter();
        //System.Xml.XmlDocument xdoc = new System.Xml.XmlDocument();

        serializer.Serialize(writer,simRun);

        //xdoc.LoadXml(writer.ToString());

        OracleParameter simRunXML = new OracleParameter();
        simRunXML.DbType = DbType.String;
        simRunXML.ParameterName = "p_simxml";
        simRunXML.Value = writer.ToString();
        simRunXML.OracleDbType = OracleDbType.XmlType;


        cmdInsertSR.Parameters.Add(runID);
        cmdInsertSR.Parameters.Add(first_interval_start);
        cmdInsertSR.Parameters.Add(simRunXML);

        cmdInsertSR.ExecuteNonQuery();

        cmdInsertSR.Dispose();
    }