CODEWITHSUNDEEP
c#.netoracle-databasetype-conversion
Karmic Coder
Karmic Coder

Reputation: 17949

Efficiently Converting OracleDecimal to .NET decimal w/truncation

I am getting an arithmetic overflow exception when trying to convert the following oracle spatial object to a coordinate set (decimals) in C# using (decimal) OracleUdt.GetValue()

MDSYS.SDO_GEOMETRY(2001, 1041001, 
  MDSYS.SDO_POINT_TYPE(-2.89957214912471,1.56043985049899E-15,NULL),NULL,NULL)

According to Oracle documentation, this is likely because one of the decimal values exceeds .NET's precision range of 28 decimals. Data that exceeds this precision limit in our database is extremely rare, and conversion needs to be as efficient as possible.

What is the best option for handling this exception by gracefully truncating the result if it exceeds the maximum precision?

Upvotes: 3

Views: 3427

Answers (2)

dipdapdop
dipdapdop

Reputation: 126

With reference to @AndrewR's answer, consider the following test:

    [Test, Explicit]
    public void OracleDecimal_NarrowingConversion_ShouldSucceed()
    {
        string sigfigs = "-9236717.7113439267890123456789012345678";
        OracleDecimal od = new OracleDecimal(sigfigs);
        OracleDecimal narrowedOd = OracleDecimal.SetPrecision(od, 28); //fails
        //OracleDecimal narrowedOd = OracleDecimal.SetPrecision(od, 27); //succeeds
        object narrowedObjectValue = (object)narrowedOd.Value;
        Assert.IsInstanceOf<decimal>(narrowedObjectValue);
    }

The Oracle documentation for the 12c Providers states that the precision should be between 1 and 38. (http://docs.oracle.com/cd/E51173_01/win.122/e17732/OracleDecimalStructure.htm#i1003600) . The .Net 'decimal' docs say that the precision is to 28 - 29 sig figs. I don't know why 28 doesn't work in this case.

Edit: If you remove the '-' sign, the above works at 28 significant figures.

Upvotes: 1

AndrewR
AndrewR

Reputation: 166

VB.NET code, untested, but I used something similar for a oracleDecimal I had. Transforming to C# should be easy.

    OracleDecimal oraDec = MDSYS.SDO_GEOMETRY(2001, 1041001, 
            MDSYS.SDO_POINT_TYPE(-2.89957214912471,1.56043985049899E-15,NULL),NULL,NULL)
    oraDec = OracleDecimal.SetPrecision(oraDec, 28)    ' !!!
    Decimal netDec = oraDec.Value

Upvotes: 1

Related Questions