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();
}