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 AS
TYPE
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();
}