通过.NET调用带有PL/SQL集合类型参数的Oracle过程

Question

我正试图通过.NET调用Oracle存储过程。通常这不是问题，但此存储过程包含一个PL/SQL集合类型的参数：

create or replace type test_type as table of number;
PROCEDURE TEST1 (pvTest IN test_type);

这是我的C#代码：

var receiverIds = new decimal[] { 683552, 683553, 683572, 683573, 683592, 683593, 683594, 683612 };
var receiversList = new OracleParameter("pvTest", OracleDbType.Decimal, ParameterDirection.Input);
receiversList.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
receiversList.Size = receiverIds.Length;
receiversList.Value = receiverIds;
using (var oracleCommand = new OracleCommand())
{
oracleCommand.Connection = this.oracleConnection;
oracleCommand.CommandText = "test_package.TEST1";
oracleCommand.BindByName = true;
oracleCommand.Parameters.Add(parameter);
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.ExecuteNonQuery();
}

当我执行此操作时，我会得到"ORA-06550:参数的数量或类型错误"错误。在本主题中：ORA-06550:参数数量或类型错误错误错误|使用表类型In参数调用Oracle过程我发现我应该在包中声明我的自定义类型。

所以我创建了一个测试包，看起来像这样：

CREATE OR REPLACE PACKAGE test_package_gkeu IS
TYPE test_type IS TABLE OF NUMBER;
PROCEDURE TEST1 (pvTest IN test_type);
END test_package_gkeu;
/
CREATE OR REPLACE PACKAGE BODY test_package_gkeu IS
PROCEDURE TEST1 (pvTest IN test_type) IS
BEGIN
null;
END TEST1;
END test_package_gkeu;
/

然而，这仍然产生了完全相同的错误。经过更多的搜索和尝试，我发现我需要将"INDEX BY BINARY_INTEGER"添加到"test_type"中，这很有效，有了它，我可以毫无错误地调用我的过程。

然后，我开始将原始过程中的SQL查询添加到这个测试包中：

select *
from receiver r
where r.receiverid in (select /*+cardinality(t 5)*/ *
from table(cast((pvTest) as test_type)) t
where rownum >= 0);

但现在我不能再构建我的包了。我在StackOverflow上发现了以下内容(PlSQL无效数据类型，即使在强制转换原因之后也是如此)：

包中定义的PL/SQL类型对SQL语句是不可见的：它们是纯PLSQL构造，SQL语言不能直接访问它们。

还有我发现的其他地方：

不能全局声明表索引；以下构造生成PLS-00355:在此上下文中不允许使用pl/sql表。

所以我在这里进退两难。如果自定义类型没有"INDEX BY"，我就不能调用该过程，当我在包中声明它时，我不能在查询中使用该类型，并且由于"INDEX BY"，我不能全局声明它。

有人能帮我吗？当类型没有"INDEX BY"时，我想我需要找到一种方法来调用过程，但我已经尝试了我能想到或找到的一切。

ps。我使用的是.NET 4.5和Oracle.ManagedDataAccess版本4.121.1.0，不幸的是，我们的Oracle数据库仍然是10g(10.2.0.4.0)。

Answer 1

通过ODP.NET进行的过程调用仅支持关联数组，即对于INDEX BY ...，不支持嵌套表。

一种解决方案是在Orale程序中转换：

CREATE OR REPLACE PACKAGE test_package_gkeu IS
TYPE test_type IS TABLE OF NUMBER;    
TYPE test_type_associative IS TABLE OF NUMBER INDEX BY INTEGER;
PROCEDURE TEST1 (pvTest IN test_type_associative ) IS
v test_type := test_type();
BEGIN
v.Extend(pvTest.COUNT);
for i in pvTest.First..pvTest.Last loop
v(i) := pvTest(i)
end loop;
select *
into ...
from receiver r
where r.receiverid MEMBER OF (v);
END;

对于DML声明，还应考虑以下内容：

FORALL i IN INDICES OF pvTest 
INSERT INTO MY_TABLE (COL_A)
VALUES (pvTest(i));
or 
FORALL i IN INDICES OF pvTest 
DELETE FROM receiver 
WHERE receiverid  = pvTest(i);