c# - 通过.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:错误的参数编号或类型”错误。在这个主题中: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;
/

但是，这仍然产生完全相同的错误。经过更多搜索和尝试后，我发现我需要在“test_type”中添加“INDEX BY BINARY_INTEGER”，并且可以正常工作，因此我可以无错误地调用我的过程。

然后，我开始将原始过程中的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 Invalid data type even after casting why)上找到了以下内容:



我发现somewhere else:



所以我在这里处于两难境地。如果自定义类型没有“INDEX BY”，则无法调用该过程。在程序包中声明该类型时，我无法在查询中使用此类型，并且由于“INDEX BY”。

有人可以帮我吗？我想我需要找到一种在类型没有“INDEX BY”的情况下调用过程的方法，但是我已经尝试了所有我能想到或发现的东西。

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

最佳答案

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

关于c# - 通过.NET调用带有PL/SQL集合类型参数的Oracle过程，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/29605713/