3

I've been trying to map a clob field using Fluent NHibernate 1.2.0.712 against Oracle 10g. I'm using System.Data provider as it's available by default and was trying to avoid adding reference to ODP.Net due to previous client issues.

However, when I try to insert entities with mapped clob properties, I get the error:

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

I've tried to fix this by using the below convention, and decorating the appropriate property with [StringLength(4000)]:

public class StringLengthConvention : AttributePropertyConvention<StringLengthAttribute>
{
    protected override void Apply(StringLengthAttribute attribute, IPropertyInstance instance)
    {
        instance.Length(attribute.MaximumLength);
    }
}

This didn't work.

Then I tried the below using "TEXT", "CLOB" and "clob" values. Neither worked:

    public class plaparteMappingOverride : IAutoMappingOverride<plaparte>
    {
        public void Override(AutoMapping<plaparte> mapping)
        {
           Map(x => x.disposiciones).CustomSqlTypeIs("TEXT");
        } 
    }

Does anyone have further suggestions for this fix other than adding ODP as the provider?

Adam Lear
  • 38,111
  • 12
  • 81
  • 101
Paul Williams
  • 386
  • 7
  • 20
  • I don't the Microsoft provider, but with ODP.NET you don't need anything special for CLOBs, they are just mapped as normal strings (with one exception, it tries to insert empty strings as null instead of empty clobs). Is your `plaparte.disposiciones` property a string? – cremor Nov 11 '11 at 08:50

1 Answers1

0

For future reference: this post perfectly describes what causes this error and how you can solve it.

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.

Solution offered in the blog post: a custom NHibernate Oracle Driver:

/// <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;
    }
}
Lodewijk
  • 2,380
  • 4
  • 25
  • 40
  • Whilst this may theoretically answer the question, [it would be preferable](http://meta.stackexchange.com/q/8259) to include the essential parts of the answer here, and provide the link for reference. – Aaronaught Dec 01 '13 at 19:27