How to declare MDSYS.Sdo_Geometry in c#

Question

I have a stored procedure in an Oracle database which takes a MDSYS.Sdo_Geometry and number type as input and give t_t_out_house_details object and p_return_cd number.

PROCEDURE prc_get_impacted_houses(p_polygon_geom      IN MDSYS.Sdo_Geometry,
                                p_imkl_id        IN NUMBER,
                                p_out_house_details OUT     t_t_out_house_details,
                                p_return_cd         OUT NUMBER) IS
v_proj_dist_limit   NUMBER;
v_out_house_details t_t_out_house_details := t_t_out_house_details();
v_net_cd            VARCHAR2(6);
v_subnet_nr         NUMBER(5);
v_cable_number      NUMBER(3);
invalid_geometry EXCEPTION;

I want to call this procedure from C# and show the result(return) as output. I want to declare MDSYS.Sdo_Geometry datatype. I don't know what type I should declare. I tried to declare using spatial class geomtry but I don't know how to use it. I can't find any example for that in the internet.

this is my c# code:

static public void CallingStoredProcedure(GeometryPolygon PPolygonGeom, int PImklId)
    { 
    //conection to the database

        using (OracleConnection conn = new OracleConnection("Data Source=; User ID=; Password="))
        {                
            OracleCommand cmd = new OracleCommand();
            conn.Open();
            cmd = new OracleCommand("prc_get_impacted_houses", conn);

            OracleParameter op = null;

            cmd.CommandType = CommandType.StoredProcedure;

            //cmd.Parameters.Add("p_polygon_geom", OracleType.).Value = PPolygonGeom;
            cmd.Parameters.Add("p_imkl_id", OracleDbType.Int32).Value = PImklId;
            op = new OracleParameter("p_return_cd", OracleDbType.Int32);
            op.Direction = ParameterDirection.Output;

            cmd.Parameters.Add(op);

            //CALL PROCEDURE
            try
            {
                cmd.ExecuteNonQuery();

                Console.WriteLine(cmd.Parameters["p_return_cd"].Value);
            }
            catch (Exception ex)
            {
                throw new Exception("Error " + ex.Message);
            }
            finally 
            {
                conn.Close();
            }
        }
    }

Answer 1

Oracle has made it possible now to use their Managed drivers as well. I've made a modified version of the NetSdoGeometry implementation called ManagedNetSdoGeometry. You can find it here,

https://github.com/flappah/ManagedNetSdoGeometry

Answer 2

There is solution http://www.orafaq.com/forum/mv/msg/27794/296419/#msg_296419 You shold define UDTs: MDSYS.SDO_GEOMETRY:

using System;
using System.Collections.Generic;
using System.Text;
using Oracle.DataAccess.Types;
using Oracle.DataAccess.Client;
using System.Data;

namespace some.namespace
{
  [OracleCustomTypeMappingAttribute("MDSYS.SDO_GEOMETRY")]
  public class SdoGeometry : OracleCustomTypeBase<SdoGeometry>
  {

    private enum OracleObjectColumns { SDO_GTYPE, SDO_SRID, SDO_POINT, SDO_ELEM_INFO, SDO_ORDINATES }

    private decimal? sdo_Gtype;

    [OracleObjectMappingAttribute(0)]
    public decimal? Sdo_Gtype
    {
      get { return sdo_Gtype; }
      set { sdo_Gtype = value; }
    }

    private decimal? sdo_Srid;

    [OracleObjectMappingAttribute(1)]
    public decimal? Sdo_Srid
    {
      get { return sdo_Srid; }
      set { sdo_Srid = value; }
    }

    private SdoPoint point;

    [OracleObjectMappingAttribute(2)]
    public SdoPoint Point
    {
      get { return point; }
      set { point = value; }
    }

    private decimal[] elemArray;

    [OracleObjectMappingAttribute(3)]
    public decimal[] ElemArray
    {
      get { return elemArray; }
      set { elemArray = value; }
    }

    private decimal[] ordinatesArray;

    [OracleObjectMappingAttribute(4)]
    public decimal[] OrdinatesArray
    {
      get { return ordinatesArray; }
      set { ordinatesArray = value; }
    }

    [OracleCustomTypeMappingAttribute("MDSYS.SDO_ELEM_INFO_ARRAY")]
    public class ElemArrayFactory : OracleArrayTypeFactoryBase<decimal> {}

    [OracleCustomTypeMappingAttribute("MDSYS.SDO_ORDINATE_ARRAY")]
    public class OrdinatesArrayFactory : OracleArrayTypeFactoryBase<decimal> {}

    public override void MapFromCustomObject()
    {
      SetValue((int)OracleObjectColumns.SDO_GTYPE, Sdo_Gtype);
      SetValue((int)OracleObjectColumns.SDO_SRID, Sdo_Srid);
      SetValue((int)OracleObjectColumns.SDO_POINT, Point);
      SetValue((int)OracleObjectColumns.SDO_ELEM_INFO, ElemArray);
      SetValue((int)OracleObjectColumns.SDO_ORDINATES, OrdinatesArray);
    }

    public override void MapToCustomObject()
    {
      Sdo_Gtype = GetValue<decimal?>((int)OracleObjectColumns.SDO_GTYPE);
      Sdo_Srid = GetValue<decimal?>((int)OracleObjectColumns.SDO_SRID);
      Point = GetValue<SdoPoint>((int)OracleObjectColumns.SDO_POINT);
      ElemArray = GetValue<decimal[]>((int)OracleObjectColumns.SDO_ELEM_INFO);
      OrdinatesArray = GetValue<decimal[]>((int)OracleObjectColumns.SDO_ORDINATES);
    }


    public static OracleParameter CreateSdoGeometry(string parameterName, SdoGeometry objGeometry, ParameterDirection direction = ParameterDirection.Input)
    {
        OracleParameter customParameter = new OracleParameter();

        customParameter.ParameterName = parameterName;
        customParameter.OracleDbType = OracleDbType.Object;
        customParameter.UdtTypeName = "MDSYS.SDO_GEOMETRY";
        customParameter.Direction = direction;
        customParameter.Value = objGeometry; 

        return customParameter;
    }
  }
}

and MDSYS.SDO_POINT_TYPE:

using System;
using System.Collections.Generic;
using System.Text;
using Oracle.DataAccess.Types;

namespace some.namespace
{
  [OracleCustomTypeMappingAttribute("MDSYS.SDO_POINT_TYPE")]
  public class SdoPoint : OracleCustomTypeBase<SdoPoint>
  {
    private decimal? x;

    [OracleObjectMappingAttribute("X")]
    public decimal? X
    {
      get { return x; }
      set { x = value; }
    }

    private decimal? y;

    [OracleObjectMappingAttribute("Y")]
    public decimal? Y
    {
      get { return y; }
      set { y = value; }
    }

    private decimal? z;

    [OracleObjectMappingAttribute("Z")]
    public decimal? Z
    {
      get { return z; }
      set { z = value; }
    }

    public override void MapFromCustomObject()
    {
      SetValue("X", x);
      SetValue("Y", y);
      SetValue("Z", z);
    }

    public override void MapToCustomObject()
    {
      X = GetValue<decimal?>("X");
      Y = GetValue<decimal?>("Y");
      Z = GetValue<decimal?>("Z");
    }
  }
}