Attempts Property in Oracle (18c) Advanced Queueing not read

[Jan Zimmermann]

Hi,

using your latest nuget package 9.6.696 it seems that the Attempts Property (OracleQueueMessage.MessageProperties.Attempts) is always Zero on 18c

Jan

[Jan Zimmermann]

Ok, some additional Input I forgot..

.Net Core
DirectMode

[Shalex]

OracleQueueMessage.MessageProperties.Attempts gets the number of unsuccessful attempts that have been made to dequeue the message.

We will correct the current description at https://www.devart.com/dotconnect/oracl ... empts.html.

[Jan Zimmermann]

Yes, I know.

Again, its always zero. Also after 3 Dequeue Attempts & Rollback.
I have test for our Environment that runs against Oracle 12 and fails against 18c

[Shalex]

A simple example of dequeuing is available at https://www.devart.com/dotconnect/oracl ... ssage.html. Could you please send us a small test project to reproduce a scenario that counts failed attempts with Oracle 12c and doesn't do that with Oracle 18c?

[Jan Zimmermann]

Code: Select all

using System;
using System.Text;
using Devart.Data.Oracle;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Newtonsoft.Json;

namespace OracleAqTest
{
    [TestClass]
    public class UnitTest1
    {
        [TestMethod]
        public void TestMethod1()
        {
            var connectionString = ""; //private :) But is a connection with Direct=True to a Oracle Cluster 18c with ServiceName
            var subscriber = $"Test_{new Random().Next(10000)}"; //+ Guid.NewGuid().ToString();
            var queueName = "FACTORYPOOL.TEST";


            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();

                var blobContent = Encoding.UTF8.GetBytes(JsonConvert.SerializeObject(new {Test = "Test"}));
                var oqmp = new OracleQueueMessageProperties(100,0);
                oqmp.RecipientList.Add(subscriber, "");

                using (var oracleEnqueue = new OracleQueue(queueName, conn))
                {
                    oracleEnqueue.Enqueue(new OracleBinary(blobContent), oqmp, new OracleQueueEnqueueOptions());
                }
            }

            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();

                using (conn.BeginTransaction())
                {
                    using (var queue = new OracleQueue(queueName, conn))
                    {
                        queue.DequeueOptions.ConsumerName = subscriber;
                        queue.DequeueOptions.DequeueMode = OracleQueueDequeueMode.Remove;

                        queue.DequeueOptions.WaitTimeout = 0;
                        queue.DequeueOptions.Navigation = OracleQueueNavigation.FirstMessage;
                        queue.DequeueOptions.Visibility = OracleQueueVisibility.OnCommit;


                        //OK First Dequeue Attempt. Attempts should be Zero
                        var message = queue.Dequeue();

                        Assert.AreEqual(0,message.MessageProperties.Attempts);
                    }
                }
            }

            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();

                using (conn.BeginTransaction())
                {
                    using (var queue = new OracleQueue(queueName, conn))
                    {
                        queue.DequeueOptions.ConsumerName = subscriber;
                        queue.DequeueOptions.DequeueMode = OracleQueueDequeueMode.Remove;

                        queue.DequeueOptions.WaitTimeout = 0;
                        queue.DequeueOptions.Navigation = OracleQueueNavigation.FirstMessage;
                        queue.DequeueOptions.Visibility = OracleQueueVisibility.OnCommit;

                        //OK Second Dequeue Attempt. Attempts should be 1
                        var message = queue.Dequeue();


                        //Here the Test fail because Attempts not filled Properly
                        Assert.AreEqual(1,message.MessageProperties.Attempts);
                    }
                }
            }
        }
    }
}

[Shalex]

TestInitialize and TestCleanup are missing in your test case. We have updated your code:

Code: Select all

using System;
using System.Text;
using Devart.Data.Oracle;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Newtonsoft.Json;

namespace OracleAqTest
{
    [TestClass]
    public class UnitTest1
    {

        //string connectionString = "User Id=alexsh;Password=alexsh;Server=oracle12c.datasoft.local;Direct=True;Sid=ORCLPDB.datasoft.local;license key=...;";
        //string connectionString = "Home=OraClient12Home1;User Id=alexsh;Password=alexsh;Server=orclpdb_1220_R2;license key=...;";

        string connectionString = "User Id=alexsh;Password=alexsh;Server=oracle18c.datasoft.local;Direct=True;Sid=oracle18c;license key=...;";

        string subscriber = $"Test_{new Random().Next(10000)}"; //+ Guid.NewGuid().ToString();
        string queueName = "MESSAGE_QUEUE";
        string queueTableName = "QUEUE_TABLE_MESSAGE";

        [TestInitialize]
        public void Setup()
        {
            try
            {
                using (var conn = new OracleConnection(connectionString))
                {
                    conn.Open();

                    OracleCommand oracleCommand = new OracleCommand(
                            "CREATE OR REPLACE TYPE message AS OBJECT (nickname VARCHAR2(15), " +
                            "mestext VARCHAR2(80));", conn);
                    oracleCommand.ExecuteNonQuery();
                    OracleQueueTable oracleQueueTable = new OracleQueueTable(queueTableName, conn);
                    oracleQueueTable.Options.PayloadTypeName = "message";
                     oracleQueueTable.Options.MultipleConsumers = true;
                    oracleQueueTable.CreateQueueTable();
                    OracleQueueAdmin oracleQueueAdmin = new OracleQueueAdmin(queueName, queueTableName, conn);
                    oracleQueueAdmin.CreateQueue();
                    oracleQueueAdmin.StartQueue();
                }
            }
            catch (Exception ex) {

            }
        }

