Efficiently Converting OracleDecimal to .NET decimal w/truncation

Question

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?

Answer 1

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

Answer 2

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.