Using Dapper with Oracle User-Defined Types

Question

Is there a way to use Dapper with Oracle User-Defined Types returned by stored procedures? Dapper seems to work with stored procedures (see Using Dapper with Oracle stored procedures which return cursors).

var p = new OracleDynamicParameters();
p.Add("param_list", null, OracleDbType.Object, ParameterDirection.Output);
p.Add("param_reco", null, OracleDbType.Object, ParameterDirection.Output);

In my example I've added a UDT collection object as output parameter (param_list). I'm using the custom OracleDynamicParameters posted in Dapper – Micro ORM for Oracle and Microsoft .NET.

The more I read ORM related stuff, the more I see Oracle UDT objects as an obstacle. In this case plain ADO.NET and generated C# entity classes seem to be the only way to go. Maybe automapper.org might be useful for mapping domain objects to UDT objects.

what happens currently? does it error? or just not update the out parameters? or...? — Marc Gravell♦
Each Query call produces an ArgumentException (ODAC set_value). But this may be caused by the classic ODAC assemply used. I've replaced the Oracle.ManagedDataAccess.Client with the classic ODP.NET's Oracle.DataAccess.Client. The managed ODP driver is still beta. — Marco Mayer

kpollock kpollock · Accepted Answer · 2014-10-21T11:16:32

Look into using OracleCustomTypeMapping attribute and the OracleObjectMapping attribute. Can't recall where I got this idea from but...

public interface INullableOracleCustomType: INullable,  IOracleCustomType
    {
    }

[OracleCustomTypeMapping("<YOUR_SCHEMA_NAME>.<UDT_OBJECT_NAME>")]
    public class ParameterObject : INullableOracleCustomType
    {
        private bool objectIsNull;

        #region constructor

        public ParameterObject()
        { }

        public ParameterObject(string parameterName, string parameterValue)
        {
            this.ParameterName = parameterName;
            this.ParameterValue = parameterValue;
        }

        #endregion

        #region properties
        [OracleObjectMappingAttribute("PARAMETERNAME")]
        public string ParameterName { get; set; }

        [OracleObjectMappingAttribute("PARAMETERVALUE")]
        public string ParameterValue { get; set; }

        public static ParameterObject Null
        {
            get
            {
                ParameterObject parameterObject = new ParameterObject();
                parameterObject.objectIsNull = true;
                return parameterObject;
            }
        }

        #endregion

        #region INullable Members

        public bool IsNull
        {
            get { return objectIsNull; }
        }

        #endregion

        #region IOracleCustomType
        public void FromCustomObject(Oracle.DataAccess.Client.OracleConnection con, IntPtr pUdt)
        {
            // Convert from the Custom Type to Oracle Object
            if (!string.IsNullOrEmpty(ParameterName))
            {
                OracleUdt.SetValue(con, pUdt, "PARAMETERNAME", ParameterName);
            }
            if (!string.IsNullOrEmpty(ParameterValue))
            {
                OracleUdt.SetValue(con, pUdt, "PARAMETERVALUE", ParameterValue);
            }
        }

        public void ToCustomObject(Oracle.DataAccess.Client.OracleConnection con, IntPtr pUdt)
        {
            ParameterName = (string)OracleUdt.GetValue(con, pUdt, "PARAMETERNAME");
            ParameterValue = (string)OracleUdt.GetValue(con, pUdt, "PARAMETERVALUE");
        }

        #endregion
    }

example usage ...

public static OracleParameter CreateCustomTypeArrayInputParameter<T>(string name, string oracleUDTName, T value)
            where T : INullableOracleCustomType
        {
            OracleParameter parameter = new OracleParameter();
            parameter.ParameterName = name;
            parameter.OracleDbType = OracleDbType.Array;
            parameter.Direction = ParameterDirection.Input;
            parameter.UdtTypeName = oracleUDTName;
            parameter.Value = value;
            return parameter;
        }

Oracle seems fussy about the schema/type/object names being in ALLCAPS

Using Dapper with Oracle User-Defined Types

1 Answers