ORA-00604, ORA-01000 errors in C# with Oracle; max open cursors exceeded

Question

Hi all: I have a program that is running 4 threads that talk to an Oracle database. I have the Oracle connections local to each thread, and I'm employing the USING statement as well as manually closing the recordset and closing the connection. As I understand it, the ORA-01000 error arises when there are more open recordsets than configured cursors on the database. I do not understand why my recordsets are staying open or why I'm getting this error. Here's the code:

    static void CheckPaths()
    {
        int pathcount = paths.Count; //paths is a typed list           
        Parallel.ForEach(paths, new ParallelOptions { MaxDegreeOfParallelism = 4 }, (p) =>
        {
            try
            {
                CheckSinglePathAllHours(p);
            }
            catch (Exception ex)
            {
                //there is logging here, this is where the exception hits
            }
        });
    }

    static void CheckSinglePathAllHours(Path p)
    {
        string sqlBase = @"Select * from table ";//this is actually a big SQL statement
        using (DBManager localdbm = new DBManager())
        {
                string sql = sqlBase;             
                OracleDataReader reader = localdbm.GetData(sql);
                while (reader.Read())
                {
                       //process the path, query always returns 24 or less rows
                }
                reader.Close();
                reader = null; //is this even necessary?               
                localdbm.Close(); //is this necessary in conjunction with the USING statement?
        }
    }

class DBManager : IDisposable
{
    OracleConnection conn;
    OracleCommand cmd;
    public DBManager()
    {
        string connStr = "blah blah blah";            
        conn = new OracleConnection(connStr);
        conn.Open();
        cmd = conn.CreateCommand();
    }
    public OracleDataReader GetData(string sql)
    {
        cmd.CommandText = sql;
        cmd.CommandTimeout = 900;
        return cmd.ExecuteReader();
    }
    public void RunSQL(string sql)
    {
        cmd.CommandText = sql;
        cmd.CommandTimeout = 900;
        cmd.ExecuteNonQuery();
    }
    public void Close()
    {
        conn.Close();
    }
    public void Dispose()
    {
        try
        {
            conn.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

The code will usually run for about a minute or two before the exception. The exception message is two-fold: ORA-00604: error occured at recursive SQL level 1; and ORA-01000: maximum open cursors exceeded. Any ideas what I'm doing wrong?

OracleDataReader and OracleConnection implement IDisposable, yet you are not disposing of them anywhere. Dispose != close. — Paul Abbott
consider using odp.net with connection pooling. See here for more — tbone
Paul, it looks like calling .Dispose() on the reader and connection solved the problem. — tuj

tuj tuj · Accepted Answer · 2017-03-27T21:50:12

Changed the code to call .Dispose() on OracleDataReader and OracleConnection as suggested by Paul Abbott. Also increased the number of cursors per session from 50 to 150 on the database.

ORA-00604, ORA-01000 errors in C# with Oracle; max open cursors exceeded

1 Answers