First of let me say that LLBLGen Pro is brilliant product, probably the best O-R mapper for .Net, I've have used it for some time now and I am very pleased with it.

However I've encountered a problem using it with Oracle.

When fetching decimal values that are calculated an overflow exception is sometimes thrown. This is also a problem in the Oracle DataProvider as well, where it can be illustrated by the following example:

OracleCommand command = new OracleCommand("select 1/3 from dual" , ora);
OracleDataReader dr = command.ExecuteReader();
while (dr.Read())
{
             OracleDecimal d = dr.GetOracleDecimal(0);
             decimal value = (decimal) d; // Throws an "Arithmetic operation resulted in an overflow" exception
}
dr.Close();

However the code work if the offending line is replaced with :

Decimal d_works = (decimal)(d.ToDouble());

I guess one of reasons for this is because the calculated columns don't have length and precision set, because we depend heavily on materialized views to cache timeconsuming calculations, we cannot explicitly create the appropriate Number definitions on the DB.

• Peter Lundsby

Lundsby wrote:

First of let me say that LLBLGen Pro is brilliant product, probably the best O-R mapper for .Net, I've have used it for some time now and I am very pleased with it.

However I've encountered a problem using it with Oracle.

When fetching decimal values that are calculated an overflow exception is sometimes thrown. This is also a problem in the Oracle DataProvider as well, where it can be illustrated by the following example:

OracleCommand command = new OracleCommand("select 1/3 from dual" , ora);
OracleDataReader dr = command.ExecuteReader();
while (dr.Read())
{
             OracleDecimal d = dr.GetOracleDecimal(0);
             decimal value = (decimal) d; // Throws an "Arithmetic operation resulted in an overflow" exception
}
dr.Close();

However the code work if the offending line is replaced with :

Decimal d_works = (decimal)(d.ToDouble());

I guess one of reasons for this is because the calculated columns don't have length and precision set, because we depend heavily on materialized views to cache timeconsuming calculations, we cannot explicitly create the appropriate Number definitions on the DB.

This issue pops up with typed views only?

The problem is: a .NET decimal can contain a max. scale of 28. An OracleDecimal can contain a max scale of 38. Converting the OracleDecimal back to decimal, using a cast, calls the explicit convertion operator build inside OracleDecimal which gives up if the scale > 28. (it uses a lot of bitmunging code to convert the oracledecimal to 96bit .net decimals). The ToDouble() converts the value first to Real as it seems, which chops off the least significant digits. It can be solved if you alter (don't know if this is possible) your materialized views to have a maximum scale of 28. 1/3 has an unlimited scale. However 0.33333333333333 doesn't and it represents 1/3 'almost' (it never will, also OracleDecimal won't).

The problem occurs in ordinary EntityCollections, since materialized views are perceived as tables by the Oracle DataProvider.

It's not possible for us to alter the scale of the materialized views :-( , we could however round the results the of query, however that would indeed take away much of the good feeling you get when, using a datalayer. Because the layer should adapt to the database and not the other way around.

• Peter Lundsby

Lundsby wrote:

The problem occurs in ordinary EntityCollections, since materialized views are perceived as tables by the Oracle DataProvider.

It's not possible for us to alter the scale of the materialized views :-( , we could however round the results the of query, however that would indeed take away much of the good feeling you get when, using a datalayer. Because the layer should adapt to the database and not the other way around.

I'm not sure how I can solve this easily, as there isn't a conversion routine in place at the moment which does after-read conversions for fields, as such a routine would only be necessary in some occasions and it would slow down the fetch logic considerably if it was used always.

I've to check what is returned by the datareader, it should be a System.Decimal object, not an OracleDecimal object, however the ODP.NET driver could internally use an OracleDecimal first and then convert it to System.Decimal, which fails.

What type of object is stored in myEntity.Fields[index].CurrentValue for a field which could cause an overflow? OracleDecimal or System.Decimal ?

That's exactly what happens ODP.Net automatically tries to convert it to System.Decimal and fails. In fact the only way that I can get the value from ODP.Net is to use GetOracleDecimal, which in turn means the value returned must be an OracleDecimal.

The way I imagine it could be implemented is to generate conversion logic in special case where the returned value is an Number with unidentified length and precision. Other fields then these should not go through any conversion.

Implementing this feature would make LLBL much more usefull in Oracle scenarios involving time consuming calculations, where Materialized views are often used. This is often the case in the financial sector.

Lundsby wrote:

That's exactly what happens ODP.Net automatically tries to convert it to System.Decimal and fails. In fact the only way that I can get the value from ODP.Net is to use GetOracleDecimal, which in turn means the value returned must be an OracleDecimal.

Hmm. But then I can't fix it, as it would require oracle specific code in the generic data fetcher logic. The only way would be that the datareader is passed on to a database specific object, which reads all fields, however when to decide to use a specific method? This could only be determined based on pre-defined data, for example in the designer, where a specific settings should be added to allow the user to specify a conversion. Pretty awkward.

The way I imagine it could be implemented is to generate conversion logic in special case where the returned value is an Number with unidentified length and precision. Other fields then these should not go through any conversion.

To determine that special case is IMHO impossible, as the value in the datareader cell can't be read. So first it has to be read, then an exception follows, which then should result in a database specific action, however which one, as Oracle has more type specific classes.

Implementing this feature would make LLBL much more usefull in Oracle scenarios involving time consuming calculations, where Materialized views are often used. This is often the case in the financial sector.

If the materialized view has number precision and scale defined, would it then be solved? I'm reluctant to throw away the generic design of the core engine just because ODP.NET gives an error in a very specific situation.

The real problem is that the datareader already gives the exception. If it would have contained an OracleDecimal object, and later on the error would have been produced, the conversion routine could have been added to template code even.