ORA-01000 Maximum Open Cursors Exceeded

ORA-01000 Maximum Open Cursors Exceeded

Postby 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();
        }
boekhoffj
 
Posts: 1
Joined: Fri 24 Oct 2008 10:20

Postby 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.
Shalex
Devart Team
 
Posts: 7781
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle