通过.NET调用带有PL/SQL集合类型参数的Oracle过程 [英] Calling an Oracle procedure with a PL/SQL collection type parameter via .NET

问题描述

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

I'm trying to call an Oracle stored procedure via .NET. Normally this isn't a problem, but this stored procedure contains a parameter that is a PL/SQL collection type:

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 Procedure 我发现我应该在包中声明我的自定义类型.

When I execute this, I get the "ORA-06550: Wrong number or type of arguments" error. In this topic: ORA-06550: Wrong number or type of arguments error | Calling Oracle Procedure with Table type IN parameter I found that I should declare my custom type inside my package.

因此，我创建了一个如下所示的测试包:

So I created a test package that looks like this:

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"，并且可以正常工作，因此我可以无错误地调用我的过程.

However, this still produced the exact same error. After some more searching and trying I found that I need to add "INDEX BY BINARY_INTEGER" to "test_type" and that works, with this I can call my procedure without errors.

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

Then I started adding the SQL query from the original procedure to this test package:

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无效的数据类型为何):

But now I cannot build my package anymore. I found the following on StackOverflow (PlSQL Invalid data type even after casting why):

包中定义的PL/SQL类型对SQL语句不可见:它们是纯PLSQL构造，并且SQL语言无法直接访问它们.

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.

在其他地方我发现:

表索引不能全局声明；以下结构生成PLS-00355:在此情况下不允许使用pl/sql表.

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".

So I'm in a dilemma here. I can't call the procedure if the custom type doesn't have an "INDEX BY", I can't use this type in a query when I declare it in the package and I can't declare it globally due to the "INDEX BY".

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

Could anyone help me out? I guess I need to find a way to call the procedure when the type doesn't have an "INDEX BY", but I've tried everything I could think of or find.

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

ps. I'm using .NET 4.5 and Oracle.ManagedDataAccess v 4.121.1.0 and our Oracle database is, unfortunately, still 10g (10.2.0.4.0).

推荐答案

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

Procedure call via ODP.NET supports only Associative arrays, i.e. with INDEX BY ..., Nested Tables are not supported.

一种解决方案是在您的Orale过程中进行转换:

One solution is to convert in in your Orale procedure:

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语句，还请考虑以下事项:

For DML statements consider also this:

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

这篇关于通过.NET调用带有PL/SQL集合类型参数的Oracle过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！