"ORA-1000 max open cursors exceeded" Using OracleDataReader (Doc ID 286707.1)

Last updated on APRIL 13, 2017

Applies to:

Oracle Data Provider for .NET - Version 9.2.0.8 and later
Microsoft Windows x64 (64-bit)
Microsoft Windows (32-bit)

***Checked for relevance on 14-Apr-2017***

Symptoms

Using an OracleDataReader with ODP.NET you encounter an error "ORA-01000: maximum open cursors exceeded." The following code example is slightly exaggerated to show how an ORA-01000 can occur faster:
   

using System;
using System.Data;
using System.IO;
using Oracle.DataAccess;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace DataReader
{
    class Class1
    {
    [STAThread]
     static void Main(string[] args)
     {
       OracleConnection conn = new OracleConnection("user id=scott;password=tiger;data source=ora10g");
       try {
           conn.Open();
           OracleCommand cmd = new OracleCommand("select * from emp", conn);
           for(int i = 0; i < 100000; i++)
            {
             OracleDataReader datareader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
             while(datareader.Read())
             Console.WriteLine("Employee number: {0} Employee Name: {1}", datareader.GetInt32(0), datareader.GetString(1));
            }
           }
       catch (Exception e)
           {
            Console.WriteLine("Error occurred: " + e.Message);
            Console.ReadLine();
           }

       conn.Close();
       conn.Dispose();
     }
   }
}

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms