Arithmetic operation resulted in an overflow
Joined: 28-Nov-2008
3.0 - Oracle ODP - Adapter
Calling GetScalar with an aggregate function of Avg. The database type is Number. In LLBL Designer, it's decimal 38.
The following query is being generated and executed:
Query: SELECT * FROM (SELECT TRUNC(AVG("TS"."DASHBOARD_V"."AGE"), 2
AS "Age" FROM "TS"."DASHBOARD_V" WHERE ( "TS"."DASHBOARD_V"."ASSIGNED_TO" = 
1)) WHERE rownum <= 1
Getting an exception of "Arithmetic operation resulted in an overflow" as soon as we call GetScalar.
If we run the query manually, we get back the correct avg.
Joined: 28-Nov-2008
Joined: 28-Nov-2008
Ok. First of all, the best option is that you fix the schema to a realistic (precision, scale) so the calculations would be shorter.
Second of all, I want to explain some things: As the OracleDecimal type is larger than the .Net System.Decimal (38 vs. 28 precision), you will receive potentially decimal overflows in two places: entity fetches and aggregates. For instance, if you have a value 379.0439858454766241651487553126 in your database, this test will fail:
[TestMethod]
public void ReproduceDecimalOverflowForEntityFetch()
{
TableaEntity t31 = new TableaEntity(1);
using (var adapter = new DataAccessAdapter())
{
adapter.FetchEntity(t31);
}
// at db: 379.0439858454766241651487553126, we expect an overflow exception
Assert.AreEqual( 379.0439858454766241651487553126, t31.B);
}
Note that the precision of that number is 31, that is why ODP.Net will raise an overflow, because it can't unbox in the IDataReader.
The other situation is when you fetch an Aggregate (like AVG). As you saw, LLBLGen Framework tries to avoid this emitting a TRUNC(..., 28 ). Nevertheless this doesn't eliminate the potential overflow, because it only trunc the decimal part, but no adjust the precision (I don't know any PL/SQL function that does that). So if the AVG returns 379.0439858454766241651487553126, the TRUNC(...,28 ) will return the same number, however it still has a precision=31. So that will raise an overflow exception from ODP as well. That is what you are experiencing. This is an example of that expected exception:
[TestMethod]
public void ReproduceDecimalOverflowForGetScalar()
{
decimal avg = decimal.Zero;
using (var adapter = new DataAccessAdapterOverflowSafe())
{
avg = (decimal)adapter.GetScalar(TableaFields.B, AggregateFunction.Avg);
}
// test the avg. We already get an exception at this point due to an ODP.Net decimal overflow
Assert.AreEqual(379.0439858454766241651487553126M, avg);
}
So, now go to the solutions. For the first scenario (entity fetches) you should override the DataAccessAdapter's **HandleValueReadErrors **method. I'm not that expert at ODP.Net but this worked for me:
protected override bool HandleValueReadErrors(IDataReader openReader, object[] toFill, Exception ex)
{
// get the actual OracleDataReader
OracleDataReader oraReader = (OracleDataReader)openReader;
// iterate the values and fillt them
for (int i = 0; i < toFill.Length; i++)
{
string typeAsString = oraReader.GetDataTypeName(i);
switch (typeAsString)
{
case "Decimal":
// get the decimal avoiding the unboxing overflow
decimal d = (decimal)(OracleDecimal.SetPrecision(oraReader.GetOracleDecimal(i), 28));
toFill[i] = d;
break;
default:
toFill[i] = openReader.GetValue(i);
break;
}
}
// this means that we handled the exception
return true;
}
For the second scenario (Aggregate fetches) it's different because the error comes directly from a command execute, and not from an IDataReader. That's why the TRUNC is emitted. So, the solution is emit a smaller value for the decimalPlaces parameter of the TRUNC function. For that you have create a custom derived OracleSpecificCreator, DynamicQueryEngine and DataAccessAdatper classes, and override the OracleSpecificCreator's **ConstructCallToAggregateWithFieldAsParameter **method. Similar to this:
protected override string ConstructCallToAggregateWithFieldAsParameter(AggregateFunction function, string fieldName)
{
string toReturn = string.Empty;
// this is the custom part. We will trunc all decimal results to only have 10 decimal places
int decimalPlaces = 10;
switch (function)
{
case AggregateFunction.Avg:
toReturn = string.Format("TRUNC(AVG({0}), {1})", fieldName, decimalPlaces);
break;
case AggregateFunction.AvgDistinct:
toReturn = string.Format("TRUNC(AVG(DISTINCT {0}), {1})", fieldName, decimalPlaces);
break;
...
}
return toReturn;
}
}
Sounds difficult? Well, I already have this coded for you. I'm attaching one file to this post. The only you have to do is: 1. Copy it to your DBSpecific project. 2. Change the first namespace of the file to your own DBSpecific one. 3. Add a reference to the Oracle.DataAccess assembly into your DBSpecific project. 4. Now you can use it in your code, it's pretty easy:
[TestMethod]
public void ReproduceDecimalOverflowForEntityFetch()
{
TableaEntity t31 = new TableaEntity(1);
using (var adapter = new DataAccessAdapterOverflowSafe())
{
adapter.FetchEntity(t31);
}
// at db: 379.0439858454766241651487553126, we expect the truncated value
Assert.AreEqual(379.0439858454766241651487553M, t31.B);
}
[TestMethod]
public void ReproduceDecimalOverflowForGetScalar()
{
decimal avg = decimal.Zero;
using (var adapter = new DataAccessAdapterOverflowSafe())
{
avg = (decimal)adapter.GetScalar(TableaFields.B, AggregateFunction.Avg);
}
// test the avg. As we trunc at our custom adapter, we expect only 10 decimal digits
Assert.AreEqual(379.0439858454M, avg);
}
Hope helpful 
| Filename | File size | Added on | Approval |
|---|---|---|---|
| OracleSpecificCreatorOverflowSafe.cs | 7,010 | 04-Aug-2011 21:49.07 | Approved |
Joined: 28-Nov-2008
Joined: 28-Nov-2008
Joined: 28-Nov-2008
Joined: 28-Nov-2008
Joined: 28-Nov-2008