如何通过 ADO.NET/C# 将数组/表参数用于 Oracle (ODP.NET 10g)？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10217922/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use Array/Table Parameter to Oracle (ODP.NET 10g) via ADO.NET/C#?
提问by Cade Roux
This answerto this questionis 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.
这个答案对这个问题很好，但是我正在寻找ADO.NET代码能够数组或表发送到Oracle程序，然后使用该表的过程。
In SQL Server table-valued parameters, it's pretty straightforward:
在 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
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:
我在 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:
采纳答案by tbone
This works for ODP.NET (odac):
这适用于 ODP.NET (odac)：
Your Oracle package will be setup like:
您的 Oracle 包将设置为：
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:
那么您的 C# 代码将如下所示：
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();
