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

Question

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?

Answer 1

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