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

}

                             

5 Comments

  • Question: what is the purpose of indexing the table?
    TYPE AssocArrayVarchar2_t IS TABLE OF VARCHAR2(20) [[[[INDEX BY BINARY_INTEGER]]]];

    i noticed if the index is not applied the c# code will throw exception (wrong number of argument or type)

  • ayumon,

    This way you can pass any number of arguments into a stored procedure. The problem is that in .NET, ODP.NET only lets you pass the arguments as an array, whether in Oracle it lets you pass something like a Dictionary.

  • Rodrigo,
    Are you declaring your associative array exacly like this AssocArrayVarchar2_t IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER?
    Remember, you can only index it by BINARY_INTEGER.

  • Did you find any way to use PLSQLAssociativeArray using ODP.Net for following example?



    TYPE MyRecord IS RECORD (FieldA NUMBER, FieldB NUMBER);
    TYPE MyArray IS TABLE OF MyRecord INDEX BY BINARY_INTEGE

  • Gautam:

    Sorry, never tried... also, for the moment, I'm not using Oracle, so I can't help you... Have you tried StackOverflow? Good luck!

Comments have been disabled for this content.