Re: Cursors, ExecuteNonQuery and C#

ExecuteNonQuery does not return any row, but it can open a cursor for an output parameter of a stored procedure if the output parameter is defined as a ref cursor. In this case, you should cast the OracleParameter into an OracleDataReader and call Close on the OracleDataReader when you're done with the cursor.

You should call Dispose on the OracleCommand object only when you're done with running the statement for all values of the bind variables that you need to run it with, not sooner. This way you only parse the statement once but can execute it multiple times with different values for its arguments. Once you call Dispose on the OracleCommand, the parse and bind information are gone, and you need to re-parse and re-bind.

ORA-01000 (max cursors exceeded) can be caused by either OPEN_CURSORS set too low in the initialization file, or a cursor leak. So I would suggest first checking the OPEN_CURSORS initialization parameter to make sure you have it set high enough for your application. (Default value is 50.) Then if you're sure you have a cursor leak, I would suggest checking to make sure all the resource release statements are done in the finally clause of a try-catch-finally block, like this:

try
{

   cmd.ExecuteNonQuery();
   ...
}
catch (Exception e)
{

   ...
}
finally
{

   cmd.Dispose();
}

This way you can be sure that resources are released even when the code throws an exception.

Also, if you're using .NET framework 1.1, I would suggest using OracleCommand instead of OleDbCommand since it's more optimized for Oracle.

HTH,
Dave

Matthew Houseman wrote:
> All,
>
> Is it possible for ExecuteNonQuery to open a cursor? The
> reason I ask is that I'm debugging a client's C# service
> using ODP.NET which is leaking cursors and eventually Oracle
> throws a MAX CURSOR EXCEEDED exception and the service dies a
> horrible death. It appears that the method that is the
> main culprit calls several stored procedures which do
> something like:
>
> INSERT INTO FOO ( ... ) RETURNING FOO_ID;
>
> The FOO_ID is setup in the C# as an output parameter and
> additionally, the client never calls the Dispose method on
> the Command object.
>
> Additionally, the Command objects are used like so:
> cmd = new OleDbCommand("sp1", cnx);
> ....
> cmd.ExecuteNonQuery();
> ....
> cmd = new OleDbCommand("sp2", cnx);
> ....
> cmd.ExecuteNonQuery();
> ....
>
> Would this technique cause cursor leaks? Thanks...
>
> Matt
Received on Mon Sep 29 2003 - 15:14:13 CDT