Dequeuer in direct mode leaves open cursors

sebniepel · Post by **sebniepel** » Thu 11 Feb 2010 16:16

When using the Dequeuer in Direct Mode and no message is received, it leaves two open cursors on the server.

Using OCI mode the number of cursors do not increase.

StanislavK · Post by **StanislavK** » Fri 12 Feb 2010 17:30

We have reproduced the issue. We will investigate it and notify you about the results.

schueler · Post by **schueler** » Thu 18 Feb 2010 11:51

Any updates on this issue?

StanislavK · Post by **StanislavK** » Fri 19 Feb 2010 07:24

I couldn't reproduce the problem with the latest 5.55.97 Beta build. Please try it:
http://www.devart.com/dotconnect/oracle/download.html

If you are still encountering the problem with the new version, please send us a small test project.

schueler · Post by **schueler** » Mon 01 Mar 2010 12:07

Checked with 5.55.97 Beta, but the problem is still there. The following select was used to check for open cursors:

select c.user_name, c.sid, count(*)
from v$open_cursor c, v$sql sql
where c.sql_id = sql.sql_id
group by c.user_name, c.sid
order by count(*) desc;

StanislavK · Post by **StanislavK** » Tue 02 Mar 2010 14:42

Please see the following sample. In our environment, it behaves as described in both Direct and OCI modes:

Code: Select all

// Open the connection. 
OracleConnection oracleConnection = new OracleConnection(
	//"User Id=Scott;Password=tiger;Server=ora;Direct=False;");
	"User Id=Scott;Password=tiger;Server=server;SID=ora;Port=1521;Direct=True;");
oracleConnection.Open();

// Initialize the Queue table and the queue.
OracleQueueTable oracleQueueTable = new OracleQueueTable
	("QUEUE_TABLE_MESSAGE", oracleConnection);
oracleQueueTable.Options.PayloadTypeName = "RAW";
OracleQueueAdmin oracleQueueAdmin = new OracleQueueAdmin
	("MESSAGE_QUEUE", "QUEUE_TABLE_MESSAGE", oracleConnection);

oracleQueueTable.CreateQueueTable();
oracleQueueAdmin.CreateQueue();
oracleQueueAdmin.StartQueue();

// Create a dequeuing object
OracleQueue oracleDequeueQueue = new OracleQueue("MESSAGE_QUEUE", oracleConnection);
oracleDequeueQueue.DequeueOptions.WaitTimeout = 1;

// Try to dequeue a message. As no messages were enqueued, a timeout exception will be thrown.
// At this moment, several cursors are opened.
try
{                
	OracleQueueMessage msg = oracleDequeueQueue.Dequeue();
}
catch (OracleException ex)            
{
	// Check that exception was thrown because of timeout.
	if (ex.Code != 25228)
	{
		throw ex;
	}
}

// The cursors are still opened.
oracleConnection.Close();

// The cursors are still opened. They close after the application is terminated.

Please specify if you are encountering a different behaviour when running this sample, or what should be changed in the sample to reproduce the
error.

Also, please note that cursors are not closed after the connection is closed because the connection pooling feature is enabled. When I disable pooling, the cursors close on oracleConnection.Close().

sebniepel · Post by **sebniepel** » Tue 23 Mar 2010 08:58

Our problem is, if a timeout occurs, we start a new Dequeue(). For that we don`t want to close and reopen the connection. This still leads to open cursors with every loop run. In OCI mode it runs fine.
To reproduce it, just put the try-catch-block in a while loop.

StanislavK · Post by **StanislavK** » Tue 23 Mar 2010 16:19

We have reproduced the problem, inside a loop new cursors are opened till the limit is reached. We will investigate the situation and notify you about the results.

StanislavK · Post by **StanislavK** » Wed 31 Mar 2010 16:56

We have fixed the problem. The fix is available in the new 5.60.120 build of dotConnect for Oracle.

The build can be downloaded from
http://www.devart.com/dotconnect/oracle/download.html
(trial version) or from Registered Users' Area (for users with valid subscription only).

For more information, please refer to
http://www.devart.com/forums/viewtopic.php?t=17514 .