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 · Accepted Answer · 2016-01-13 20:56:30Z

1

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?

answered Jan 13, 2016 at 20:56

JustAPup

1,79013 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

lgerras84 Over a year ago

I read in other web page that They declare first of all the type of the content in this case my array is a list of int32. After that they modify the parameter using this code:

lgerras84 Over a year ago

I read in other website. They declared the type of the single element of the list and after they modified using da.SelectCommand.Parameters[2].CollectionType = OracleCollectionType.PLSQLAssociativeArray

Collectives™ on Stack Overflow

How to pass an array Oracle parameter in c#

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related