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

Andy Fish wrote:

> 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#)
------------------------------------------------------------------------

"Frans Bouma [C# MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Andy Fish wrote:
>
>> 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
>

Thanks Frans

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

> --
> ------------------------------------------------------------------------
> 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#)
> ------------------------------------------------------------------------