Devart Forums

Discussion forums for open issues and questions concerning database tools, data access components and developer tools from Devart
https://forums.devart.com/

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

https://forums.devart.com/viewtopic.php?f=1&t=38450

Page 1 of 1

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

Posted: Tue 12 Mar 2019 08:13
by 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

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

Posted: Wed 13 Mar 2019 05:59
by Jan Zimmermann
Ok, some additional Input I forgot..

.Net Core
DirectMode

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

Posted: Thu 14 Mar 2019 17:09
by 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.

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

Posted: Fri 15 Mar 2019 05:26
by 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

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

Posted: Fri 15 Mar 2019 15:11
by 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?

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

Posted: Mon 18 Mar 2019 06:29
by 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);
                    }
                }
            }
        }
    }
}

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

Posted: Fri 22 Mar 2019 18:28
by 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?

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

Posted: Mon 25 Mar 2019 09:25
by 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.

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

Posted: Tue 26 Mar 2019 13:05
by 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.

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

Posted: Wed 27 Mar 2019 06:57
by 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.

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

Posted: Thu 28 Mar 2019 15:33
by 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;

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

Posted: Thu 04 Jul 2019 05:07
by 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
All times are UTC
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/