Hexagon Geospatial
MENU

Developers Knowledge Base

GeoMedia, a comprehensive and dynamic GIS, extracts compelling intelligence from geospatial data and integrates it to present actionable information.
Showing results for 
Search instead for 
Do you mean 

ODP.NET System.OverflowException: Arithmetic operation resulted in an overflow on Spatial Data

by psmith on ‎04-01-2016 01:18 PM - edited on ‎04-06-2016 02:05 PM by Moderator (2,238 Views)

Problem

Attempting to programatically read Oracle spatial data (coming from GTECH) with c# using ODP.NET. In some cases the data has number values inside SDO_GEOMETRY’s Ordinate array that is too big for .NET to handle. When attempt to read the SDO_GEOMETRY value it throws:

System.OverflowException: Arithmetic operation resulted in an overflow

 

Solution

SDO_ORDINATE_ARRAY uses the NUMBER data type so the range is +- 1E-130 to 9.99E125 with 38 digits of precision.  All indications point to this being a bug in ODP.net. The doc says:

When accessing the OracleDecimal.Value property from an OracleDecimal that has a value greater than 28 precision, loss of precision can occur.

However, Oracle says the exception is expected behavior and the document is wrong.   One recommendation is to create your own custom .net class to accept the data. There is also a setting on the Oracle connection that will silently round Oracle decimals to .net decimals. You could also create an Oracle function that would be callable from ODP.NET that would handle this:
 

CREATE OR REPLACE function Get_Ordinate_Value(geom sdo_geometry, vPos number default 1) return number
is
begin
if geom is null then
return null;
end if;
if vPos <1 or vPos >geom.sdo_ordinates.count() then
return null;
end if;
return round(geom.sdo_ordinates(vPos ),8);
end;
/

This comes from the How to Setup the Safe Type Mapping from the Oracle Data Provider for .NET Developer's Guide:

  • To use the Safe Type Mapping functionality, the OracleDataAdapter.SafeMapping property must be set with a hashtable of key-value pairs. The key-value pairs must map database table column names (of type string) to a .NET type (of type Type). ODP.NET supports safe type mapping to byte[] and String types. Any other type mapping causes an exception.
  • In situations where the column names are not known at design time, an asterisk ("*") can be used to map all occurrences of database types to a safe .NET type where it is needed. If both the valid column name and the asterisk are present, the column name is used:
using System;
using System.Data;
using Oracle.DataAccess.Client;

/*
create table testme(empno number);
insert into testme values (0.12345678901234567890123456789);
commit;
*/
public class dataadapterfill
{
public static void Main()
{
OracleConnection con = new OracleConnection("user id=scott;password=tiger;data source=orcl");
con.Open();
OracleDataAdapter da = new OracleDataAdapter("SELECT * from testme",con);
DataSet ds = new DataSet();
//This is where you add the safemapping
da.SafeMapping.Add("EMPNO",typeof(string));
da.Fill(ds,"foo");
con.Close();
}
}


Here is another recommendation from Oracle support:

"OracleDecimal can store up to 38 precision, while the .NET Decimal datatype can only hold up to 28 precision. When accessing the OracleDecimal.Value property from an OracleDecimal that has a value greater than 28 precision, loss of precision can occur. To retrieve the actual value of OracleDecimal, use the OracleDecimal.ToString() method. Another approach is to obtain the OracleDecimal value as a byte array in an internal Oracle NUMBER format through the BinData property."

There is also a note that this was filed as bug 2882787 and fixed in 11.2 and later.

Overview
Contributors