Insert Into Oracle XMLTYPE column with ExecuteStoreCommand()

Question

I have a table like

Id (int) | xml (xmltype)

I want to insert to this table as:

string Sql = "insert into MyTable (xml) values (xmltype(:0))";
myxml = "<root>very long xml...</root>";
MyEntity.ExecuteStoreCommand(Sql, myxml);

If the xml is very long (more than 4000 chars) this returns the following error:

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

I have searched the internet, and found that "myxml" should be "clob" but entity framework sends it as "long".

How can we solve this issue?

isa isa · Accepted Answer · 2014-12-12T12:25:29

After lots of searching and working and trying, I have came up with the following solution:

You cannot use ExecuteStoreCommand() method in order to insert a string which is longer than 4K.

In order to add string more than 4K, you have to bind parameters.

In order to bind parameters in .net, we will utilize odp.net.

Step One

Add the reference to your project.

Right click references on .net project explorer window and click add reference.
On .net tab, find Oracle.DataAccess and add this to project.

Step Two

Add this to your top of your page:

using Oracle.DataAccess.Client;

Step Three

Now we code:

using (OracleConnection con = new OracleConnection("Here comes your oracle connection string."))
{
    con.Open();

    //prepare the command
    OracleCommand cmd = con.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = @"INSERT INTO MyTable VALUES (:param0) ";

    //define the parameter.
    OracleParameter param0 = new OracleParameter();
    param0.OracleDbType = OracleDbType.Clob;
    param0.Value = "very long xml...";
    param0.ParameterName = "param0";
    cmd.Parameters.Add(param0);

    try
    {
        AffectedRows = cmd.ExecuteNonQuery(); //execute the query
    }
    catch (Exception e)
    {
        return 0;
    }
}

Now it should work like a charm :)

Notes

1- Do not use System.Data.OracleClient, it is deprecated.

2- If your table column data type is xmltype and still get an error, change your datatype to clob.

Hope this helps others...

Insert Into Oracle XMLTYPE column with ExecuteStoreCommand()

1 Answers