-
December 16th, 2011, 07:50 AM
#1
Writing to table with xmltype column issue on Oracle
Hi Everyone, my C# code writes to a column in a table in Oracle database. When the contents are more than 4000 characters, it throws an error saying
ORA-01461: can bind a LONG value only for insert into a LONG column.
Less than that, it works. I bind the column as ntext type because I am using System.Data.OracleClient to communicate with Oracle db and it does not recognize xmltype column but does recognize ntext.
I also have C++ code that writes to database using ODBC apis. I have tried the same with it and get the same error.
Has anyone seen or tried this? Any idea about handling xmltype column in C#?
TIA
-
March 21st, 2013, 09:41 AM
#2
Re: Writing to table with xmltype column issue on Oracle
the trick is to bind with xmltype and knowing that setting the <OracleCommand>.DbType = DbType.Xml doesn't work.
Instead set it <OracleCommand>.OracleDbType = OracleDbType.XmlType;
Example using oracle data access components (found at http://www.oracle.com/technetwork/to...ft-086879.html )
public void insertSimRun(OracleConnection conn, SliceDataExchange.ServiceReference1.SimulationRun simRun)
{
string sqlInsert = "INSERT INTO slice_sim (runid, first_int_start, simulation_run) ";
sqlInsert += "values (_runid, _interval_start, _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();
}
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|