ODP.NET

Hi!

I have a stored procedure with associative array parameter which constructs the SQL statement and opens and returns a sys_refcursor with it binding an array parameter.
When I call the stored procedure I got not all records in C#, but calling from PL/SQL block everything OK. When I decrease the number of the selected fields in select statement the result will be OK from C# too (eg. "SELECT T1.FIELD1, T1.FIELD2" in the following SP). It seems to depend on amount of data.

Can anybody know what can be the reason of this? I think it can be a ODP.NET bug.

I use Oracle Database Server 10.2.0 and the version of Oracle.DataAccess from ODP.NET is 2.102.2.20.

The code of the stored procedure is similar as the following:

create or replace PROCEDURE SP1
( cur_out OUT SYS_REFCURSOR
, p_TM_TYPE_ID IN PCK01.ASSOC_ARRAY_OF_NUMBER DEFAULT PCK01.EMPTY_ASSOC_ARRAY_OF_NUMBER
, p_FIELD1 IN NUMBER DEFAULT NULL
) AS
S VARCHAR2(4000);
BEGIN
S:='SELECT T1.FIELD1, T1.FIELD2, ...T1.FIELD17, T3.FIELD_1, T3.FIELD_2, ... T3.FIELD11 '||
'FROM (SELECT DISTINCT T0.FIELD1 '||
'FROM TABLE_0 T0 WHERE 1=1 AND T0.FIELD2=12 AND ((1=1) OR (1=1))'||
') T0 INNER JOIN TABLE_1 T1 ON T1.FIELD_1=T0.FIELD_1 '||
'LEFT OUTER JOIN (SELECT TM1.FIELD17, TM1.FIELD_1, TM1.FIELD_2, ... TM1.FIELD8, TM2.FIELD10, TM2.FIELD11, TM3.FIELD12 '||
'FROM TABLE_M1 TM1 '||
' INNER JOIN TABLE_TM2 TM2 ON TM2.ID=TM1.ID AND TM2.TYPE_ID IN (select * from TABLE(:tm_ids)) '||
' INNER JOIN TABLE_TM3 TM3 ON TM3.S_ID=TM2.S_ID) TM1 '||
' ON TM1.MO_ID=T1.FIELD1 AND TM1.MO_TYPE_ID=2 '||
' WHERE 1=1 ';
OPEN cur_out FOR S USING p_TM_TYPE_ID;
END SP1;


The C# code is the following:

using (OracleConnection conn = new OracleConnection("Data Source=dbserver/db;User ID=user;Password=pwd;Enlist=false"))
{
OracleCommand cmd = new OracleCommand();
cmd.BindByName = true;
cmd.Connection = conn;

cmd.CommandText = "SP1";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("cur_out", OracleDbType.RefCursor, ParameterDirection.Output);
cmd.Parameters.Add("p_FIELD1", OracleDbType.Decimal, ParameterDirection.Input).Value = 466;

int[] typeIds = new int[3] { 1, 2, 3 };
OracleParameter OP = new OracleParameter("p_TM_TYPE_ID", OracleDbType.Decimal, ParameterDirection.Input);
OP.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
OP.Value = typeIds;
OP.Size = typeIds.Length;
cmd.Parameters.Add(OP);

OracleDataAdapter od = new OracleDataAdapter(cmd);
DataSet DS = new DataSet();
od.Fill(DS);
Console.WriteLine(DS.Tables[0].Rows.Count);
}

Thank you.