Development With A Dot

Blog on development in general, and specifically on .NET

Sponsors

News

My Friends

My Links

Permanent Posts

Portuguese Communities

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

}

Comments

ayumon said:

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)

# August 18, 2009 11:40 AM

Ricardo Peres said:

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.

# August 19, 2009 5:56 AM

Ricardo Peres said:

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.

# September 10, 2009 6:55 AM

Gautam said:

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

# July 8, 2010 8:10 AM

Ricardo Peres said:

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!

# July 8, 2010 9:42 AM