通过 .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# 代码:

This is my C# code:

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

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

然而，这仍然产生了完全相同的错误.经过更多的搜索和尝试后，我发现我需要将INDEX BY BINARY_INTEGER"添加到test_type"并且可以正常工作，这样我就可以毫无错误地调用我的程序.

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 Invalid data type even after cast why):

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"，我将无法调用该过程，当我在包中声明它时，我无法在查询中使用此类型，并且由于索引".

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屋！