oracle 如何使用 OracleClientFactory 在 XmlType 中插入超过 4000 个字符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6142055/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I insert more than 4000 characters into XmlType using OracleClientFactory?
提问by Hyman B Nimble
I am trying to insert a chunk of Xml that is larger than 4000 characters into an XmlType field in an Oracle table.
我正在尝试将大于 4000 个字符的 Xml 块插入 Oracle 表的 XmlType 字段中。
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:
但是，当我开始尝试插入大于 4000 字节的 Xml 块时，我得到了：
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).
这是与这个问题2508987/insert-xml-with-more-than-4000-characters-into-a-oracle-xmltype-column 相同的问题，但是我没有 DbType.Clob 作为选项（它没有存在）。
Next I tried changing the type to DbType.Object, hoping it would just convert it to whatever it needed, but I get this message:
接下来，我尝试将类型更改为 DbType.Object，希望它能将其转换为所需的任何内容，但我收到以下消息：
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:
然后我尝试使用 DbType.XML，我修改了我的代码以将 messageToLog 移动到 SqlXml 对象中：
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.
确实，我只想在我的专栏中存储更大的 XML 块。
回答by Adam Hawkes
You should the the Oracle Docsfor the ODP.NET library, specifically for setting XMLType information. The OracleXmlTypeclass and its uses are also describedseparately. I'm not really a C# developer myself, so I can't give you exact code. This is what I foundsearching around, but I haven't tried it myself:
您应该查看 ODP.NET 库的Oracle Docs，专门用于设置 XMLType 信息。的OracleXmlType类及其用途也描述分开。我自己并不是真正的 C# 开发人员，所以我不能给你确切的代码。这是我在四处搜索时发现的，但我自己还没有尝试过：
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();
回答by Jim Hansen
bind it like this: .OracleDbType = OracleDbType.XmlType;
像这样绑定它： .OracleDbType = OracleDbType.XmlType;
.DBType = DbType.Xml doesn't work and will throw a runtime error because the enumerated value is out of range.
.DBType = DbType.Xml 不起作用，并且会抛出运行时错误，因为枚举值超出范围。
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();
}
