Unable to Use Decimal in EDM Mapping

andrew.williams

I've been trying to use edmmappings in my site's web config in order to force Decimal use over Double for float-like objects, but I'm getting an error whenever I try to use the decimal name. I'm using ODP for 12.2 with the below section added to by web config under the Configuration tag:

<oracle.dataaccess.client>

<settings>

<add name="decimal" value="edmmapping number(10,2)" />

</settings>

</oracle.dataaccess.client>

Other types like int64 and bool work with no issues, but for some reason decimal always throws this error:

'The type initializer for 'Oracle.DataAccess.Client.OracleParameter' threw an exception.'

ConfigurationErrorsException: decimal is invalid

I've tried every variation of decimal I can think of (decimal, System.Decimal, system.decimal) but adding the namespace doesn't seem to work, unless I'm using the wrong namespace? I'm just grasping at straws at this point, has anyone else had luck using the decimal type?

andrew.williams

<EDIT >

I wanted to update any other viewers of this thread on an even better solution found by one of my colleagues. If you are using the OracleDataAdapter to fill your dataset, then you can remap the data types used by the fill operation. Here's a brief example, hope it helps:

var dataAdapter = new OracleDataAdapter(" put command here ");

var dataSet = new DataSet();

dataAdapter.FillSchema(dataSet, SchemaType.Source);

foreach (DataTable table in dataSet.Tables)

{

     foreach (DataColumn column in table.Columns)

     {

          if (column.DataType == typeof(float) || column.DataType == typeof(double))

          {

               column.DataType = typeof(decimal);

          }

     }

}

dataAdapter.Fill(dataSet);

</EDIT>

@Mark.Williams Setting the Int64 mapping lower didn't cause the decimal to be used in it's place at higher number precisions.

@Alex Keh - Product Manager-Oracle Like you said, setting the scale won't work, I missed the part in the documentation where it states the edm mappings will only work for integer types. Using the ReturnProviderSpecificTypes setting causes too many other type conversion issues in other parts of the application so I don't think that will work for this purpose.

Ultimately I decided to go with setting SafeMappings on the OracleDataAdapter, it's low impact enough and should provide better support for huge oracle decimals in the future. Thanks all for your help!

Mark Williams

Hi,

I suspect you are seeing the error because "decimal" is not a valid mapping type:

https://docs.oracle.com/database/121/ODPNT/entityDataTypeMapping.htm

https://docs.oracle.com/database/121/ODPNT/entityDataTypeMapping.htm#ODPNT8300

The valid types to map to are:

bool

byte

int16

int32

int64

Regards,

Mark

andrew.williams

That's unfortunate. My real problem is that I'm upgrading some legacy code that makes a lot of stored procedure calls. These stored procs typically return cursors full of data. The problem is that in oracle 11, Number(10,2) type columns were being returned as Decimals, but now in 12.2 they're being returned as doubles.

Is there some other way to control which types are used when a data set is filled? Maybe something in the OracleDataAdapter class?

Mark Williams

I'm traveling this week so a bit limited, but you might try setting the Int64 mapping to a lower limit and anything higher than that should be returned as decimal by default. I might be misremembering that so definitely test it! I'm not sure if that will be acceptable as all values will be mapped.

Regards,

Mark

Alex Keh-Oracle

I believe the underlying issue is that you are trying to map a Number(10,2). From the doc:

https://docs.oracle.com/database/121/ODPNT/entityDataTypeMapping.htm#ODPNT8300

"You can configure a custom mapping in the .NET configuration file to override the default mapping for the Number(p,0) Oracle data type."

The scale value has to be a zero value for mapping. You can see this requirement in the mapping configuration for <oracle.manageddataaccess.client> and <oracle.unmanageddataaccess.client> as ODP.NET only lets you specify the precision, but never the scale any longer.

To control what types come back to DataSet, you can set the ReturnProviderSpecificTypes to true. I believe that will return you an Oracle Decimal for a Number(10,2)

https://docs.oracle.com/database/121/ODPNT/OracleDataAdapterClass.htm#CHDIGJCB

andrew.williams

<EDIT >

I wanted to update any other viewers of this thread on an even better solution found by one of my colleagues. If you are using the OracleDataAdapter to fill your dataset, then you can remap the data types used by the fill operation. Here's a brief example, hope it helps:

var dataAdapter = new OracleDataAdapter(" put command here ");

var dataSet = new DataSet();

dataAdapter.FillSchema(dataSet, SchemaType.Source);

foreach (DataTable table in dataSet.Tables)

{

     foreach (DataColumn column in table.Columns)

     {

          if (column.DataType == typeof(float) || column.DataType == typeof(double))

          {

               column.DataType = typeof(decimal);

          }

     }

}

dataAdapter.Fill(dataSet);

</EDIT>

@Mark.Williams Setting the Int64 mapping lower didn't cause the decimal to be used in it's place at higher number precisions.

@Alex Keh - Product Manager-Oracle Like you said, setting the scale won't work, I missed the part in the documentation where it states the edm mappings will only work for integer types. Using the ReturnProviderSpecificTypes setting causes too many other type conversion issues in other parts of the application so I don't think that will work for this purpose.

Ultimately I decided to go with setting SafeMappings on the OracleDataAdapter, it's low impact enough and should provide better support for huge oracle decimals in the future. Thanks all for your help!