OracleParameter AssociativeArray bug in ODP.Net 11

Today I was writing some code that was wrapping up stored procedures for an Oracle database. In one situation we were needing to retrieve a result set based on a list of values that we had in memory. Oracle provides a really sweet bit of functionality that allows you to pass an array to an OracleParameter object and have that value passed through to your stored procedure as one parameter value.

I have no idea how this looks or works on the stored procedure side of things, but it’s pretty slick in .Net. All you need to do is create a normal Oracle parameter object, pass the array into the value and set the CollectionType to PLSQLAssociativeArray.

One of the ways that you can create an OracleParameter is to use the constructor overload for (string name, object value). This works fine when you’re creating native typed, single value parameters that are have either input or output directions. It even works great when you’re creating an output cursor type parameter. When you try to use this constructor overload and pass it an array, things go horribly wrong.

Passing the array into that constructor overload causes an Object Reference Not Set exception to be thrown. After a bit of poking around, it became clear that using a different constructor and setting the OracleParameter.Value property to the array would work just fine.

Offending code

string[] myArray = new string[5] { "sdf", "weo", "wdi", "saf", "vbc" };
OracleParameter parameter = new OracleParameter("myParameterName", myArray);

Working code

string[] myArray = new string[5] { "sdf", "weo", "wdi", "saf", "vbc" };
OracleParameter parameter = new OracleParameter("myParameterName", OracleDbType.Varchar2)
parameter.Value = myArray;

I did some looking at the Oracle.DataAccess.dll using Reflector and couldn’t seen an obvious reason for this to be happening, but here’s what I suspect. The constructor is taking in the array and trying to store it in an internal collection of some type. When it attempts to add values to that internal collection during the constructor execution, the internal collection is still set to null. When you run the constructor and subsequently set the Value property, something has initialized that internal collection, eliminating the possibility of this exception occurring.