Oracle Function to Read SDO_ORDINATE Values without causing Exception in .NET

Below is an Oracle function to read a specified SDO_ORDINATE value in an SDO_GEOMETRY attribute so that ODP.NET can handle them without causing an Arithmetic Overflow Exception in .NET when the Oracle NUMBER value has too many digits after the decimal place for a .NET Decimal value.

Most of this function comes from this thread on the Oracle Discussion Forum:

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;
/

Using the Function is something like this where I am getting the origin point and orientation vector for a 3D Oriented Point geometry:

select Get_Ordinate_Value(MyGeomAttr,1) as X,
        Get_Ordinate_Value(MyGeomAttr,2) as Y,
        Get_Ordinate_Value(MyGeomAttr,3) as Z,
        Get_Ordinate_Value(MyGeomAttr,4) as AngleX,
        Get_Ordinate_Value(MyGeomAttr,5) as AngleY,
        Get_Ordinate_Value(MyGeomAttr,6) as AngleZ
from MyTable

This function is pretty simple. I first check to see if the geometry attribute is Null and return Null if it is. I found that if I did not do this, you will get a geometry attribute that is Null passed into the function and you get a "ORA-06531: Reference to uninitialized collection" and it is sometimes hard to tell where it is coming from.

Then the index is checked to see that it is in range for the SDO_ORDINATE array.

I finally use the Round function to reduce the number of digits after the decimal point to a number that the .NET Decimal type can handle.