A colleague of mine had this interesting problem today: OdbcDataAdapter ORA-01000. When querying an Oracle database via ODBC from C# using the following code, an Oracle error ORA-01000: maximum open cursors exceeded occured after a certain number of iterations (300 in our case):
static void Main(string[] args)
{
var connectionString = "DSN=MYDB;UID=user;PWD=pass";
var connection = new OdbcConnection(connectionString);
var sql = "Select 1 From DUAL";
connection.Open();
for (int i = 1; i < 500; i++)
{
using (var dataAdapter = new OdbcDataAdapter(sql, connection))
{
var dataTable = new DataTable();
dataAdapter.Fill(dataTable); // -> error ORA-01000 in iteration 300
Console.WriteLine("Iteration {0,8:d} Count = {1,4}", i, dataTable.Rows.Count);
}
}
connection.Close();
}
As it turns out, the OdbcCommand object, that the OdbcDataAdapter uses internally (see field OdbcDataAdapter.SelectCommand), has to be disposed explicitly to get rid of the error. A simple dataAdapter.SelectCommand.Dispose() at the end of the using block would do the job, but there is an even cleaner way:
for (int i = 1; i < 500; i++)
{
using (var command = new OdbcCommand(sql, connection)) // -> makes sure command gets disposed
using (var dataAdapter = new OdbcDataAdapter(command))
{
...
}
}