        [TestCleanup]
        public void TearDown()
        {
            try
            {
                using (var conn = new OracleConnection(connectionString))
                {
                    conn.Open();
                    OracleQueueTable oracleQueueTable = new OracleQueueTable(queueTableName, conn);
                    OracleQueueAdmin oracleQueueAdmin = new OracleQueueAdmin(queueName, queueTableName, conn);
                    oracleQueueAdmin.StopQueue();
                    oracleQueueAdmin.DropQueue();
                    oracleQueueTable.DropQueueTable();
                }
            }
            catch (Exception ex) { }
        }

        [TestMethod]
        public void TestMethod1()
        {
            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();

                OracleQueue oracleEnqueueQueue = new OracleQueue(queueName, conn);
                OracleObject mes = new OracleObject("message", conn);
                mes["nickname"] = conn.UserId;
                mes["mestext"] = "Hello, world!";

                var oqmp = new OracleQueueMessageProperties(100, 0);
                oqmp.RecipientList.Add(subscriber, "");
                oracleEnqueueQueue.Enqueue(mes, oqmp, new OracleQueueEnqueueOptions());
            }

            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();

                using (conn.BeginTransaction())
                {
                    using (var queue = new OracleQueue(queueName, conn))
                    {
                        queue.DequeueOptions.ConsumerName = subscriber;
                        queue.DequeueOptions.DequeueMode = OracleQueueDequeueMode.Remove;

                        queue.DequeueOptions.WaitTimeout = 0;
                        queue.DequeueOptions.Navigation = OracleQueueNavigation.FirstMessage;
                        queue.DequeueOptions.Visibility = OracleQueueVisibility.OnCommit;


                        //OK First Dequeue Attempt. Attempts should be Zero
                        var message = queue.Dequeue();

                        Assert.AreEqual(0, message.MessageProperties.Attempts);
                    }
                }
            }

            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();

                using (conn.BeginTransaction())
                {
                    using (var queue = new OracleQueue(queueName, conn))
                    {
                        queue.DequeueOptions.ConsumerName = subscriber;
                        queue.DequeueOptions.DequeueMode = OracleQueueDequeueMode.Remove;

                        queue.DequeueOptions.WaitTimeout = 0;
                        queue.DequeueOptions.Navigation = OracleQueueNavigation.FirstMessage;
                        queue.DequeueOptions.Visibility = OracleQueueVisibility.OnCommit;

                        OracleQueueMessage message = new OracleQueueMessage();
                        //try
                        //{
                            //OK Second Dequeue Attempt. Attempts should be 1
                            message = queue.Dequeue();
                        //}
                        //catch (Exception ex) { }


                        //Here the Test fail because Attempts not filled Properly
                        Assert.AreEqual(1, message.MessageProperties.Attempts);
                    }
                }
            }
        }
    }
}

Does this code reproduce a scenario that counts failed attempts with Oracle 12c and doesn't do that with Oracle 18c?

[Jan Zimmermann]

Yes if you insert a oracleQueueAdmin.AlterMaxRetries(1); after oracleQueueAdmin.CreateQueue();

You cannot Dequeue twice if MaxRetries zero.

Be aware: Your cleanup Method runs not very well. So be sure oracleQueueAdmin.AlterMaxRetries(1); in the Setup Method is reached. And not ends in that empty catch block.

[Shalex]

The updated test passed successfully with our "Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production":

Code: Select all

using System;
using System.Text;
using Devart.Data.Oracle;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Newtonsoft.Json;

namespace OracleAqTest
{
    [TestClass]
    public class UnitTest1
    {

        //string connectionString = "User Id=alexsh;Password=alexsh;Server=oracle12c.datasoft.local;Direct=True;Sid=ORCLPDB.datasoft.local;license key=...;";
        //string connectionString = "Home=OraClient12Home1;User Id=alexsh;Password=alexsh;Server=orclpdb_1220_R2;license key=...;";

        string connectionString = "User Id=alexsh;Password=alexsh;Server=oracle18c.datasoft.local;Direct=True;Sid=oracle18c;license key=...;";

        string subscriber = $"Test_{new Random().Next(10000)}"; //+ Guid.NewGuid().ToString();
        string queueName = "MESSAGE_QUEUE";
        string queueTableName = "QUEUE_TABLE_MESSAGE";

        [TestInitialize]
        public void Setup()
        {
            try
            {
                using (var conn = new OracleConnection(connectionString))
                {
                    conn.Open();

                    OracleCommand oracleCommand = new OracleCommand(
                            "CREATE OR REPLACE TYPE message AS OBJECT (nickname VARCHAR2(15), " +
                            "mestext VARCHAR2(80));", conn);
                    oracleCommand.ExecuteNonQuery();
                    OracleQueueTable oracleQueueTable = new OracleQueueTable(queueTableName, conn);
                    oracleQueueTable.Options.PayloadTypeName = "message";
                     oracleQueueTable.Options.MultipleConsumers = true;
                    oracleQueueTable.CreateQueueTable();
                    OracleQueueAdmin oracleQueueAdmin = new OracleQueueAdmin(queueName, queueTableName, conn);
                    oracleQueueAdmin.CreateQueue();
                    oracleQueueAdmin.AlterMaxRetries(1);
                    oracleQueueAdmin.StartQueue();
                }
            }
            catch (Exception ex) {

            }
        }

        [TestCleanup]
        public void TearDown()
        {
            try
            {
                using (var conn = new OracleConnection(connectionString))
                {
                    conn.Open();
                    OracleQueueTable oracleQueueTable = new OracleQueueTable(queueTableName, conn);
                    OracleQueueAdmin oracleQueueAdmin = new OracleQueueAdmin(queueName, queueTableName, conn);
                    oracleQueueAdmin.StopQueue();
                    oracleQueueAdmin.DropQueue();
                    oracleQueueTable.DropQueueTable();
                }
            }
            catch (Exception ex) { }
        }

        [TestMethod]
        public void TestMethod1()
        {
            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();

                OracleQueue oracleEnqueueQueue = new OracleQueue(queueName, conn);
                OracleObject mes = new OracleObject("message", conn);
                mes["nickname"] = conn.UserId;
                mes["mestext"] = "Hello, world!";

                var oqmp = new OracleQueueMessageProperties(100, 0);
                oqmp.RecipientList.Add(subscriber, "");
                oracleEnqueueQueue.Enqueue(mes, oqmp, new OracleQueueEnqueueOptions());
            }

            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();

                using (conn.BeginTransaction())
                {
                    using (var queue = new OracleQueue(queueName, conn))
                    {
                        queue.DequeueOptions.ConsumerName = subscriber;
                        queue.DequeueOptions.DequeueMode = OracleQueueDequeueMode.Remove;

                        queue.DequeueOptions.WaitTimeout = 0;
                        queue.DequeueOptions.Navigation = OracleQueueNavigation.FirstMessage;
                        queue.DequeueOptions.Visibility = OracleQueueVisibility.OnCommit;


                        //OK First Dequeue Attempt. Attempts should be Zero
                        var message = queue.Dequeue();

                        Assert.AreEqual(0, message.MessageProperties.Attempts);
                    }
                }
            }

            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();

                using (conn.BeginTransaction())
                {
                    using (var queue = new OracleQueue(queueName, conn))
                    {
                        queue.DequeueOptions.ConsumerName = subscriber;
                        queue.DequeueOptions.DequeueMode = OracleQueueDequeueMode.Remove;

                        queue.DequeueOptions.WaitTimeout = 0;
                        queue.DequeueOptions.Navigation = OracleQueueNavigation.FirstMessage;
                        queue.DequeueOptions.Visibility = OracleQueueVisibility.OnCommit;

                        OracleQueueMessage message = new OracleQueueMessage();
                        //try
                        //{
                            //OK Second Dequeue Attempt. Attempts should be 1
                            message = queue.Dequeue();
                        //}
                        //catch (Exception ex) { }


                        //Here the Test fail because Attempts not filled Properly
                        Assert.AreEqual(1, message.MessageProperties.Attempts);
                    }
                }
            }
        }
    }
}

Please run exactly the same test with your Oracle 18c. If it fails, could you give us an access to your Oracle 18c via Internet? The database user may have limited privileges required for running this test.

[Jan Zimmermann]

What the Hell..

I have rewritten this in PL/SQL (urgh)

It has the same behaviour. Sorry to bother you.

Do you have an idea what weird user privilege this could be?
I can create Tables, Queues, Enqueue, Dequeue but cannot read Attempts Property??

I think this topic can be closed.

[Shalex]

JIC: we ran the test with the following database user:

Code: Select all

CREATE USER ALEXSH
	IDENTIFIED BY 
	DEFAULT TABLESPACE USERS
	TEMPORARY TABLESPACE TEMP
	PROFILE "DEFAULT"
DEFAULT COLLATION USING_NLS_COMP;

GRANT UNLIMITED TABLESPACE, "CONNECT", "RESOURCE" TO ALEXSH;
ALTER USER ALEXSH DEFAULT ROLE ALL;
GRANT DEBUG, EXECUTE ON SYS.DBMS_AQ TO ALEXSH;
GRANT DEBUG, EXECUTE ON SYS.DBMS_AQADM TO ALEXSH;
GRANT EXECUTE ON SYS.DBMS_AQ_BQVIEW TO ALEXSH;
GRANT SELECT ON SYS.AQ$_UNFLUSHED_DEQUEUES TO ALEXSH;

[Jan Zimmermann]

Sorry for the late Answer.

Seems we have to call DBMS_AQIN.set_multi_retry(TRUE) for whatever reason on Oracle 18c Enterprise to Activate the "Attempts" Feature