Passing array of raw(32) argument to Oracle Stored Procedure in ODP.NET

Question

I have a custom type and stored procedure defined as follows:

CREATE OR REPLACE TYPE GuidArray is varray(1000) of RAW(32);
/

CREATE OR REPLACE 
PROCEDURE BulkInsertTempItemGuid
(
  ItemGuidList IN GuidArray default null
) AS
BEGIN
  --
  -- procedure body here
  --
END;
/

I'm attempting to call this stored procedure in Oracle's ODP .NET from a c# application. I seem to be failling to set up my OracleParameter correctly, as Oracle is telling me that I am not sending it the correct parameter set. I'm setting the CollectionType, OracleDbType, and Value as follows. parameters is an OracleParameter and arrangedGuidList is an IEnumerable<Guid>.

parameter.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
parameter.OracleDbType = OracleDbType.Raw;
parameter.Value = arrangedGuidList.ToArray();

Exception:

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'BulkInsertTempItemGuid'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Answer 1

When calling a stored procedure once with an array (rather than calling a procedure that takes atomic items, and calling it multiple times), you must set the OracleDbType as Array. The value gets set to the underlying data structured, as specified in a custom oracle datatype class. In this case, it is a byte[][].

parameter.OracleDbType = OracleDbType.Array;
parameter.UdtTypeName = "GUIDARRAY";
parameter.Value = arrangedGuidList.ToArray();
// Note, do NOT set the collection type. Default it to "none"

I then defined my Oracle custom types. See here for an example. I adapted the SimpleVarray / SimpleVarrayFactory classes, but have them work with Byte[][].

%ODAC_HOME%\ODACsamples\odp.net\4\UDT

The key part of the factory class is that the Custom Type mapping must match the parameter's UdtTypeName property, and be all caps. It seems like that the OracleCustomTypeMapping causes ODAC to bind at runtime the custom type specified in to the parameter and build the GuidArray class somewhere under the hood via the ToCustomObject / FromCustomObject methods.

[OracleCustomTypeMapping("GUIDARRAY")]
public class GuidArrayFactory : IOracleCustomTypeFactory, IOracleArrayTypeFactory
{
    public IOracleCustomType CreateObject()
    {
        return new GuidArray();
    }

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

    public Array CreateStatusArray(int numElems)
    {
        // CreateStatusArray may return null if null status information 
        // is not required.
        return new OracleUdtStatus[numElems];
    }
}

-

    // See %ODAC_HOME%\ODACsamples\odp.net\4\UDT
    public class GuidArray : IOracleCustomType, INullable
    {
        [OracleArrayMapping]
        public Byte[][] Array;

        private OracleUdtStatus[] m_statusArray;
        public OracleUdtStatus[] StatusArray
        {
            get
            {
                return m_statusArray;
            }
            set
            {
                m_statusArray = value;
            }
        }

        private bool m_bIsNull;

        public bool IsNull
        {
            get
            {
                return m_bIsNull;
            }
        }

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

        public void ToCustomObject(OracleConnection con, IntPtr pUdt)
        {
            object objectStatusArray = null;
            Array = (Byte[][])OracleUdt.GetValue(con, pUdt, 0, out objectStatusArray);
            m_statusArray = (OracleUdtStatus[])objectStatusArray;
        }

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

        public override string ToString()
        {
            if (m_bIsNull)
                return "GuidArray.Null";

            string rtnstr = String.Empty;
            if (m_statusArray[0] == OracleUdtStatus.Null)
                rtnstr = "NULL";
            else
                rtnstr = Array.GetValue(0).ToString();
            for (int i = 1; i < m_statusArray.Length; i++)
            {
                if (m_statusArray[i] == OracleUdtStatus.Null)
                    rtnstr += "," + "NULL";
                else
                    rtnstr += "," + Array.GetValue(i);
            }
            return "GuidArray(" + rtnstr + ")";

        }
    }