Oracle's SDO_DIM_ARRAY as UDT and utilizing SDO_MIGRATE.TO_CURRENT as stored procedure

Author: Jonio, Dennis

Utilizing Oracle’s latest ODP for .NET the following is one implementation of the SDO_DIM_ARRAY and SDO_DIM_ELEMENT type(s) as a .NET class.

This one is easy to explain. I needed to use SDO_MIGRATE.TO_CURRENT to fix some ORA-13367: wrong orientation for interior/exterior rings errors. So I needed SDO_DIM_ARRAY and thusly SDO_DIM_ELEMENT because SDO_MIGRATE.TO_CURRENT requires a SDO_DIM_ARRAY as a parameter. I have included my method of utilizing SDO_MIGRATE.TO_CURRENT after the class definitions.

Source code (C#):

+ expand sourceview plainprint?

1. using System;  
2. using System.Collections;  
3. using System.Text;  
4. using Oracle.DataAccess.Client;  
5. using Oracle.DataAccess.Types;  
6.   
7. namespace NetSdoDimArray  
8. {  
9.     /// <summary>  
10.     /// ////////////////////////////////////  
11.     /// sdodimarray  
12.     /// ////////////////////////////////////  
13.     /// </summary>  
14.     public class sdodimarray : IOracleCustomType, INullable  
15.     {  
16.         [OracleObjectMapping(0)]  
17.         public sdodimelement[] _dimelements;  
18.         private bool m_bIsNull;  
19.   
20.         public bool IsNull  
21.         {  
22.             get  
23.             {  
24.                 return m_bIsNull;  
25.             }  
26.         }  
27.   
28.         public sdodimelement[] DimElements  
29.         {  
30.             get  
31.             {  
32.                 return _dimelements;  
33.             }  
34.             set  
35.             {  
36.                 _dimelements = value;  
37.             }  
38.         }  
39.   
40.         public static sdodimarray Null  
41.         {  
42.             get  
43.             {  
44.                 sdodimarray obj = new sdodimarray();  
45.                 obj.m_bIsNull = true;  
46.                 return obj;  
47.             }  
48.         }  
49.   
50.         public override string ToString()  
51.         {  
52.             if (m_bIsNull)  
53.                 return "sdodimarray.Null";  
54.             else  
55.             {  
56.                 StringBuilder sb = new StringBuilder();  
57.                 foreach (sdodimelement i in _dimelements)  
58.                 {  
59.                     sb.Append("sdodimelement(");  
60.                     sb.Append(i._dimname + "=");  
61.                     sb.Append(string.Format("{0:0.#####}", i._lb));  
62.                     sb.Append(",");  
63.                     sb.Append(string.Format("{0:0.#####}", i._ub));  
64.                     sb.Append(",Tol=");  
65.                     sb.Append(i._tolerance.ToString());  
66.                     sb.Append(")");  
67.                 }  
68.                 return sb.ToString();  
69.             }  
70.         }  
71.   
72.         public void ToCustomObject(OracleConnection con, IntPtr pUdt)  
73.         {  
74.             _dimelements = (sdodimelement[])OracleUdt.GetValue(con, pUdt, 0);  
75.         }  
76.   
77.         public void FromCustomObject(OracleConnection con, IntPtr pUdt)  
78.         {  
79.             OracleUdt.SetValue(con, pUdt, 0, _dimelements);  
80.         }  
81.     }  
82.   
83.     [OracleCustomTypeMapping("MDSYS.SDO_DIM_ARRAY")]  
84.     public class sdodimarrayFactory : IOracleArrayTypeFactory, IOracleCustomTypeFactory  
85.     {  
86.         // IOracleCustomTypeFactory Inteface  
87.         public IOracleCustomType CreateObject()  
88.         {  
89.             return new sdodimarray();  
90.         }  
91.   
92.         // IOracleArrayTypeFactory.CreateArray Inteface  
93.         public Array CreateArray(int numElems)  
94.         {  
95.             return new sdodimelement[numElems];  
96.         }  
97.   
98.         // IOracleArrayTypeFactory.CreateStatusArray  
99.         public Array CreateStatusArray(int numElems)  
100.         {  
101.             // An OracleUdtStatus[] is not required to store null status information  
102.             // if there is no NULL attribute data in the element array  
103.             return null;  
104.         }  
105.     }  
106.   
107.     /// <summary>  
108.     /// ////////////////////////////////////  
109.     /// sdodimelement  
110.     /// ////////////////////////////////////  
111.     /// </summary>  
112.     public class sdodimelement : IOracleCustomType, INullable  
113.     {  
114.         [OracleObjectMapping(0)]  
115.         public string _dimname;  
116.   
117.         [OracleObjectMapping(1)]  
118.         public double _lb;  
119.   
120.         [OracleObjectMapping(2)]  
121.         public double _ub;  
122.   
123.         [OracleObjectMapping(3)]  
124.         public double _tolerance;  
125.   
126.         private bool m_bIsNull;  
127.   
128.         public bool IsNull  
129.         {  
130.             get  
131.             {  
132.                 return m_bIsNull;  
133.             }  
134.         }  
135.   
136.         public static sdodimelement Null  
137.         {  
138.             get  
139.             {  
140.                 sdodimelement obj = new sdodimelement();  
141.                 obj.m_bIsNull = true;  
142.                 return obj;  
143.             }  
144.         }  
145.   
146.         public override string ToString()  
147.         {  
148.             if (m_bIsNull)  
149.                 return "sdodimelement.Null";  
150.             else  
151.             {  
152.                 StringBuilder sb = new StringBuilder();  
153.                 sb.Append("sdodimelement(");  
154.                 sb.Append(_dimname + "=");  
155.                 sb.Append(string.Format("{0:0.#####}", _lb));  
156.                 sb.Append(",");  
157.                 sb.Append(string.Format("{0:0.#####}", _ub));  
158.                 sb.Append(",Tol=");  
159.                 sb.Append(_tolerance.ToString());  
160.                 sb.Append(")");  
161.                 return sb.ToString();  
162.             }  
163.         }  
164.   
165.         public void ToCustomObject(OracleConnection con, IntPtr pUdt)  
166.         {  
167.             _dimname = (string)OracleUdt.GetValue(con, pUdt, 0);  
168.             _lb = (double)OracleUdt.GetValue(con, pUdt, 1);  
169.             _ub = (double)OracleUdt.GetValue(con, pUdt, 2);  
170.             _tolerance = (double)OracleUdt.GetValue(con, pUdt, 3);  
171.         }  
172.   
173.         public void FromCustomObject(OracleConnection con, IntPtr pUdt)  
174.         {  
175.             OracleUdt.SetValue(con, pUdt, 0, _dimname);  
176.             OracleUdt.SetValue(con, pUdt, 1, _lb);  
177.             OracleUdt.SetValue(con, pUdt, 2, _ub);  
178.             OracleUdt.SetValue(con, pUdt, 3, _tolerance);  
179.         }  
180.     }  
181.   
182.     [OracleCustomTypeMapping("MDSYS.SDO_DIM_ELEMENT")]  
183.     public class sdodimelementFactory : IOracleCustomTypeFactory  
184.     {  
185.         // IOracleCustomTypeFactory Inteface  
186.         public IOracleCustomType CreateObject()  
187.         {  
188.             sdodimelement sdodimelement = new sdodimelement();  
189.             return sdodimelement;  
190.         }  
191.     }  
192. }  

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

namespace NetSdoDimArray
{
    /// 

    /// ////////////////////////////////////
    /// sdodimarray
    /// ////////////////////////////////////
    /// 
    public class sdodimarray : IOracleCustomType, INullable
    {
        [OracleObjectMapping(0)]
        public sdodimelement[] _dimelements;
        private bool m_bIsNull;

        public bool IsNull
        {
            get
            {
                return m_bIsNull;
            }
        }

        public sdodimelement[] DimElements
        {
            get
            {
                return _dimelements;
            }
            set
            {
                _dimelements = value;
            }
        }

        public static sdodimarray Null
        {
            get
            {
                sdodimarray obj = new sdodimarray();
                obj.m_bIsNull = true;
                return obj;
            }
        }

        public override string ToString()
        {
            if (m_bIsNull)
                return "sdodimarray.Null";
            else
            {
                StringBuilder sb = new StringBuilder();
                foreach (sdodimelement i in _dimelements)
                {
                    sb.Append("sdodimelement(");
                    sb.Append(i._dimname + "=");
                    sb.Append(string.Format("{0:0.#####}", i._lb));
                    sb.Append(",");
                    sb.Append(string.Format("{0:0.#####}", i._ub));
                    sb.Append(",Tol=");
                    sb.Append(i._tolerance.ToString());
                    sb.Append(")");
                }
                return sb.ToString();
            }
        }

        public void ToCustomObject(OracleConnection con, IntPtr pUdt)
        {
            _dimelements = (sdodimelement[])OracleUdt.GetValue(con, pUdt, 0);
        }

        public void FromCustomObject(OracleConnection con, IntPtr pUdt)
        {
            OracleUdt.SetValue(con, pUdt, 0, _dimelements);
        }
    }

    [OracleCustomTypeMapping("MDSYS.SDO_DIM_ARRAY")]
    public class sdodimarrayFactory : IOracleArrayTypeFactory, IOracleCustomTypeFactory
    {
        // IOracleCustomTypeFactory Inteface
        public IOracleCustomType CreateObject()
        {
            return new sdodimarray();
        }

        // IOracleArrayTypeFactory.CreateArray Inteface
        public Array CreateArray(int numElems)
        {
            return new sdodimelement[numElems];
        }

        // IOracleArrayTypeFactory.CreateStatusArray
        public Array CreateStatusArray(int numElems)
        {
            // An OracleUdtStatus[] is not required to store null status information
            // if there is no NULL attribute data in the element array
            return null;
        }
    }

    /// 

    /// ////////////////////////////////////
    /// sdodimelement
    /// ////////////////////////////////////
    /// 
    public class sdodimelement : IOracleCustomType, INullable
    {
        [OracleObjectMapping(0)]
        public string _dimname;

        [OracleObjectMapping(1)]
        public double _lb;

        [OracleObjectMapping(2)]
        public double _ub;

        [OracleObjectMapping(3)]
        public double _tolerance;

        private bool m_bIsNull;

        public bool IsNull
        {
            get
            {
                return m_bIsNull;
            }
        }

        public static sdodimelement Null
        {
            get
            {
                sdodimelement obj = new sdodimelement();
                obj.m_bIsNull = true;
                return obj;
            }
        }

        public override string ToString()
        {
            if (m_bIsNull)
                return "sdodimelement.Null";
            else
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("sdodimelement(");
                sb.Append(_dimname + "=");
                sb.Append(string.Format("{0:0.#####}", _lb));
                sb.Append(",");
                sb.Append(string.Format("{0:0.#####}", _ub));
                sb.Append(",Tol=");
                sb.Append(_tolerance.ToString());
                sb.Append(")");
                return sb.ToString();
            }
        }

        public void ToCustomObject(OracleConnection con, IntPtr pUdt)
        {
            _dimname = (string)OracleUdt.GetValue(con, pUdt, 0);
            _lb = (double)OracleUdt.GetValue(con, pUdt, 1);
            _ub = (double)OracleUdt.GetValue(con, pUdt, 2);
            _tolerance = (double)OracleUdt.GetValue(con, pUdt, 3);
        }

        public void FromCustomObject(OracleConnection con, IntPtr pUdt)
        {
            OracleUdt.SetValue(con, pUdt, 0, _dimname);
            OracleUdt.SetValue(con, pUdt, 1, _lb);
            OracleUdt.SetValue(con, pUdt, 2, _ub);
            OracleUdt.SetValue(con, pUdt, 3, _tolerance);
        }
    }

    [OracleCustomTypeMapping("MDSYS.SDO_DIM_ELEMENT")]
    public class sdodimelementFactory : IOracleCustomTypeFactory
    {
        // IOracleCustomTypeFactory Inteface
        public IOracleCustomType CreateObject()
        {
            sdodimelement sdodimelement = new sdodimelement();
            return sdodimelement;
        }
    }
}

