如何使用磁盘阵列/表通过ADO.NET/C#参数到Oracle（ODP.NET 10G）？ [英] How to use Array/Table Parameter to Oracle (ODP.NET 10g) via ADO.NET/C#?

问题描述

这个答案以<一href="http://stackoverflow.com/questions/5868234/in-sql-server-2008-i-am-able-to-pass-table-valued-parameter-to-my-stored-procedu">this问题是不错，但我在寻找ADO.NET code，以便能够在过程中发出的数组或表到Oracle程序，然后使用该表。

在SQL Server的表值参数，它是pretty的简单：

  CREATE TYPE [DBO]。[IntTable]如表（
    [的intValue] [INT] NOT NULL，
    PRIMARY KEY CLUSTERED
    （
        [的intValue] ASC
    ）和（IGNORE_DUP_KEY = OFF）
）
走

CREATE PROCEDURE dbo.UseTable
    @SomeInt INT
    @ IntTable dbo.IntTable READONLY
如
开始
     - 做任何使用@SomeInt和@IntTable，如：
    INSERT INTO分配（masterid，childid）
    SELECT @SomeInt，的intValue
    从@IntTable
结束
走
 

然后在客户端上：

  VAR参数=新的名单，其中，INT＆GT;（）;
param.Add（1）;
param.Add（2）;

Cm.Parameters
    .AddWithValue（@ IntTable，参数/ * IEnumerable的＆LT;诚信＆GT; * /）
    .SqlDbType = SqlDbType.Structured
 

这是我目前有：

  CREATE OR REPLACE TYPE TRAIT_ID_TABLE如表数字;

程序SET_TRAITS（P_CUST_TANK_PROD_ID在CUST_TANK_PROD.CUST_TANK_PROD_ID％型，TRAIT_ID_TABLE P_TRAIT_IDS）
如
开始
  DELETE FROM TANK_TRAIT
        WHERE CUST_TANK_PROD_ID = P_CUST_TANK_PROD_ID;

  INSERT INTO TANK_TRAIT（CUST_TANK_PROD_ID，TRAIT_ID）
     选择P_CUST_TANK_PROD_ID，COLUMN_VALUE FROM TABLE（P_TRAIT_IDS）;

  承诺;
例外
  WHEN OTHERS
  然后
     ROLLBACK;
结束;


VAR参数=新的OracleParameter（）;
param.ParameterName =P_TRAIT_IDS;
param.OracleDbType = OracleDbType.Decimal;
param.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param.Direction = ParameterDirection.Input;
param.Value = traitIdList.ToArray＆LT;诠释＆GT;（）;
param.Size = traitIdList.Count;
cmd.Parameters.Add（参数）;
 

和我得到这个对的ExecuteNonQuery：

  System.AccessViolationException被抓
  消息=试图读取或写入受保护的内存。这通常表明其他内存已损坏。
  来源= Oracle.DataAccess
  堆栈跟踪：
       在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）
       在Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery（）
       在EDC2.Domain.TraitList.SaveTraits（字符串的connectionString）在C：\ code \ EDC2 \干线\程序\ EDC2.Domain \ Trait.cs：行195
  的InnerException：
 

解决方案

这适用于ODP.NET（ODAC）：

像

您的Oracle软件包将被设置：

  CREATE OR REPLACE包SOME_PACKAGE作为
  ...
  键入t_number_tab是桌子PLS_INTEGER数量指标;
  ...
  程序ins_test（i_id_tab在t_number_tab，o_inserted出数）;

结束SOME_PACKAGE;


CREATE OR REPLACE包体SOME_PACKAGE作为

    程序ins_test（i_id_tab在t_number_tab，o_inserted出数）
    开始
         - 所有记录测试表中插入基于ID的传入表
        FORALL我i_id_tab.first .. i_id_tab.last
            插入TEST_TAB
            （ID，VAL1，将val2）
            选择ID，VAL1，将val2
            从main_tab
            其中id = i_id_tab（ⅰ）;

        o_inserted：= SQL％ROWCOUNT;

        承诺;
    例外
        其他人则当
            回滚;
            提高;
    结束ins_test;
...
结束SOME_PACKAGE;
 

这时你的C＃code看起来像：

 字符串connStr =用户ID = XXX;密码= XXXX;数据源= XXXXX;;
为OracleConnection _conn =新的OracleConnection（connStr）;
_conn.Open（）;

的OracleCommand CMD = _conn.CreateCommand（）;
cmd.CommandText =some_package.ins_test;
cmd.CommandType = CommandType.StoredProcedure;

的OracleParameter P1 =新的OracleParameter（）;
的OracleParameter P2 =新的OracleParameter（）;

p1.OracleDbType = OracleDbType.Decimal;
p1.Direction = ParameterDirection.Input;
p2.OracleDbType = OracleDbType.Decimal;
p2.Direction = ParameterDirection.Output;

p1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
p1.Value =新INT [3] {1，2，3};
p1.Size = 3;

cmd.Parameters.Add（p1）为;
cmd.Parameters.Add（P2）;

cmd.ExecuteNonQuery（）;
 

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<int>();
param.Add(1);
param.Add(2);

Cm.Parameters
    .AddWithValue("@IntTable", param /* IEnumerable<Int> */)
    .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 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);

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:\code\EDC2\trunk\app\EDC2.Domain\Trait.cs:line 195
  InnerException: 

解决方案

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

这篇关于如何使用磁盘阵列/表通过ADO.NET/C#参数到Oracle（ODP.NET 10G）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！