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):

  1. static void Main(string[] args)
  2. {
  3.     var connectionString = "DSN=MYDB;UID=user;PWD=pass";
  4.     var connection = new OdbcConnection(connectionString);
  5.     var sql = "Select 1 From DUAL";
  6.     connection.Open();
  7.  
  8.     for (int i = 1; i < 500; i++)
  9.     {
  10.         using (var dataAdapter = new OdbcDataAdapter(sql, connection))
  11.         {
  12.             var dataTable = new DataTable();
  13.             dataAdapter.Fill(dataTable); // -> error ORA-01000 in iteration 300
  14.             Console.WriteLine("Iteration {0,8:d} Count = {1,4}", i, dataTable.Rows.Count);
  15.         }
  16.     }
  17.  
  18.     connection.Close();
  19. }

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:

  1. for (int i = 1; i < 500; i++)
  2. {
  3.     using (var command = new OdbcCommand(sql, connection)) // -> makes sure command gets disposed
  4.     using (var dataAdapter = new OdbcDataAdapter(command))
  5.     {
  6.         …
  7.     }
  8. }

Ü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