Using ODP.Net with NHibernate and System.Decimal

A few months ago I wrote a question asking for solutions to this error.

There were some good answers but none of them provided me with a work around that was database independent. At least something that would work with both Oracle and SQL Server.

I was implementing some calculated fields in NHibernate and I again had to retrieve and modify a decimal from the database. Again I received the error ‘The arithmetic operation has caused an overflow.’

What I found was that the database method ROUND() is the same on Oracle and SQL Server. So I simply had to wrap my decimal in a ROUND() method.

For example, in my calculated field I needed to multiply Time by Cost. Cost is a decimal.

By rounding the TotalCost to 2 decimal places, I am able to use this in both Oracle and SQL Server.

<property name="TotalCost"
           formula="ROUND(((Time/60)*Cost), 2)"
           type="decimal"  />

You need to note that this error happens if you use ODP.Net to connect to Oracle.