PLSQL associative array size limit in parameters

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
chrsadk
Posts: 3
Joined: Fri 09 Sep 2011 10:22

PLSQL associative array size limit in parameters

Post by chrsadk » Wed 18 Sep 2013 09:40

Hi,

I'm encountering a problem in dotConnect for Oracle 5.0.1.0, which i have also confirmed to be in the latest express edition.

I have the stored procedure:

Code: Select all

TYPE REF_T IS TABLE OF number INDEX BY BINARY_INTEGER;
PROCEDURE TESTPROC(p_list    IN REF_T,
                  p_cursor  OUT SYS_REFCURSOR)
which simply returns the input list in a ref custor (e.g. if p_list contains 1,2,3 - then p_cursor will also contain 1,2,3)

I execute it with the following code:

Code: Select all

var count = 65539; //max seems to be 65536 = 2^16
var list = new int[count];            
for (var i = 0; i < count; i++)
{
  list[i] = i;                
}

using (var con = new OracleConnection(conStr))
{
  con.Open();
  
  var cmd = con.CreateCommand();
  cmd.CommandText = "MYPACKAGE.TESTPROC";
  cmd.CommandType = CommandType.StoredProcedure;
                
  var inParm = new OracleParameter("p_list", OracleDbType.Number);
  inParm.Value = CreateList();                
  inParm.Direction = ParameterDirection.Input;
  var outParm = new OracleParameter("p_cursor", OracleDbType.Cursor) { Direction = ParameterDirection.Output };
  cmd.Parameters.Add(inParm);
  cmd.Parameters.Add(outParm);
                
  cmd.ExecuteNonQuery();

  var cursor = outParm.Value as OracleCursor;
  var dr = cursor.GetDataReader();
  var resultList = new List<int>();
  while (dr.Read())
  {
    resultList.Add(dr.GetInt32(0));
  }
  dr.Close();
}
The resultList will contain 3 elements only, when the list.Count = 65539. If listCount = 65535, then resultList contains 65535 elements.

So it looks like dotConnect has a max list input size of 65535.

If possible, please confirm if this is intended or if it's a bug?

I checked with ODP.NET as well, which always returns the full list using the same example. So the issue seems to be with dotConnect.

Thanks,
Christian

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: PLSQL associative array size limit in parameters

Post by Pinturiccio » Fri 20 Sep 2013 14:46

Could you please provide the following?
1. DDL scripts for REF_T Type, MYPACKAGE and MYPACKAGE body.
2. The CreateList function code.

chrsadk
Posts: 3
Joined: Fri 09 Sep 2011 10:22

Re: PLSQL associative array size limit in parameters

Post by chrsadk » Mon 14 Oct 2013 13:00

Hi again,

I will supply ddl shortly.

I checked the problem further using reflector, and I found the following code in Devart.Data.OracleCommand.ExecuteArray(int iters):

Code: Select all

...
 if (iters >= 0xffff)
 {
     throw new ArgumentException(j.a("ItersLess65535"));
 }
...
I'm guessing this means that there is in fact a max array size for arrays used as parameters.

Best regards,
Christian

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: PLSQL associative array size limit in parameters

Post by Pinturiccio » Tue 15 Oct 2013 13:15

The code you have given is related to the ExecuteArray method, which is not used in your application. We need the following information to reproduce and investigate the issue:
1. DDL scripts for REF_T Type, MYPACKAGE and MYPACKAGE body.
2. The CreateList function code.

Post Reply