ODP.NET

This one might have to be a suggestion for future development, but if anyone shows me how to do this it would be great....


I've been working with some stuff similar on the example on PL/SQL Associative Array Binding in the Developer's guide for ODP.NET 10.2 - here's the VB code

Imports Oracle.DataAccess

Dim myConn As New Client.OracleConnection
Dim myCommand As Client.OracleCommand

myConn.ConnectionString = *<enter your connection string here>*
myConn.Open()

myCommand = New Client.OracleCommand("MY_PKG.MYyProc", myConn)
myCommand.CommandType = CommandType.StoredProcedure
myCommand.BindByName = True

Dim Param1 As Client.OracleParameter = myCommand.Parameters.Add("Param1", Client.OracleDbType.Varchar2)

Param1.Direction = ParameterDirection.Input

Param1.CollectionType = Client.OracleCollectionType.PLSQLAssociativeArray
Param1.value=*<enter your array here>*

Ok, this works find if you put it an ordinary .net array such as new string(2) {"first","second", "third"}. However, this isn't really an associative array as I'm used to the term. I would like to access the parameters at the oracle end by name rather than by number.

I realise that .NET doesn't possess an associative array type, but it does possess objects such as collections and dictionaries which serve much the same purpose. However, you can't use these as the parameter value, because when you come to the step that executes the code, you get something like "System.InvalidCastException: Unable to cast object of type 'System.Collections.Generic.Dictionary`2[System.String,System.String]' to type 'System.Array'"

Does anyone know how to get round this? It would be really good if so.

Also, as another suggestion for future development - it would be good to extend beyond associative arrays and maybe use system defined types as the parameter type, e.g.

CREATE OR REPLACE
TYPE "MY_TYPE" AS OBJECT
(
MY_ID NUMBER,
MY_NAME VARCHAR2(40),
MY_STATUS NUMBER
);
/