Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
329 views
in Technique[技术] by (71.8m points)

c# - Calling an Oracle procedure with a PL/SQL collection type parameter via .NET

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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:

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;

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...