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

“ORA-01461: can bind a LONG value only for insert into a LONG column” happen when try to insert large text (> 4000 Unicode characters) using NHibernate and Oracle.

Following are steps work around to fix the problem:

1. Using Clob or NClob data type

Normally we use VARCHAR2 or NVARCHAR2 for text. But the max length of these data type is 4000. So If we want to store large text which greater than 4000 characters, we must change the data type to CLOB or NCLOB (for Unicode)

2. Using correct connection.driver_class: NHibernate.Driver.OracleDataClientDriver

We were using the NHibernate.Driver.OracleClientDriver which default to Microsoft's Oracle provider (System.Data.OracleClient). We must change the driver_class property to NHibernate.Driver.OracleDataClientDriver

//c.SetProperty(NHibernate.Cfg.Environment.ConnectionDriver, "NHibernate.Driver.OracleClientDriver");

c.SetProperty(NHibernate.Cfg.Environment.ConnectionDriver, "NHibernate.Driver.OracleDataClientDriver");             

 

Or in config file

<property name="connection.driver_class">

        NHibernate.Driver.OracleDataClientDriver

</property>

 

3. Using correct Mapping attributes: type=”AnsiString”

Normally we can use type=”String” default for CLOB/NCLOB. Try to use type=”AnsiString” if two steps above not work.

<property name="SoNhaDuongPho" column="SO_NHA_DUONG_PHO" type="AnsiString"/>

With three steps above, I solved the problem. Hope this help!

Here are some helpful links

http://msarchitectureadventures.blogspot.com/2009/10/problem-inserting-large-characters.html

http://rextang.net/blogs/past/archive/2005/11/16/2905.aspx

http://nhjira.koah.net/browse/NH-465