Oracle error ORA-01000 (maximum open cursors exceeded) occurs when querying via ODBC with C#

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)) { ... } }

Über uns Stefan

Polyglot Clean Code Developer

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax