Avoid InvalidCastException with Oracle number high precision values

[kriewall]

Describe the issue

I'm getting an InvalidCastException when rehydrating numbers with very small / high precision values in an Oracle dB. The limitation appears to actually be due to the fact that Oracle can represent numbers with higher precision than C# (described here); however my main question here is how to prevent NHibernate from inserting values into the dB that have higher precision than C# can handle. If you have recommendations how to work around the intake issue, that would be much appreciated as well.

Expected behavior

• By default, C# precision limits should be respected when inserting doubles into the dB.
• Ideally intake of high precision values would be handled gracefully; however, I recognize you may not have control over this since the issue occurs in the Oracle reader.

As a workaround to prevent overly precise data from getting inserted into the dB, do I need to specify precision and scale everywhere I map a double? Is there a generic way to do this?

Environment

• Windows 10 and Windows 2016 server (observed on both)
• NHibernate
  • Problematic data inserted into production dB under NHibernate 5.2.7
  • InvalidCastException occurs on both 5.2.7 and 5.3.5.
• Oracle dB: 11.2.0.2
• Oracle.ManagedDataAccess: 18.3, but have also tried with 19.10

Additional information

Stack trace:

 at Oracle.ManagedDataAccess.Client.OracleDataReader.GetDecimal(Int32 i)
       at Oracle.ManagedDataAccess.Client.OracleDataReader.GetValue(Int32 i)
       at Oracle.ManagedDataAccess.Client.OracleDataReader.get_Item(Int32 i)
       at NHibernate.Type.NullableType.NullSafeGet(DbDataReader rs, String name, ISessionImplementor session)
       at NHibernate.Type.NullableType.NullSafeGet(DbDataReader rs, String[] names, ISessionImplementor session, Object owner)
       at NHibernate.Type.AbstractType.Hydrate(DbDataReader rs, Object owner)
       at NHibernate.Persister.Entity.AbstractEntityPersister.Hydrate(DbDataReader rs, Object id, Object obj, String[][] suffixedPropertyColumns, ISet`1 fetchedLazyProperties, Boolean allProperties, Int32[] indexes, ISessionImplementor session)
       at NHibernate.Persister.Entity.LoadableExtensions.Hydrate(ILoadable loadable, DbDataReader rs, ISessionImplementor session)
       at NHibernate.Loader.Loader.LoadFromResultSet(DbDataReader rs, Int32 i, ILoadable persister, EntityKey key, LockMode lockMode, ILoadable rootPersister, ISessionImplementor session)
       at NHibernate.Loader.Loader.InstanceNotYetLoaded(DbDataReader dr, EntityKey optionalObjectKey, Object optionalObject, IList hydratedObjects, ISessionImplementor session)
       at NHibernate.Loader.Loader.GetRow(DbDataReader rs, ILoadable[] persisters, EntityKey[] keys, LockMode[] lockModes, Boolean mustLoadMissingEntity, Action`2 cacheBatchingHandler)
       at NHibernate.Loader.Loader.GetRowFromResultSet(DbDataReader resultSet, QueryParameters queryParameters, LockMode[] lockModeArray, Boolean returnProxies, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder, Action`2 cacheBatchingHandler)
       at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryCacheResultBuilder queryCacheResultBuilder)
       at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryCacheResultBuilder queryCacheResultBuilder)
       at NHibernate.Loader.Loader.LoadCollectionBatch(ISessionImplementor session, IType type)
at NHibernate.Loader.Loader.LoadCollectionBatch(ISessionImplementor session, Object[] ids, IType type)
       at NHibernate.Loader.Collection.BatchingCollectionInitializer.Initialize(Object id, ISessionImplementor session)
       at NHibernate.Persister.Collection.AbstractCollectionPersister.Initialize(Object key, ISessionImplementor session)
       at NHibernate.Event.Default.DefaultInitializeCollectionEventListener.OnInitializeCollection(InitializeCollectionEvent event)
       at NHibernate.Impl.SessionImpl.InitializeCollection(IPersistentCollection collection, Boolean writing)
       at NHibernate.Collection.AbstractPersistentCollection.Initialize(Boolean writing)
       at NHibernate.Collection.AbstractPersistentCollection.Read()
       at NHibernate.Collection.Generic.PersistentGenericBag`1.GetEnumerator()
       at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
       at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
       at MathNet.Numerics.Statistics.DescriptiveStatistics.ComputeDecimal(IEnumerable`1 data)
       at MathNet.Numerics.Statistics.DescriptiveStatistics..ctor(IEnumerable`1 data, Boolean increasedAccuracy)
       at Locnes.Core.Domain.State.Smoothing.LotHistoryData.GetFinalCheckStdev() in C:\src\locnes\Locnes.Core\Domain\State\Smoothing\LotHistoryData.cs:line 98
       at Locnes.Core.Domain.State.Smoothing.VdewmaStateEngine.<>c__DisplayClass35_0.<AdvanceState>b__0(LotHistoryData d) in C:\src\locnes\Locnes.Core\Domain\State\Smoothing\VdewmaStateEngine.cs:line 78
       at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
       at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
       at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
       at Locnes.Core.Domain.State.Smoothing.VdewmaStateEngine.AdvanceState(EngineData history, IDataStrategyService service) in C:\src\locnes\Locnes.Core\Domain\State\Smoothing\VdewmaStateEngine.cs:line 78
       at Locnes.Core.Domain.State.Smoothing.TermEngine.DoGetAverage() in C:\src\locnes\Locnes.Core\Domain\State\Smoothing\TermEngine.cs:line 132
       at Locnes.Core.Domain.State.Smoothing.TermEngine.<>c__DisplayClass35_0.<GetAverage>b__0() in C:\src\locnes\Locnes.Core\Domain\State\Smoothing\TermEngine.cs:line 118
       at CSharpFunctionalExtensions.Result.Try[T](Func`1 func, Func`2 errorHandler)

SessionFactory config

                var dbConfig = OracleDataClientConfiguration.Oracle10
                    .ConnectionString(c => c.Is(connectionString))
                    .Driver<NHibernate.Driver.OracleManagedDataClientDriver>();
                //.ShowSql();
                dbConfig = ApplicationGlobals.DefaultSchema.Equals(ApplicationGlobals.WindowsUser) ? dbConfig : dbConfig.DefaultSchema(ApplicationGlobals.DefaultSchema);

                FluentConfiguration configuration = Fluently.Configure()
                    .Database(dbConfig)
                    .Mappings(m =>
                    {
                        m.FluentMappings.AddFromAssembly(targetAssembly).AddConventions() /*.ExportTo(@"c:\temp\mappings")*/;
                        //m.FluentMappings.AddFromAssembly(Assembly.GetExecutingAssembly()).AddConventions()/*.ExportTo(@"c:\temp\mappings")*/;
                        //m.HbmMappings.AddFromAssembly(Assembly.GetExecutingAssembly());
                    })
                    .ExposeConfiguration(x =>
                    {
                        x.EventListeners.PostCommitUpdateEventListeners = new IPostUpdateEventListener[] { new EventListener() };
                        x.EventListeners.PostCommitInsertEventListeners = new IPostInsertEventListener[] { new EventListener() };
                        x.EventListeners.PostCommitDeleteEventListeners = new IPostDeleteEventListener[] { new EventListener() };
                        x.EventListeners.PostCollectionUpdateEventListeners = new IPostCollectionUpdateEventListener[] { new EventListener() };
                        x.DataBaseIntegration(dbi =>
                        {
                            dbi.Batcher<OracleDataClientBatchingBatcherFactory>();
                            dbi.Dialect<Oracle10gDialect>();
                            dbi.BatchSize = 100;
                        });
                        x.SetProperty(Environment.DefaultBatchFetchSize, "50");
                        x.Cache(cache => cache.Provider<HashtableCacheProvider>());
                        x.SetInterceptor(new ReferenceDataInterceptor());
                        x.SetInterceptor(new SqlStatementInterceptor());
                    });

I have a separate query routine that uses the Oracle.ManagedDataAccess dll that was also bombing out for an InvalidCastException when used against similar data. I verified using the query that the issue resolved when I changed the value of the problematic number at the last decimal place). I'm still trying to figure out the exact source of the problem under NHibernate, but the signature is basically the same InvalidCastException at GetDecimal().

Example value that causes cast exception: 0.00000000000000422030887989616

Let me know if I can provide any additional information. Appreciate any help you can provide; I'm looking at a pretty ugly issue if I don't get this resolved soon.

[hazzik]

Looks like the issue with the provider.

[kriewall]

Meaning there's nothing I can do from an application perspective that can prevent problematic values from getting inserted into the dB?

[kriewall]

@hazzik I discovered that using GetDouble() allows the value to be returned successfully, as opposed to the GetDecimal() method which gets called from within GetValue(). The property which is having a problem is a nullable double. Is there something about my use of a nullable double that is causing NHibernate to call GetValue() instead of GetDouble(), or does NHibernate use GetValue() across the board?

    <property access="field.camelcase-underscore" name="FinalCheckMeasurement" type="System.Nullable`1[[System.Double, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]], mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
      <column name="FinalCheckMeasurement" not-null="false" />
    </property>

Applying precision and scale doesn't seem to make any difference in how the number is recorded to the dB.

[maca88]

Meaning there's nothing I can do from an application perspective that can prevent problematic values from getting inserted into the dB?

As you are using Oracle 11, you can enable binary floating point types, by setting configuration setting oracle.use_binary_floating_point_types to true. Note that this setting was added in 5.3 by #2349, so it won't work with NHibernate 5.2.7.

