Passing .NET array as Oracle Collection, IN parameter to stored proc
784188Jul 9 2010 — edited Jul 13 2010I've been trying for a long time to pass a collection into a stored procedure through ODP.NET 11g. First, I tried simply sending an array of strings, as oracle's documentation says this is the default type mapping. That gave me an error, and after trying many other things, I came across a tutorial and used the wizard to create a custom type based on ETI_TT, the collection type shown below.
I get an exception, "Custom type mapping for 'Application.Internal.ETI_TT' is not specified or is invalid.", and I'm not sure why. The type ODP generated clearly implements the IOracleCustomType interface. Does anyone know how to fix this?
/*
-- IN APP SCHEMA
Type ETI_TT AS TABLE OF VARCHAR(20);
*/
/*
-- IN PACKAGE APP.GET_DATA
Type RefC IS REF CURSOR;
Procedure GET_DATA_ETIS(i_etiArray ETI_TT, o_data OUT RefC);
*/
namespace Application.Internal {
class Testing {
function void test() {
DataTable results = new DataTable();
using (OracleConnection connection = new OracleConnection(connectionString))
using (OracleCommand cmd = new OracleCommand("APP.GET_DATA.GET_DATA_ETIS", connection))
using (OracleDataAdapter adapter = new OracleDataAdapter(cmd))
{
connection.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.BindByName = true;
var etiColl = cmd.Parameters.Add("i_etiArray", OracleDbType.Object);
etiColl.Value = new ETI_TT { Value = etis.ToArray() };
etiColl.Direction = ParameterDirection.Input;
etiColl.UdtTypeName = "APP.ETI_TT";
cmd.Parameters.Add("o_Data", OracleDbType.RefCursor, ParameterDirection.Output);
adapter.Fill(results);
}
}
}
}
Thanks,
Jason