Pass an array of objects in a variable to a stored procedure through Oracle.ManagedDataAccess.dll

Question

With the latest Oracle Managed drivers, there is no support for Oracle.DataAccess.Types.OracleUdt and OracleDbType.Array I want to pass an array of integers and retrieve corresponding data from a stored procedure.

List<OracleParameter> lstParams = new List<OracleParameter>()
new OracleParameter(){ParameterName = "PARAM1", Value = new Int32[]{1,2,3}, Direction = ParameterDirection.Input, CollectionType = OracleCollectionType.PLSQLAssociativeArray, Size = 3},
new OracleParameter(){ParameterName = "OUT_CUR", Value = null, OracleDbType = OracleDbType.RefCursor, Direction = ParameterDirection.Output}
};

using (OracleCommand cmd = new OracleCommand() { CommandText = "MANAGED_DEMO_TEST", Connection = OracleConnectionFactory.GetInstance(), CommandType = System.Data.CommandType.StoredProcedure})
{
cmd.Parameters.AddRange(lstParams.ToArray());
//Getting exception at this line
reader = cmd.ExecuteReader();
}

In Oracle, I am creating a custom type and using it in the procedure.

CREATE OR REPLACE TYPE CUSTOM_ID IS TABLE OF NUMBER;
create or replace PROCEDURE MANAGED_DEMO_TEST(
PARAM1 IN CUSTOM_ID,
OUT_CUR out sys_refcursor
) 
AS BEGIN
open out_cur for
--Random select statement to test the execution of SP
SELECT * from my_table where id = 1;
END;

Currently I am getting an exception: PLS-00306: wrong number or types of arguments in call to 'MANAGED_DEMO_TEST'

The main intention is to pass an array of custom type objects to a stored procedure. I have tried this solution, but did not work for me. Got the same exception.

Answer 1

I think you must use an "Associative array" rather than a "Nested table".

Instead of

CREATE OR REPLACE TYPE CUSTOM_ID IS TABLE OF NUMBER; 

you have to use

TYPE CUSTOM_ID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

However, you cannot create an Associative array as schema objects (i.e. CREATE OR REPLACE TYPE CUSTOM_ID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER does not work). You have to define the Associative array in your Package, perhaps you have to convert it into a nested table if required.