C# 2010, ODP.net, call stored procedure passing array

Question

I have a PL/SQL stored procedure that takes 4 inputs. One of those inputs is an associative array (Oracle Type: Table of VARCHAR2(1) index by PLS_INTEGER).

I want to have a C# program that calls this stored procedure with the proper inputs including the associative array.

I am using ODP.net 11.2 with Visual C# 2010 Express and Oracle 11gR2.

I cannot find any good examples of how to pass an array to a pl/sql procedure from C#. Can anybody give me an example? Following Oracle Documentation exactly gives me error saying Wrong number or type of arguments.

My C# Code:

        OracleCommand cmd = new OracleCommand("begin sdg_test.sdg_test2(:1); end;", conn);

        OracleParameter Param1 = cmd.Parameters.Add("1", OracleDbType.Varchar2);

        Param1.Direction = ParameterDirection.Input;

        Param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

        Param1.Value = new string[22] { "Y", "Y", "N", "Y", "N", "Y", "Y", "Y", "Y", "Y", "N", "Y", "N", "Y", "Y", "Y", "Y", "Y", "N", "Y", "N", "Y" };

        Param1.Size = 22;
        Param1.ArrayBindSize = new int[22] { 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 };

        cmd.ExecuteNonQuery();

        conn.Close();
        conn.Dispose();

All my procedure does is log a message. I am just trying to get this concept to work.

Answer 1

You can start from (simpler way):

List<int> idList = yourObjectList;
List<int> nameList = yourObjectList;

using (OracleConnection oraconn = new OracleConnection())
{
    oraconn.ConnectionString = "Your_Connection_string";

    using (OracleCommand oracmd = new OracleCommand())
    {
        oracmd.Connection = oraconn;

        oracmd.CommandType = CommandType.StoredProcedure;
        oracmd.CommandText = "Your_Procedura_name";
        oraconn.Open();

        // To use ArrayBinding, you need to set ArrayBindCount   
        oracmd.BindByName = true;
        oracmd.ArrayBindCount = idList.Count;

        // Instead of single values, we pass arrays of values as parameters   
        oracmd.Parameters.Add("ids", OracleDbType.Int32, oyourObjectList.ToArray(), ParameterDirection.Input);
        oracmd.Parameters.Add("names", OracleDbType.Varchar2, oyourObjectList.ToArray(), ParameterDirection.Input);

        oracmd.ExecuteNonQuery();
        oraconn.Close();
    }
}

Then, add package / procedure in db:

PROCEDURE Your_Procedure_name(
      name IN VARCHAR2,
      id IN NUMBER
      )    IS     
BEGIN

    INSERT INTO your_table VALUES( id, name);
END Your_Procedure_name;

Another option is:

using (OracleConnection oraconn = new OracleConnection())
{
    oraconn.ConnectionString = "Your_Connection_string";

    using (OracleCommand cmd = new OracleCommand())
    {

        cmd.Connection = oraconn;

        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "Your_Procedure_name";
        oraconn.Open();


        OracleParameter idParam = new OracleParameter("i_idList", OracleDbType.Int32, ParameterDirection.Input);
        idParam.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
        idParam.Value = idList.ToArray();
        idParam.Size = idList.Count;

        OracleParameter nameParam = new OracleParameter("i_nameList", OracleDbType.Varchar2, ParameterDirection.Input);
        nameParam.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
        nameParam.Value = nameList.ToArray();
        nameParam.Size = nameList.Count;

        // You need this param for output
        cmd.Parameters.Add("ret", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
        cmd.Parameters.Add(idParam);
        cmd.Parameters.Add(nameParam);

        conn.Open();

        //If you need to read results ...
        using (OracleDataReader dr = cmd.ExecuteReader())
        {
            while (dr.Read())
            {
                ...
            }
        }
        conn.Close();
    }
}

But it is more complicated, since you need to define new types for the stored procedure, like

TYPE integer_list IS TABLE OF Your_table.id_column%TYPE INDEX BY BINARY_INTEGER;
// same for names

create a schema-level type like

create or replace TYPE T_ID_TABLE is table of number; 

And then use them in the Stored Procedure, like

PROCEDURE Your_Procedure_name(
      v_ret IN OUT SYS_REFCURSOR,
      i_idList integer_list,
      i_nameList string_list)
  IS  
  begin
    -- Store passed object id list to array
 idList T_ID_TABLE := T_ID_TABLE(); 
  ...
  begin

    -- Store passed object id list to array
    idList.Extend(i_idList.Count);
    FOR i in i_idList.first..i_idList.last loop
     idList(i) := i_idList(i);
    END LOOP;    

    ...
END Your_Procedure_name;