My Oracle Support Banner

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

Last updated on SEPTEMBER 23, 2018

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 23-Sep-2018***

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

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.