“ORA-01461: can bind a LONG value only for insert into a LONG column” when set parameter in NHibernate

3
votes

I have search this problem on internet, but my issue is different. I'm using Fluent NHibernate and try insert data with sql query:

var query = "INSERT INTO TABLE_NAME('ID','CONTENT') VALUES(:ID, :CONTENT)";
var executedQuery = Session.CreateSQLQuery(query);

executedQuery.SetParameter("ID", data.Id);
executedQuery.SetParameter("CONTENT", data.Content);
executedQuery.ExecuteUpdate();

Here data passing to method. In database(Oracle 11g) datatype of CONTENT is NCLOB. When try to insert data, I get this error:

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

What is problem in here?

1
c#oraclenhibernateoracle11gfluent-nhibernate
What type is data.Content? – M. Wiśnicki
@M.Wiśnicki, data.Content is string, example: "<p>Some text</p>". if string length is bigger than 2000 this error occurred, in case <2000 insert work. – vaqifrv
Which version ODAC you using? – M. Wiśnicki
stackoverflow.com/questions/8083129/… – Valeh Mikayilzadeh

1 Answers

4
votes

This error is not very helpful and goggling it will most likely result in topics regarding oracle patches and the like. In reality this is a bug with the microsoft oracle client driver. The driver mistakenly infers the column type of the string being saved, and tries forcing the server to update a LONG value into a CLOB/NCLOB column type. The reason for the incorrect behavior is even more obscure and only happens when all the following conditions are met:

  1. when we set the IDbDataParameter.Value = (string whose length is : 4000 > length > 2000 )
  2. when we set the IDbDataParameter.DbType = DbType.String
  3. when DB Column is of type NCLOB/CLOB

In this situation you must set database column type in set parameter method overload, so:

executedQuery.SetParameter("CONTENT", data.Content,  NHibernateUtil.StringClob);