OracleParameter array type

[deniscpereira]

Hi

I ran the stored-procedure example passing the OracleParameter parameter towards the input and I was successful.

When trying to send an array as a parameter I am not successful. Could you help me find the problem.

var arrayParam = new OracleParameter
{
     ParameterName = "list",
     Value = new long[2] { 1, 37 },
     OracleDbType = OracleDbType.Int64,
     Direction = ParameterDirection.Input,
     CollectionType = OracleCollectionType.PLSQLAssociativeArray // I need to add this
};

var idReturn = new OracleParameter
{
     ParameterName = "idNumberReturn",
     OracleDbType = OracleDbType.Int64,
     Direction = ParameterDirection.Output
};

string sql = "BEGIN PTEST(:list, :idNumberReturn); END;"
var teste = await _db.Database.ExecuteSqlRawAsync(sql, new object[] { arrayParam, idReturn });

Parameters de stored procedure

create PROCEDURE PTEST (
    ID_ARRAY        IN ARRAY_NUMBER_TYPE,
    ID_OUT             OUT NUMBER
)...

When I add CollectionType into parameter return error PLS-00306: incorrect number of argument types in the call to.

If you remove CollectionType in the parameter. System.InvalidCastException parameter return error: Cannot throw object of type 'System.Int64 []' to type 'System.IConvertible'.

What is the correct way to send array as parameter?

Use .net core and ef core 3.1

[alexkeh]

I don't see the code where the parameters are bound. Make sure to set BindByName to true as well when you do bind. I also don't see a Size (i.e. number of elements) specified for the array.

Here's a PL/SQL associative array C# code sample that you can reference.

[deniscpereira]

@alexkeh Thanks for the answer.

I'm using EF Core context and I don't have OracleCommand.

My example was based on this Exemple .net core How could I add an array in the procedure parameter according to this example.

CollectionType = OracleCollectionType.PLSQLAssociativeArray is required?

[alexkeh]

You can't use Oracle specific data types in EF Core. Currently, EF Core only supports .NET Core types IIRC.

You can use an associative array with ODP.NET Core and manipulate data with EF Core objects. You just can't have EF Core operate directly on the associative array.

[deniscpereira]

@alexkeh Thanks for the reply and the help.

I used the following approach it worked.

string sql = $"BEGIN PTEST(ARRAY_NUMBER_TYPE({ String.Join(", ", list) }), :idNumberReturn); END;"