How to use Array/Table Parameter to Oracle (ODP.NET 10g) via ADO.NET/C#?

This works for ODP.NET (odac):

Your Oracle package will be setup like:

CREATE OR REPLACE package SOME_PACKAGE as  ...  type t_number_tab is table of number index by pls_integer;  ...  procedure ins_test(i_id_tab in t_number_tab, o_inserted out number);end SOME_PACKAGE;CREATE OR REPLACE package body SOME_PACKAGE as    procedure ins_test(i_id_tab in t_number_tab, o_inserted out number) is    begin        -- inserts all records to test table based on incoming table of ids        forall i in i_id_tab.first .. i_id_tab.last            insert into TEST_TAB            (id, val1, val2)            select id,val1,val2            from main_tab            where id = i_id_tab(i);        o_inserted := SQL%ROWCOUNT;        commit;    exception        when others then            rollback;            raise;    end ins_test;...end SOME_PACKAGE;

Then your C# code would look like:

string connStr = "User Id=xxx;Password=xxxx;Data Source=xxxxx;";OracleConnection _conn = new OracleConnection(connStr);_conn.Open();OracleCommand cmd = _conn.CreateCommand();cmd.CommandText = "some_package.ins_test";cmd.CommandType = CommandType.StoredProcedure;OracleParameter p1 = new OracleParameter();OracleParameter p2 = new OracleParameter();p1.OracleDbType = OracleDbType.Decimal;p1.Direction = ParameterDirection.Input;p2.OracleDbType = OracleDbType.Decimal;p2.Direction = ParameterDirection.Output;p1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;p1.Value = new int[3] { 1, 2, 3 };p1.Size = 3;cmd.Parameters.Add(p1);cmd.Parameters.Add(p2);cmd.ExecuteNonQuery();