As you see, it utilizes both sdogeometry and sdodimarray...

+ expand sourceview plainprint?

1. public static sdogeometry MigrateToCurrentSP(string connectstring, sdogeometry _geoIn, sdodimarray _toleranceIn)  
2.         {  
3.             //ORA-13367:      wrong orientation for interior/exterior rings  
4.             sdogeometry rtnval = null;  
5.             using (OracleConnection conn = new OracleConnection(connectstring))  
6.             {  
7.                 using (OracleCommand dbcmd = conn.CreateCommand())  
8.                 {  
9.                     dbcmd.CommandType = CommandType.StoredProcedure;  
10.                     dbcmd.CommandText = "SDO_MIGRATE.TO_CURRENT";  
11.   
12.                     OracleParameter cParm1 = new OracleParameter();  
13.                     cParm1.ParameterName = "geometry";  
14.                     cParm1.OracleDbType = OracleDbType.Object;  
15.                     cParm1.Value = _geoIn;  
16.                     cParm1.UdtTypeName = "MDSYS.SDO_GEOMETRY";  
17.                     cParm1.Direction = ParameterDirection.Input;  
18.   
19.                     OracleParameter cParm2 = new OracleParameter();  
20.                     cParm2.ParameterName = "tolerance";  
21.                     cParm2.OracleDbType = OracleDbType.Object;  
22.                     cParm2.Value = _toleranceIn;  
23.                     cParm2.UdtTypeName = "MDSYS.SDO_DIM_ARRAY";  
24.                     cParm2.Direction = ParameterDirection.Input;  
25.   
26.                     OracleParameter cParm3 = new OracleParameter();  
27.                     cParm3.ParameterName = "RETURNVALUE";  
28.                     cParm3.OracleDbType = OracleDbType.Object;  
29.                     cParm3.UdtTypeName = "MDSYS.SDO_GEOMETRY";  
30.                     cParm3.Direction = ParameterDirection.ReturnValue;  
31.   
32.                     // Note the order  
33.                     dbcmd.Parameters.Add(cParm3);  
34.                     dbcmd.Parameters.Add(cParm1);  
35.                     dbcmd.Parameters.Add(cParm2);  
36.   
37.                     try  
38.                     {  
39.                         conn.Open();  
40.                         dbcmd.ExecuteNonQuery();  
41.                         rtnval = (sdogeometry)dbcmd.Parameters["RETURNVALUE"].Value;  
42.                     }  
43.                     catch (Exception _Ex)  
44.                     {  
45.                         throw (_Ex); // Actually rethrow  
46.                     }  
47.                     finally  
48.                     {  
49.                         if (conn != null && conn.State == ConnectionState.Open)  
50.                         {  
51.                             conn.Close();  
52.                         }  
53.                     }  
54.                 }//using cmd  
55.             }//using conn  
56.   
57.             return rtnval;  
58.         }  