[kriewall]

@maca88 Thank you for the suggestion! I tried it out but now I'm seeing InvalidCastExceptions at the same query but across the board for all numeric (double) values. The registration that happens in Oracle10gDialect.RegisterFloatingPointTypeMappings() is applied at data intake, correct? Or is this something I would have had to have enabled before any data was inserted into the dB in order to avoid compatibility issues?

[kriewall]

@maca88 I managed to confirm that the data inserted is slightly different when OracleUseBinaryFloatingPointTypes is on versus off ... surprisingly (to me, anyway), the precision seems to be increased when the setting is true.

OracleUseBinaryFloatingPointTypes = false:

OracleUseBinaryFloatingPointTypes = true

In either case, GetDecimal() is still being called internally (same stack trace as above). I agree with @hazzik that this seems to be a bug with the GetValue() method on the provider; but is there anything I can do to get NHibernate to call GetDouble() instead of GetValue()?

I did happen to come across this; the final response might provide a workaround, although his implementation isn't working out of the box for me, so I'll have to figure out how to enable it.

[maca88]

The registration that happens in Oracle10gDialect.RegisterFloatingPointTypeMappings() is applied at data intake, correct?

Yes, but is it is also used to create columns of type binary_double instead of DOUBLE PRECISION.

I tried it out but now I'm seeing InvalidCastExceptions

That is because you most likely didn't change the column type to binary_double. In case you cannot change the column type to binary_double, then it is better to not use it. I think that oracle driver decides which method to call depending on the column type of the value. The advantage of binary_double is that is equivalent to C# double as both complies with IEEE 754 standard, where for DOUBLE PRECISION the driver need to manually convert it double and as you pointer out, it is not always possible. So I would recommend you to change the columns by using a script in case you cannot recreate the database.

[kriewall]

@maca88 Thanks. I can look into changing the column types; the challenge is that I'm using Fluent Migrator to generate the dB, and it appears to be using double precision instead of binary_double to generate the columns. But that's a feature request for them. :)

I just came across this about the same time that I received the response below from Oracle support.

In ODP.NET 19.10, we added a new property to OracleDataReader and OracleDataAdapter, SuppressGetDecimalInvalidCastException, for this exact use case. Set the property value to true and it will round off the NUMBER value and suppress getting the exception.

Is this a configuration setting that could be added to NHibernate? It seems like this would solve my problem very easily.

[kriewall]

@maca88 Direct link to SuppressGetDecimalInvalidCastException documentation here in case it's useful.

[maca88]

Is this a configuration setting that could be added to NHibernate?

Currently NHibernate doesn't have any interceptor for DbDataReader where this property can be set and it is something that could be added in the future. As for now, the only option you is to extend OracleManagedDataClientDriver, override CreateCommand and GenerateCommand methods by calling the base method and wrapping the result into a custom command that wraps OracleCommand. In the custom command override ExecuteDbDataReaderAsync and ExecuteDbDataReader, where prior returning set the proeprty SuppressGetDecimalInvalidCastException to true. Finally register the extended diver instead of OracleManagedDataClientDriver in the NHibernate configuration. Example code:

public class WrappedCommand : DbCommand
{
  private OracleCommand _command;

  public WrappedCommand(OracleCommand command)
  {
    _command = command;
  }

  protected override async Task<DbDataReader> ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
  {
    var reader = (OracleDataReader) await _command.ExecuteReaderAsync(behavior, cancellationToken);
    reader.SuppressGetDecimalInvalidCastException = true;
    return reader;
  }

  protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior)
  {
    var reader = _command.ExecuteReader(behavior);
    reader.SuppressGetDecimalInvalidCastException = true;
    return reader;
  }

  // TODO: wrap missing methods
}

public class CustomDriver : OracleManagedDataClientDriver
{
  public override DbCommand CreateCommand()
  {
    return new WrappedCommand((OracleCommand) base.CreateCommand());
  }

  public override DbCommand GenerateCommand(CommandType type, SqlString sqlString, SqlType[] parameterTypes)
  {
    return new WrappedCommand((OracleCommand) base.GenerateCommand(type, sqlString, parameterTypes));
  }
}

[kriewall]

Got it - thanks for the tip! I enabled SuppressGetDecimalInvalidCastException in my custom query routine and it had absolutely no observable effect. I've sent a note to Oracle inquiring about it. I'll try out your method once I understand what either they or I are doing wrong. :)

[HakanKaraoglu]

@kriewall
Have you had any feedback regarding your problem? I'm having the similar problem too.
See #2914

[kriewall]

@HakanKaraoglu Sorry for the slow response. I did contact Oracle about it; they acknowledged it was a bug and committed to fix it in an upcoming version. However, I ended up putting in custom logic to limit the precision on values going to the dB since I couldn't wait for Oracle's release schedule and lost track of it after that. I would be curious to know whether a version of Oracle.ManagedDataAccess more recent than 19.10 resolves the issue, in case you find yourself checking it out.

[HakanKaraoglu]

hello @kriewall,
I had to find my own solution and go on :) that's what I did

[kriewall]

@maca88 I need to revive this conversation. I put in a local patch for the issue I faced a couple years ago, but I just ran into another problem pertaining to this behavior with the Oracle client. I've taken another look at the fix you recommended, but I'm continually running into InvalidCastExceptions, this time pertaining to the inability to cast between OracleCommand and WrappedCommand. I ended up with the following implementation since the incoming command from CreateCommand and GenerateCommand was not always of a consistent type:

public class CustomDriver : OracleManagedDataClientDriver
{
    public override DbCommand CreateCommand()
    {
        var command = base.CreateCommand();
        return command.GetType() == typeof(WrappedCommand) ? command : new WrappedCommand((OracleCommand)command);
    }

    public override DbCommand GenerateCommand(CommandType type, SqlString sqlString, SqlType[] parameterTypes)
    {
        var command = base.GenerateCommand(type, sqlString, parameterTypes);
        return command.GetType() == typeof(WrappedCommand) ? command : new WrappedCommand((OracleCommand)command);
    }
}

Once I did this, I started to see InvalidCastExceptions thrown by OracleDataClientDriverBase.OnBeforePrepare(DbCommand command). I attempted to resolve this by overriding OnBeforePrepare:

protected override void OnBeforePrepare(DbCommand command)
{
    var cmd = command.GetType() == typeof(WrappedCommand) ? ((WrappedCommand)command).OracleCommand : command;
    base.OnBeforePrepare(cmd);
}

... but that yields a NullReferenceException from the private method OracleDataClientBatchingBatcher.SetArrayBindCount(Int32 arraySize). I investigated a custom implementation of OracleDataClientBatchingBatcher, but it looks like that would require access to internal members within NHibernate.

So, I have a number of questions:

• Is there something else I need to override or some other straightforward custom implementation I need to get this to fall into place?
• I've tried specifying precision in the mapping, but it doesn't have any effect on writes to or reads from the database. If I set the precision very low, I should expect to see that reflected in the dB, yes? I am using Fluent NHibernate, so I've raised an issue with them here, but I'm not sure if my expectations are correct or if this is related to NHibernate itself.
• You mentioned above that adding an interceptor for DbDataReader such that SuppressGetDecimalInvalidCastException could be set is an option. Is this something that could be added to your roadmap? I'm hesitant to offer submitting a merge request myself since I might need a fair amount of guidance depending on the complexity and required knowledge of NHibernate internals