How to pass an array Oracle parameter in c#

Question

I have a problem passing an array parameter using c#, my code is:

private OracleDataAdapter da;
private OracleConnection cnn;
public DataTable select_ids_between_friends(int cod, List<int> excludeList) {
    DataTable dt = new DataTable();
    try
    {
        using (cnn = new OracleConnection(Properties.Settings.Default.connectionString1))
        {
            cnn.Open();
            da = new OracleDataAdapter("PROC_SELECT_IDS_BT_FRIENDS", cnn);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.Add("CURSOR", OracleDbType.RefCursor, ParameterDirection.Output);
            da.SelectCommand.Parameters.Add("COD_US", OracleDbType.Int32, cod, ParameterDirection.Input);
            da.SelectCommand.Parameters.Add(new OracleParameter("IDS_FRIEND", OracleDbType.Int32));
            da.SelectCommand.Parameters[2].CollectionType = OracleCollectionType.PLSQLAssociativeArray;
            da.SelectCommand.Parameters[2].Value = excludeList.ToArray();
            da.SelectCommand.Parameters[2].Size = excludeList.Count;
            da.Fill(dt);
            cnn.Close();
        }
    }
    catch (Exception ex)
    {

    }
    return dt;
}

in the database I created the array and the stored procedure like this: This is the Oracle type:

CREATE TYPE FACEBOOK.ARRAY_ID_FRIENDS AS TABLE OF INT;

Finally this is my procedure:

CREATE OR REPLACE PROCEDURE PROC_SELECT_IDS_BT_FRIENDS
(DATA_CURSOR OUT SYS_REFCURSOR,COD_US IN INT, IDS_FRIEND IN FACEBOOK.ARRAY_ID_FRIENDS) IS
BEGIN
    OPEN DATA_CURSOR FOR 
    SELECT ID_USER1,ID_USER2 FROM T_FRIENDSHIP WHERE ID_USER2 NOT IN(SELECT COLUMN_VALUE FROM TABLE(IDS_FRIEND)))
END;
/

The error showed is PLS-00306: wrong number or types of arguments in call PROC_SELECT_IDS_BT_FRIENDS. Please help me. Thanks in advance.

JustAPup JustAPup · Accepted Answer · 2016-01-13T20:56:30

This line:

da.SelectCommand.Parameters.Add(new OracleParameter("IDS_FRIEND", OracleDbType.Int32));

Why is it an Int32? Shouldn't it be an array? I see that you declare it as an array, but only till later. Is there a different way to declare it?

How to pass an array Oracle parameter in c#

1 Answers