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);
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();
}
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;
/
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.
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);
But now I cannot build my package anymore. I found the following on 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.
And somewhere else I found:
Index by tables cannot be declared globally; the following construct generates a PLS-00355: use of pl/sql table not allowed in this context.
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".
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. 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).
Best Answer
Procedure call via ODP.NET supports only Associative arrays, i.e. with
INDEX BY ...
, Nested Tables are not supported.One solution is to convert in in your Orale procedure:
For DML statements consider also this: