问题描述
我正在尝试通过.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):
在其他地方我发现:
所以我在这里陷入了困境.如果自定义类型没有"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过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!