Pass UDT defined in a package as a parameter to stored proc in Oracle

Question

A package is created to define a custom collection and a stored proc having this custom collection as an input param. How do I call this proc from c# ?

Here's the package:

CREATE OR REPLACE PACKAGE pkg_name
AS
    TYPE customCollectionType IS VARRAY(200) OF VARCHAR2 (1000);

    PROCEDURE ProcName(p_collection IN customCollectionType);
END pkg_name;
/
CREATE OR REPLACE PACKAGE BODY pkg_name
AS
    PROCEDURE StudyProc (p_StudyNum   IN     customCollectionType)
    IS
    ........................
END pkg_name;

Here's factory implementation of customCollectionType:

public class PlaceHolderType : IOracleCustomType, INullable
{
    [OracleArrayMapping()]
    public string[] Array;
    private bool m_bIsNull;
    private OracleUdtStatus[] m_statusArray;

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

    public virtual bool IsNull
    {
        get
        {
            return m_bIsNull;
        }
    }

    public static PlaceHolderType Null
    {
        get
        {
            PlaceHolderType p = new PlaceHolderType();
            p.m_bIsNull = true;
            return p;
        }
    }

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

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

    public override string ToString()
    {
        return string.Empty;
    }
}

[OracleCustomTypeMappingAttribute("USER_NAME.PKG_NAME.CUSTOMCOLLECTIONTYPE")]
public class CUSTOMCOLLECTIONTYPE: IOracleCustomTypeFactory, IOracleArrayTypeFactory
{
    // Implementation of IOracleCustomTypeFactory.CreateObject()
    public IOracleCustomType CreateObject()
    {
        // Return a new custom object
        //OracleString or;
        return new PlaceHolderType();
    }

    #region IOracleArrayTypeFactory Members
    public Array CreateArray(int numElems)
    {
        return new string[numElems];
    }

    public Array CreateStatusArray(int numElems)
    {
        return new OracleUdtStatus[numElems];
    }

    #endregion
}

Here's the call:

cmd.Connection = OracleConnectionObj;
cmd.BindByName = true;
cmd.CommandText = "PKG_NAME.PROC_NAME";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
OracleParameter param1 = new OracleParameter();
Array inputValue = (new CUSTOMCOLLECTIONTYPE()).CreateArray(5);
System.Array.Copy(SomeArray, inputValue, 5);
param1.OracleDbType = OracleDbType.Array;
param1.Direction = ParameterDirection.Input;
param1.UdtTypeName = "USER_NAME.CUSTOMCOLLECTIONTYPE";
param1.Value = inputValue;
cmd.Parameters.Add(param1);
cmd.ExecuteNonQuery();

The error in .Net is:

"OCI-22303: type \"USER_NAME\".\"CUSTOMCOLLECTIONTYPE\" not found"

Answer 1

When you are passing an object to the procedure from C# which is mapped to a UDT, then you need to define the type at the schema level and not at the package level. So, you need to execute the following command in sqlplus :-

Create Type customCollectionType AS VARRAY(200) OF VARCHAR2 (1000);

and remove the declaration of customCollectionType from your package spec.