ORA-01000 Maximum Open Cursors Exceeded

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
boekhoffj
Posts: 1
Joined: Fri 24 Oct 2008 10:20

ORA-01000 Maximum Open Cursors Exceeded

Post by boekhoffj » Fri 24 Oct 2008 10:46

Hi,
I would like to read master detail data from several tables. It would be nice to use only one select statement like "select dapartment_name, CURSOR(select * from employees where department_id = department.department_id) from department".
If the number of rows of department is less than the number of open cursors all is fine. But if there are more departments the DataAdapter.Fill raises an ORA-00604, ORA-01000 exception. I tried to work arround by reading data with a DataReader and closing the reader / cursor directly after reading. But the problem persists. How can I close the cursors?
Sample:

Code: Select all

private void test()
{
...
   string sql = "select dapartment_name, CURSOR(select * from employees where department_id = department.department_id) from department";
   CoreLab.Oracle.OracleCommand myCommand = new CoreLab.Oracle.OracleCommand(sql, conn);
  CoreLab.Oracle.OracleDataReader myReader = myCommand.ExecuteReader();
  ExecuteReader(ds4, myReader, "DEPARTMENT");
}

private static void ExecuteReader(DataSet ds, CoreLab.Oracle.OracleDataReader reader, string tableName)
        {
            while (reader.Read())
            {
                DataTable tab = ds4.Tables[tableName];
                DataRow row = tab.NewRow();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    Type t = reader.GetFieldType(i);
                    object obj = reader.GetValue(i);
                    if (obj.GetType() == typeof(CoreLab.Oracle.OracleCursor))
                    {
                        tab.Rows.Add(row);
                        CoreLab.Oracle.OracleCursor cursor = (CoreLab.Oracle.OracleCursor)obj;
                        CoreLab.Oracle.OracleDataReader r = cursor.GetDataReader();
                        ExecuteReader( ds4,r, tableName + "Sub");
                        r.Close();
                        cursor.Close();
                        cursor.Dispose();
                        r.Dispose();
                    }
                    else
                        row[i] = obj;
                }
                if (row.RowState == DataRowState.Detached) 
                    tab.Rows.Add(row);
            }
            reader.Close();
        }

Shalex
Devart Team
Posts: 8036
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 27 Oct 2008 10:00

This behaviour is designed by Oracle. OraDirect .NET actually closes cursors. Probably, Oracle associates child cursors with cursor of command and closes them only after command's cursor closing.

Post Reply