Dequeuer in direct mode leaves open cursors

Dequeuer in direct mode leaves open cursors

Postby 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.
sebniepel
 
Posts: 9
Joined: Tue 08 Sep 2009 09:42

Postby StanislavK » Fri 12 Feb 2010 17:30

We have reproduced the issue. We will investigate it and notify you about the results.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby schueler » Thu 18 Feb 2010 11:51

Any updates on this issue?
schueler
 
Posts: 17
Joined: Tue 17 May 2005 15:44
Location: Germany

Postby 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.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby 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;
schueler
 
Posts: 17
Joined: Tue 17 May 2005 15:44
Location: Germany

Postby 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().
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby 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.
sebniepel
 
Posts: 9
Joined: Tue 08 Sep 2009 09:42

Postby 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
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby 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 .
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to dotConnect for Oracle