如何通过 ADO.NET/C# 将数组/表参数用于 Oracle (ODP.NET 10g)? [英] How to use Array/Table Parameter to Oracle (ODP.NET 10g) via ADO.NET/C#?

问题描述

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

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.

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

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

然后在客户端:

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 上得到了这个:

And I get this on the 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:codeEDC2	runkappEDC2.DomainTrait.cs:line 195
  InnerException: 

推荐答案

这适用于 ODP.NET (odac):

This works for ODP.NET (odac):

您的 Oracle 软件包将被设置为:

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;

那么您的 C# 代码将如下所示:

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

这篇关于如何通过 ADO.NET/C# 将数组/表参数用于 Oracle (ODP.NET 10g)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！