Recently I was in fight with an ORACLE “issue” I found in my day-by-day coding activity. I was lucky to discover this “issue”, after 2 hours spent in debugging, rewriting mappings, and some bad words I said to my computer, I found a solution and a post which was helpful for me.

The situation: I was mapping a simple, usual, set of Entity Properties to a related set of ORACLE NUMBER columns with no precision info. These columns were updated by a Package Procedure. So, assuming Column1 and Column2 were the comlumns I was trying to map, I mapped them in a standard way as:

   1: <!-- ... -->
   2: <property name="PropertyOne"
   3:           type="System.Decimal">
   4:   <column name="COLUMN1"/>
   5: </property>
   6: <property name="PropertyTwo"
   7:           type="System.Decimal">
   8:   <column name="COLUMN2"/>
   9: </property>
  10: <!-- ... -->

I though this could be a very easy and standard mapping, so I didnt take  so much care about, because this was a usual mapping, but I was wrong (maybe I wasn’t but Oracle were)

The problem: After I’ve done with the mapping above, I experimented a strange kind of Exception, this Exception was so strange I fall in a desperate search of all possible causes, but with no asnwers. The Exception was “Arithmetic operation resulted in an overflow“.

Now, no spending more words about my search for a cause, This situation makes me crazy, what could be the problem I asked to me? Then, as a last try, I’ve done the same operation I was doing with NHibernate in the old ADO.NET – DataSet way, and here the surprise: I FOUND THE SAME ERROR!! How was it possible, a column of NUMBER, with no precision (this assume the biggest precision supported, I though) fall in an Exception during reading from?

So after some search in the Google universe, I found a possible solution, and when I read about, it makes me too much crazy than before. I found a post in the Oracle Forum which describe a similar situation with the same exception, the answer says something like:

..We found that this is caused by a return value that has more places to the right of the decimal point than .NET can handle…Be careful of functions that return calculated values. They somethimes try to return a larger number than even ODP.NET can handle.

The solution: After days, I found this a ridiculous thing, why this could happen? Trying to better explain what above sayd, the problem was with CALCULATED VALUES maybe some times a calculated decimal can result in a decimal point number too bigger than a standard .NET decimal point number, so, it’s clear that I felt in this kind of exception, and NHibernate was innocent, because the underlying ODP.NET was the problem. I don’t want to spend much words, (I wrote more than I’ll wish), following the suggestion, this kind of bug could be solved in no many ways:

  1. change the mapping to use with a VIEW with truncated values, but I don’t like to intriduce more objects than it’s needed
  2. change the mapping to truncate values, but this fall into a “broken mapping”, a mapping which target to a single Database implementation, in my case Oracle
  3. change the procedure to outputs a well delimited  NUMBER value, but this wasn’t possible because this part of the application wasn’t written and controlled by me

So I decided for second way, in this way, just change the mapping as I will show, my code and my mapping go ahead without exceptions and any kind of problem, here is the modified mapping:

   1: <!-- ... -->
   2: <property name="PropertyOne"
   3:           type="Decimal"
   4:           formula="TRUNC(COLUMN1, 6)" />
   5: <property name="PropertyTwo"
   6:           type="Decimal"
   7:           formula="TRUNC(COLUMN2, 6)" />
   8: <!-- ... -->

In this way the resulting query result with the TRUNC function which applied as above truncate the decimal places as a desired, fixed number.

I hope this helps and could prevent anyone to a “crazy-noon” as does! ah… here is the Oracle Forum Post which gracefully helps me Arithmetic opreation…POST

Sorry for my poor and bad English, I hope I will learn well..Ciao!

Technorati Tag: ,,