Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Passing .NET array as Oracle Collection, IN parameter to stored proc

784188Jul 9 2010 — edited Jul 13 2010
I'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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 10 2010
Added on Jul 9 2010
5 comments
17,326 views