我试图通过.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();
}
所以我创建了一个如下所示的测试包:
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);
PL/SQL types defined in package are invisible to SQL statements: they are pure PLSQL constructs and the SQL language can’t access them directly.
Index by tables cannot be declared globally; the following construct generates a PLS-00355: use of pl/sql table not allowed in this context.
所以我在这里陷入两难境地.如果自定义类型没有“INDEX BY”,我无法调用该过程,当我在包中声明它时,我不能在查询中使用此类型,并且由于“我不能全局声明它”索引“.
任何人都可以帮我吗?我想我需要找到一种方法来在类型没有“INDEX BY”时调用该过程,但我已经尝试了我能想到或找到的所有内容.
PS.我使用的是.NET 4.5和Oracle.ManagedDataAccess v 4.121.1.0,不幸的是,我们的Oracle数据库仍然是10g(10.2.0.4.0).