How to use micro ORM Dapper with Oracle to display NUMBER (OracleDecimal)
The ODP.NET provider throws an exception in IDataReader.GetValue () / GetValues () if the column type is NUMBER (x, y), so it will overflow all .NET numeric types. Therefore Dapper cannot map such a column to the POCO property.
I have an Oracle stored procedure that uses the REF CURSOR output parameter to return records from three columns. Basically all 3 have NUMBER (something), but the ODP.NET Oracle managing the provider seems to be deciding which type of ODP.NET or .NET should flip them into.
I had problems with Dapper Query () displaying records from this sproc to POCOs. Maybe it's not really my fault this time - it seems that when the column is encountered as an ODP.NET type rather than a .NET type, Dapper fails. If I comment out the offending column from my POCO everything works.
Here's a couple of lines to illustrate:
-------------------------------------------------------------------- RDWY_LINK_ID RLC_LINK_OSET SIGN ---------------------- ---------------------- 1829 1.51639964279667746989761971196153763602 1 14380 578.483600357203322530102380288038462364 -1
The first column appears in .NET as int , the second as OracleDecimal , and the third as decimal . Second problem.
For example, removing Dapper for now and using vanilla ODP.NET to access these entries thus indicates a problem:
int linkid = (int)reader.GetValue(0); decimal linksign = (decimal)reader.GetValue(2); //decimal dlinkoffset = (decimal)reader.GetValue(1); //**invalid cast exception at at Oracle.ManagedDataAccess.Client.OracleDataReader.GetDecimal(Int32 i)** //object olinkoffset = reader.GetValue(1); //**same** //decimal dlinkoffset = reader.GetDecimal(1); //**same** //object values = new object[reader.FieldCount]; //reader.GetValues(values); //**same** OracleDecimal linkoffset = (OracleDecimal)reader.GetProviderSpecificValue(1); //this works! double dblinkoffset = reader.GetDouble(1); //interesting, this works too! //decimal dlinkoffset = linkoffset.Value; //overflow exception dblinkoffset = linkoffset.ToDouble(); //voila
What a little look and intercept I did in the Dapper SqlMapper.cs file shows me that it is fetching data from the reader using GetValue () / GetValues () as above, which fails.
Any suggestions for fixing Dapper? Many thanks.
After Reflection, I'm RTFMed: Section 3, "Retrieving Data from an OracleDataReader Object" in the Oracle Data Provider for .NET Developers Guidewhich explains. For NUMBER ODP.NET columns, the OracleDataReader will try to sequence .NET types from byte to decimal to prevent overflow. But NUMBER can still overflow Decimal, providing an invalid listing exception if you try to use any of the Access Access type readers (GetValue () / GetValues ()), in which case you need to use the ODP type Access type. NET GetProviderSpecificValue () which gives you OracleDecimal and if it overflows Decimal its Value property will give you an overflow exception and your only way is to coerce it to a smaller type with one of the OracleDecimal ToXxx () methods.
But, of course, the ODP.NET type access type is not part of the IDataReader interface used by Dapper to store reader objects, so it seems that Dapper itself is incompatible with Oracle when the column type overflows all .NET types.
The question remains - smart people know how to extend Dapper to handle this. I think I need an extension point where I could provide an implementation on how to use the reader (forcing it to use GetDouble () instead of GetValue (), or send it to OracleDataReader and call GetProviderSpecificValue ()) on a specific POCO property or column types.
source to share
To avoid this problem, I used:
CAST(COLUMN AS BINARY_DOUBLE)
In the Oracle types listed here , it is described as:
64-bit floating point number. This data type requires 9 bytes, including the length byte.
Most of the other types of numbers used by Oracle are 22 bytes, so this is as good as it is for .NET.
source to share