public static sdogeometry MigrateToCurrentSP(string connectstring, sdogeometry _geoIn, sdodimarray _toleranceIn)
        {
            //ORA-13367:      wrong orientation for interior/exterior rings
            sdogeometry rtnval = null;
            using (OracleConnection conn = new OracleConnection(connectstring))
            {
                using (OracleCommand dbcmd = conn.CreateCommand())
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    dbcmd.CommandText = "SDO_MIGRATE.TO_CURRENT";

                    OracleParameter cParm1 = new OracleParameter();
                    cParm1.ParameterName = "geometry";
                    cParm1.OracleDbType = OracleDbType.Object;
                    cParm1.Value = _geoIn;
                    cParm1.UdtTypeName = "MDSYS.SDO_GEOMETRY";
                    cParm1.Direction = ParameterDirection.Input;

                    OracleParameter cParm2 = new OracleParameter();
                    cParm2.ParameterName = "tolerance";
                    cParm2.OracleDbType = OracleDbType.Object;
                    cParm2.Value = _toleranceIn;
                    cParm2.UdtTypeName = "MDSYS.SDO_DIM_ARRAY";
                    cParm2.Direction = ParameterDirection.Input;

                    OracleParameter cParm3 = new OracleParameter();
                    cParm3.ParameterName = "RETURNVALUE";
                    cParm3.OracleDbType = OracleDbType.Object;
                    cParm3.UdtTypeName = "MDSYS.SDO_GEOMETRY";
                    cParm3.Direction = ParameterDirection.ReturnValue;

                    // Note the order
                    dbcmd.Parameters.Add(cParm3);
                    dbcmd.Parameters.Add(cParm1);
                    dbcmd.Parameters.Add(cParm2);

                    try
                    {
                        conn.Open();
                        dbcmd.ExecuteNonQuery();
                        rtnval = (sdogeometry)dbcmd.Parameters["RETURNVALUE"].Value;
                    }
                    catch (Exception _Ex)
                    {
                        throw (_Ex); // Actually rethrow
                    }
                    finally
                    {
                        if (conn != null && conn.State == ConnectionState.Open)
                        {
                            conn.Close();
                        }
                    }
                }//using cmd
            }//using conn

            return rtnval;
        }