如何通过 ADO.NET/C# 将数组/表参数用于 Oracle (ODP.NET 10g)

这个

问题的答案很好，但我正在寻找 ADO.NET 代码，以便能够将数组或表发送到 Oracle 过程，然后在过程中使用该表。

在SQL Server表值参数中，非常简单：

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

然后在客户端上：

var param = new List<int>();
param.Add(1);
param.Add(2);
Cm.Parameters
    .AddWithValue("@IntTable", param /* IEnumerable<Int> */)
    .SqlDbType = SqlDbType.Structured

这是我目前拥有的：

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 TRAIT_ID_TABLE)
AS
BEGIN
  DELETE FROM TANK_TRAIT
        WHERE CUST_TANK_PROD_ID = P_CUST_TANK_PROD_ID;
  INSERT INTO TANK_TRAIT(CUST_TANK_PROD_ID, TRAIT_ID)
     SELECT P_CUST_TANK_PROD_ID, COLUMN_VALUE FROM TABLE(P_TRAIT_IDS);
  COMMIT;
EXCEPTION
  WHEN OTHERS
  THEN
     ROLLBACK;
END;

var param = new OracleParameter();
param.ParameterName = "P_TRAIT_IDS";
param.OracleDbType = OracleDbType.Decimal;
param.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param.Direction = ParameterDirection.Input;
param.Value = traitIdList.ToArray<int>();
param.Size = traitIdList.Count;
cmd.Parameters.Add(param);

我在ExecuteNonQuery上得到这个：

System.AccessViolationException was caught
  Message=Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
  Source=Oracle.DataAccess
  StackTrace:
       at Oracle.DataAccess.Client.OpsSql.ExecuteNonQuery(IntPtr opsConCtx, IntPtr& opsErrCtx, IntPtr& opsSqlCtx, IntPtr& opsDacCtx, IntPtr opsSubscrCtx, Int32& isSubscrRegistered, OpoSqlValCtx*& pOpoSqlValCtx, OpoSqlRefCtx& pOpoSqlRefCtx, IntPtr[] pOpoPrmValCtx, OpoPrmRefCtx[] pOpoPrmRefCtx, OpoMetValCtx*& pOpoMetValCtx, Int32 prmCnt)
       at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
       at EDC2.Domain.TraitList.SaveTraits(String connectionString) in C:'code'EDC2'trunk'app'EDC2.Domain'Trait.cs:line 195
  InnerException: 

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;

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