Unable to Use Decimal in EDM Mapping — oracle-tech

    Forum Stats

  • 3,716,136 Users
  • 2,242,961 Discussions
  • 7,845,842 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Unable to Use Decimal in EDM Mapping

andrew.williams
andrew.williams Member Posts: 3
edited July 2018 in ODP.NET

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?

Best Answer

  • andrew.williams
    andrew.williams Member Posts: 3
    edited July 2018 Accepted Answer

    <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!

Answers

  • Mark Williams
    Mark Williams Member Posts: 67 Blue Ribbon
    edited June 2018

    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
    andrew.williams Member Posts: 3
    edited June 2018

    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
    Mark Williams Member Posts: 67 Blue Ribbon
    edited June 2018

    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
    Alex Keh-Oracle Posts: 2,753 Employee
    edited June 2018

    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
    andrew.williams Member Posts: 3
    edited July 2018 Accepted Answer

    <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!

This discussion has been closed.