Broken Clone in ODP.Net OracleParameter Class

A great disappointment in ODP.Net. My colleague and me had wasted half day each trying to figure out an weird error from ODP.Net when working with PL/SQL Associated Array.

	System.InvalidCastException: Unable to cast object of type 'System.Int32[]' to 
	type 'System.IConvertible'.
	at System.Convert.ToInt32(Object value)
	at Oracle.DataAccess.Client.OracleParameter.PreBind_Int32()
	at Oracle.DataAccess.Client.OracleParameter.PreBind(OracleConnection conn, IntPtr errCtx, Int32 arraySize)
	at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
	at Oracle.DataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
	at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
	L:\Projects\Spring.NET\src\Spring\Spring.Data\Data\Core\AdoTemplate.cs(3009,0): 
        at Spring.Data.Core.AdoTemplate.QueryCallback.DoInCommand(IDbCommand command)
	L:\Projects\Spring.NET\src\Spring\Spring.Data\Data\Core\AdoTemplate.cs(251,0): 
        at Spring.Data.Core.AdoTemplate.Execute(ICommandCallback action)
	L:\Projects\Spring.NET\src\Spring\Spring.Data\Data\Core\AdoTemplate.cs(758,0): 
        at Spring.Data.Core.AdoTemplate.QueryWithResultSetExtractor(CommandType cmdType, String cmdText, IResultSetExtractor resultSetExtractor, IDbParameters parameters)
	L:\Projects\Spring.NET\src\Spring\Spring.Data\Data\Core\AdoTemplate.cs(647,0): 
        at Spring.Data.Core.AdoTemplate.QueryWithRowCallbackDelegate(CommandType cmdType, String sql, RowCallbackDelegate rowCallbackDelegate, IDbParameters parameters)
	L:\Projects\Spring.NET\src\Spring\Spring.Data\Data\Generic\AdoTemplate.cs(805,0): 
        at Spring.Data.Generic.AdoTemplate.QueryWithRowCallbackDelegate(CommandType cmdType, String sql, RowCallbackDelegate rowCallbackDelegate, IDbParameters parameters)

From the error message, it seems that the CollectionType property was not setup properly, but we did set it in the code below.

            OracleParameter idList = new OracleParameter
            {
                OracleDbType = OracleDbType.Int32,
                CollectionType = OracleCollectionType.PLSQLAssociativeArray,
                Value = new int[] {555, 666, 777},
                Size = 3
            };

            OracleParameter refCursor = new OracleParameter
            {
                OracleDbType = OracleDbType.RefCursor,
                Direction = ParameterDirection.Output
            };

            IDbParameters parameters = AdoTemplate.CreateDbParameters();

            parameters.AddParameter(idList);
            parameters.AddParameter(refCursor);

            AdoTemplate.QueryWithRowCallbackDelegate(
                CommandType.StoredProcedure,
                "p_kentest",
                PrintRow,
                parameters);

Well, yes we use Spring.Net. Could that be the reason? I started a new test project and use ODP.Net directly and it worked!

Back to the code and trace inside the Spring.Net code all the way to ParameterUtils.CopyParameters, our original parameter gets cloned there and CollectionType property is lost after clone. Write a simple test:

            OracleParameter idList = new OracleParameter
            {
                OracleDbType = OracleDbType.Int32,
                CollectionType = OracleCollectionType.PLSQLAssociativeArray,
                Value = new int[] {555, 666, 777},
                Size = 3
            };

            OracleParameter clone = (OracleParameter) idList.Clone();
            Console.Out.WriteLine("CollectionType in original parameter: " + idList.CollectionType);
            Console.Out.WriteLine("CollectionType in cloned parameter: " + clone.CollectionType);
            Console.ReadKey();

The output below verified that the clone is the culprit.

     CollectionType in original parameter: PLSQLAssociativeArray
     CollectionType in cloned parameter: None

What happened in the OracleParameter.Clone? We don't have the source code for this to check, only with the help of Reflector, we found out the poorly implemented Clone method:

        public object Clone()
        {
            if ((this.m_value != null) && this.m_value.GetType().IsArray)
            {
                return new OracleParameter(
                    this.m_dbType, this.m_direction, this.m_nullable, this.m_offset, 
                    this.m_oraDbType, this.m_opoPrmRefCtx.pBindName, this.m_precision, 
                    this.m_scale, this.m_maxSize, this.m_sourceColumn, this.m_sourceVersion, 
                    this.m_status, ((Array)this.m_value).Clone());
            }
            return new OracleParameter(
                    this.m_dbType, this.m_direction, this.m_nullable, this.m_offset, 
                    this.m_oraDbType, this.m_opoPrmRefCtx.pBindName, this.m_precision, 
                    this.m_scale, this.m_maxSize, this.m_sourceColumn, this.m_sourceVersion, 
                    this.m_status, this.m_value);
        }

Given another thought, why does Spring.Net have to make a clone when setting the parameters? I checked all the usage of the CopyParameters method, there isn't a need for async, neither parameters are reused later. Am I missing something here? Update (5/2/2009): Yes, I was missing something. This is because

1. A parameter is only allowed to be added to one parameter collection, this is true for SQL client and both System.Data and ODP.Net Oracle client.
2. Spring.Net's implementation of IDdParameters uses parameter collection from an IDbCommand of various ADO clients. So as soon as the parameter is added to IDbParameters, it is in a parameter collection thus cannot be added to any new command.

But IDbParameters is just an parameter hold, its underplaying parameter collection never bounds to a real command, so can Spring.Net implement its own or simply do not use IDataParameterCollection? Probably, but will that work? The answer is unfortunately no.