c# - 通过 .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: 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);

但现在我无法再构建我的包了。我在 StackOverflow ( PlSQL Invalid data type even after casting why ) 上找到了以下内容:

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.

和somewhere else我发现:

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”时调用该过程，但我已经尝试了所有我能想到或找到的方法。

附言。我正在使用 .NET 4.5 和 Oracle.ManagedDataAccess v 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);