Fog Creek Software
Discussion Board




Welcome! and rules

Joel on Software

ORA-01000: maximum open cursors exceeded

I'm trying to do a very simple task: 1000 updates in a loop as a transaction.  I'm using .NET 1.1 Oracle 9i.

After about 500 iterations of the loop, I get the error.

Here is the code.  Any ideas will be most appreciated.



        public static void Update(int tradeNumber, ArrayList validAssigmentList, string strUserId)
        {
            if(null == validAssigmentList || validAssigmentList.Count < 1)
                throw new ArgumentException("null or empty validAssigmentList!");

            OracleConnection conn = null;
            OracleTransaction trans = null;

            try
            {
                conn = new OracleConnection("Data Source=YYYY.WORLD;User Id=XXXX;Password=XXXX;Min Pool Size=15;Max Pool Size=30;Pooling='true'");
                conn.Open();

                trans = conn.BeginTransaction();                

                //update the individual loan values in the xref table
                string query = "UPDATE tableA SET " +
                    " last_mod_user          ='{0}', " +
                    " last_mod_dt            = {1}  " +
                    " WHERE id = '{2}'";

                string finalQuery = null;
                Assignment assignment = null;
                for(int i = 0; i < validAssigmentList.Count; i++)
                {
                    assignment = (Assignment)validAssigmentList[i];
                    finalQuery = String.Format(query,
                        strUserId,
                        "SYSDATE",
                        assignment.id);

                    OracleCommand cmd = new OracleCommand(finalQuery, conn);
                    cmd.Transaction = trans;
                    cmd.CommandType = CommandType.Text;
                    cmd.Prepare();
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();

                }

                trans.Commit();
                conn.Close();
                conn.Dispose();
            }
            catch(Exception excep)
            {
                trans.Rollback();
                conn.Close();
                conn.Dispose();
                throw excep;
            }
        }

Greg
Wednesday, January 12, 2005

try increasing the pool size to some larger number and if it doesn't error out (or loops thru more rows) then you have a leak somewhere (I can't see where though).

smallbiz
Wednesday, January 12, 2005

I'd guess the command objects aren't actually being disposed until you commit the transaction.  You might try reusing a single command (by just changing its text, then going through Prepare & Execute without the New & Dispose) within the loop, or grouping multiple updates into the text of a single Command object.

Note the word "guess" above.

Mike Gunderloy
Thursday, January 13, 2005

from Google:

.NET has no concept of a cursor, because it's suppose to work with SQL Server (which has no cursors either).  As a result, .NET ADO driver interface is written without the explicit method to close the cursor, which is necessary to work properly with Oracle.

I can't beilive that .NET has no concept of a cursor and no way to close the statment properly.  Ouch.

Greg NUdelman
Thursday, January 13, 2005

".NET has no concept of a cursor, because it's suppose to work with SQL Server (which has no cursors either)."

Open SQL Server Books Online, and look up "cursors, implementing". You see:

-----

Cursor Implementations
Microsoft® SQL Server™ 2000 supports three cursor implementations:

Transact-SQL cursors.

Are based on the DECLARE CURSOR syntax and are used mainly in Transact-SQL scripts, stored procedures, and triggers. Transact-SQL cursors are implemented on the server and are managed by Transact-SQL statements sent from the client to the server. They are also contained in batches, stored procedures, or triggers.

Application programming interface (API) server cursors

Support the API cursor functions in OLE DB, ODBC and DB-Library. API server cursors are implemented on the server. Each time a client application calls an API cursor function, the SQL Server OLE DB provider, ODBC driver, or DB-Library dynamic-link library (DLL) transmits the request to the server for action against the API server cursor.

Client cursors

Are implemented internally by the SQL Server ODBC driver, the DB-Library DLL, and by the DLL that implements the ADO API. Client cursors are implemented by caching all the result set rows on the client. Each time a client application calls an API cursor function, the SQL Server ODBC driver, the DB-Library DLL, or the ADO DLL performs the cursor operation on the result set rows cached on the client.

Brad Wilson
Thursday, January 13, 2005

I faced the same problem using MSODP version 1 with framework 1.0. After executing command the cursor remains opened. The possible soultion would be to update your MSODP with a patch from Microsoft support. It worked for me. Increasing the max cursor size wont work as it is bound to fail after that limit is reached.

Hemant Mishal
Friday, January 14, 2005

Did you happen to know the name of the patch?

Greg
Friday, January 14, 2005

OK, I found only one patch: .NET 1.1 SP1.  It makes no metion of this fix.  There are also 2 more fixes that are "not stable" and not recommended that deal with connection leakage.  Related, but no cigar.

However, I found this nice thread:

http://forums.oracle.com/forums/thread.jsp?forum=146&thread=30723&message=414035


And in depseration I tried GC.Collect();  It worked like a charm!!  I'm now doing:

                        ///this is a useful little hack to resolve
    ///"ORA-01000 Maximum Open Cursors Exceeded"
    ///For each 10 UPDATE statments we get 10 cursors open,
    ///so force clean up after 10 iterations of the loop
    ///getting rid of 10 cursors when we run GC.Collect()                                        if(i % 10 == 0) //every 10 iterations
            GC.Collect();

This works. Whew.  I can't beilve this hakarino!!  Oracle driver for .NET can sure use some work. :^(

Thanks again for all the help.  Problem solved, that's the important thing.

Greg

Greg
Friday, January 14, 2005

This seems like a terribly inefficient way to do the update --  can you not do a single update for all 1,000 values by using a bound array ... http://www.oracle.com/technology/sample_code/tech/windows/odpnet/howto/arraybind/index.html ?

David Aldridge
Tuesday, January 18, 2005

*  Recent Topics

*  Fog Creek Home