ODP.NET Associative Arrays
Through ODP.NET you can use Oracle's Associative Array feature. Unfortunately, there is a limitation: you cannot have associative arrays with an index type other that number.
If you have this stored procedure:
PACKAGE MyPackage ASTYPE AssocArrayVarchar2_t IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
PROCEDURE MyStoredProcedure(
Param1 IN AssocArrayVarchar2_t);
END MyPackage;
You can use this code to call it:
using (OracleConnection con = new OracleConnection(@"DATA SOURCE=(DESCRIPTION=(ADDRESS=(COMMUNITY=MyCommunity)(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))(CONNECT_DATA=(SID=MySid)));USER ID=MyUsername;PASSWORD=MyPassword;Promotable Transaction=local"))using (OracleCommand cmd = con.CreateCommand()){
con.Open();
cmd.CommandText = @"MyPackage.MyStoredProcedure";cmd.CommandType = CommandType.StoredProcedure;
OracleParameter param1 = cmd.Parameters.Add("param1", OracleDbType.Varchar2, ParameterDirection.Input);param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param1.Value = new String[] { "First Element", "Second Element", "Third Element" };
cmd.ExecuteNonQuery();
}