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

我试图通过.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: Wrong number or type of arguments"错误 . 在本主题中:ORA-06550: Wrong number or type of arguments error | Calling Oracle Procedure with Table type IN parameter我发现我应该在我的包中声明我的自定义类型 .

所以我创建了一个如下所示的测试包:

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

但现在我不能再 Build 我的包了 . 我在StackOverflow(PlSQL Invalid data type even after casting why)上找到了以下内容:

包中定义的PL / SQL类型对SQL语句是不可见的:它们是纯PLSQL结构,SQL语言不能直接访问它们 .

并且somewhere else我发现:

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

所以我在这里陷入两难境地 . 如果自定义类型没有“INDEX BY”,我无法调用该过程,当我在包中声明它时,我不能在查询中使用此类型,并且由于“我不能全局声明它”索引“ .

任何人都可以帮我吗?我想我需要找到一种方法来在类型没有“INDEX BY”时调用该过程,但我已经尝试了我能想到或发现的一切 .

PS . 我使用的是.NET 4.5和Oracle.ManagedDataAccess v 4.121.1.0,遗憾的是,我们的Oracle数据库仍然是10g(10.2.0.4.0) .

回答(1)

3 years ago

通过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);