Thursday, July 30, 2009

NHibernate, Oracle & Clobs

If you are starting to work with NHibernate and Oracle you will definitely go through a lengthy stressful process of denile, understanding and acceptance.

So here I will share a bit of knowledge that will make that process a bit less stressful.

If you are working with clobs/nclobs and oracle you may or may not have encountered the following error when using the microsoft provided system.data.oracleclient

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

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

Unfortunately NHibernate 2.0's default behavior is to do exactly the above, making it quite more likely to run into this ugly bug when using nhibernate and oracle.

It is also unfortunate that the issue has indeed been raised with the NHibernate community but has been closed as external. http://nhjira.koah.net/browse/NH-465

I pretty much figure that since Microsoft has decided to pull their driver from the .NET framework in the future, they wont fix this external issue, so it becomes the burden of the developer to work around this ugly bug. So without much further ado here is the way I've gotten around fixing this issue.

I created a custom driver and inherited from the Oracle client driver where I override as follows.

/// <summary>
///
Initializes the parameter.
/// </summary>
///
<param name="dbParam">The db param.
///
<param name="name">The name.
///
<param name="sqlType">Type of the SQL.
protected override void InitializeParameter(System.Data.IDbDataParameter dbParam, string name, global::NHibernate.SqlTypes.SqlType sqlType)
{
base.InitializeParameter(dbParam, name, sqlType);

//System.Data.OracleClient.dll driver generates an exception
//we set the IDbDataParameter.Value = (string whose length: 4000 > length > 2000 )
//when we set the IDbDataParameter.DbType = DbType.String
//when DB Column is of type NCLOB/CLOB
//The Above is the default behavior for NHibernate.OracleClientDriver
//So we use the built-in StringClobSqlType to tell the driver to use the NClob Oracle type
//This will work for both NCLOB/CLOBs without issues.
//Mapping file will need to be update to use StringClob as the property type
if ((sqlType is StringClobSqlType))
{
((OracleParameter)dbParam).OracleType = OracleType.NClob;
}
}


I will try reopening this issue with the NHibernate commiters and see if it now warrants an update.

5 comments:

Ibsta said...

Ye this is a pretty surprising. I got this one too. A way i found round this was to simply use Oracle's client driver.

I used
NHibernate.Driver.OracleDataClientDriver instead of
NHibernate.Driver.OracleClientDriver

I also needed the following to ensure that Nhibernate found the driver:









you will need to check the details of your driver in your GAC (ie you might be using a different version number).

hope this helps someone

cheers

Rene Felix said...

I will eat a lunch free because of you.
Thank's

Kevin Gosse said...

2013, and still the same issue.

In my case, since it's a seldomly used field, I just settled for padding the value to get it above 4000 chars:

if (entity.Content.Length >= 2000 && entity.Content.Length <= 4000)
{
entity.Content = entity.Content.PadRight(4001, ' ');
}

Dirimo Quintero said...

Hi guys, i'm having the same problem whith a CLOB field in a Oracle database, could you help me?. I dont understand what i have to do... Thank you

Greg Bonney said...

OMG. I wasted so much time on a workaround for this problem before I saw your post. Switching to NHibernate.Driver.OracleDataClientDriver worked for me. Had to make sure that Oracle.DataAccess was referenced in my project and any other project in the solution referencing my project with Copy Local = True in the properties. Then it finally worked.