Writing to table with xmltype column issue on Oracle
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2

Thread: Writing to table with xmltype column issue on Oracle

  1. #1
    Join Date
    Jun 2003
    Location
    INDIA
    Posts
    586

    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

  2. #2
    Join Date
    Mar 2013
    Posts
    1

    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
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center