corruption updating a NCLOB field with ODP.Net

[Andy Fish]

hi,

I am trying to insert/update an NCLOB field using ODP.Net. it seems to
execute OK but the data gets corrupted if I put in more than a couple of Kb
into the field.

here is a complete working example:


create table foo (id int, text1 nclob)


using Oracle.DataAccess.Client;
string connectString = "user id=xxx; password=xxx; data source=xxx";
string commandString = "insert into foo (id, text1) values (2, :1)";
IDbConnection conn = GetConnection(connectString);
IDbCommand cmd = GetCommand(commandString, conn, null);
conn.Open();
IDbDataParameter param = cmd.CreateParameter();
param.ParameterName = ":1";
param.DbType = DbType.String;
param.Direction = ParameterDirection.Input;
StringBuilder sb = new StringBuilder();
for (int i = 0; i < 100; i++)
{
sb.Append(i).Append("this is some text").Append(i).Append(";");
}
param.Value = sb.ToString() ;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();


If I change the count from 100 to 10 it works OK (no corruption). also an
identical test with the microsoft Oracle provider works without problems.

can anyone tell me what I'm doing wrong?

Many thanks

Andy

 

[Frans Bouma [C# MVP]]

hi,

I am trying to insert/update an NCLOB field using ODP.Net. it seems
to execute OK but the data gets corrupted if I put in more than a
couple of Kb into the field.

here is a complete working example:


create table foo (id int, text1 nclob)


using Oracle.DataAccess.Client;
string connectString = "user id=xxx; password=xxx; data source=xxx";
string commandString = "insert into foo (id, text1) values (2, :1)";
IDbConnection conn = GetConnection(connectString);
IDbCommand cmd = GetCommand(commandString, conn, null);
conn.Open();
IDbDataParameter param = cmd.CreateParameter();
param.ParameterName = ":1";
param.DbType = DbType.String;
param.Direction = ParameterDirection.Input;
StringBuilder sb = new StringBuilder();
for (int i = 0; i < 100; i++)
{
sb.Append(i).Append("this is some text").Append(i).Append(";");
}
param.Value = sb.ToString() ;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();


If I change the count from 100 to 10 it works OK (no corruption).
also an identical test with the microsoft Oracle provider works
without problems.

can anyone tell me what I'm doing wrong?

Many thanks

Andy

Don't set parameter.DbType, but set the OracleDbType . Also set the
length to 2GB.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Andy Fish]

Don't set parameter.DbType, but set the OracleDbType . Also set the
length to 2GB.

FB

Thanks Frans

I didn't see a length parameter, but setting the OracleDbType did the trick