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

Question

This answer to this question is fine, but I'm looking for ADO.NET code to be able to send an array or table to an Oracle procedure and then use that table in the procedure. In SQL Server table-valued parameters, it's pretty straightforward: ``` CREATE TYPE [dbo].[IntTable] AS TABLE( [intvalue] [int] NOT NULL, PRIMARY KEY CLUSTERED ( [intvalue] ASC )WITH (IGNORE_DUP_KEY = OFF) ) GO CREATE PROCEDURE dbo.UseTable @SomeInt INT ,@IntTable dbo.IntTable READONLY AS BEGIN -- Do whatever using @SomeInt and @IntTable like: INSERT INTO Assignments (masterid, childid) SELECT @SomeInt, intvalue FROM @IntTable END GO ``` Then on the client: ``` var param = new List(); param.Add(1); param.Add(2); Cm.Parameters .AddWithValue("@IntTable", param /* IEnumerable */) .SqlDbType = SqlDbType.Structured ``` This is what I currently have: ``` CREATE OR REPLACE TYPE TRAIT_ID_TABLE AS TABLE OF NUMBER; PROCEDURE SET_TRAITS(P_CUST_TANK_PROD_ID IN CUST_TANK_PROD.CUST_TANK_PROD_ID%TYPE, P_TRAIT_IDS IN TRA…

Accepted Answer

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();
```

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

Solution

Problem