Dequeue Oracle AQ with ODP.NET With MSGID

Question

I am trying to Dequeue a Message from oracle AQ using ODP.NET by service

I have two conditions

1. MessageID of the Of the Record in Queue table to be dequeued will be provided by the Service
2. Once the Service Dequeue the message that record should not be available for other instances of services ,Means it should be locked till transaction is committed or transaction expires

Issue :

1. DequeueOptions MsgID not Recognising my MSGID
2. Cannot find how to lock other instances from dequeuing same msg ID

And My Queue is like

BEGIN DBMS_AQADM.CREATE_QUEUE_TABLE(
     Queue_table        => '"DEVENV1_CUSTOM"."INTEGRATION_MSG_QUEUE"',
     Queue_payload_type => '"DEVENV1_CUSTOM"."TRIGGER_AUDIT_TYPE"',
     Sort_list          => 'ENQ_TIME',
     Compatible         => '10.0.0',
     primary_instance   => 3,
     secondary_instance => 2);
  END;

Can somebody suggest me a way to pass msgid of record to be dequeued from queue and how to lock them from others accessing it till commit or time out

public static void DequeueMessage(string MessageID)
        {
            // Create connection
            string constr = "my constring";
            Oracle.DataAccess.Client.OracleConnection con = new Oracle.DataAccess.Client.OracleConnection(constr);
            // Create queue
            OracleAQQueue queue = new OracleAQQueue("DEVENV1_CUSTOM.INTEGRATION_MSG_QUEUE_Q", con);
            try
            {
                // Open connection
                con.Open();     
                // Begin txn for enqueue
                Oracle.DataAccess.Client.OracleTransaction txn = con.BeginTransaction();
                // Prepare to Dequeue
                queue.DequeueOptions.Visibility = OracleAQVisibilityMode.OnCommit;

                queue.DequeueOptions.Wait = 10;

                queue.DequeueOptions.DequeueMode = OracleAQDequeueMode.Locked;

                // Set message type for the queue
                queue.MessageType = OracleAQMessageType.Udt;

                queue.UdtTypeName = "TRIGGER_AUDIT_TYPE";        

                queue.DequeueOptions.MessageId = System.Text.Encoding.ASCII.GetBytes ("A0CB9F328889420D8F0C8A62BF38B454");

                // Dequeue message
                OracleAQMessage deqMsg = queue.Dequeue();

                TRIGGER_AUDIT_TYPE data = (TRIGGER_AUDIT_TYPE)deqMsg.Payload;

                // Dequeue txn commit
                txn.Commit();

            }
            catch (Exception e)
            {
                Console.WriteLine("Error: {0}", e.Message);
               
            }
            finally
            {
                // Close/Dispose objects

                con.Close();
                con.Dispose();
            }

        }

Answer 1

The issue is that the the byte array returned by the Oracle and byte array created by .net are different (read about little Endian and Big Endian if you are intereseted )

so i solved the issue by directly casting the array from oracle and used it for Dequeue

  OracleCommand oracleCommand = new OracleCommand("Select MSGID  from INTEGRATION_MSG_QUEUE where msgid='B85439241C9241A4A6B1869F08F382E7'", con);
            var K=  oracleCommand.ExecuteScalar();
            Byte[] rawBytesFromOracle =K as byte[];
           


            // Begin txn for enqueue
            Oracle.DataAccess.Client.OracleTransaction txn = con.BeginTransaction();
            // Prepare to Dequeue
            queue.DequeueOptions.Visibility = OracleAQVisibilityMode.OnCommit;

            queue.DequeueOptions.Wait = 10;

            queue.DequeueOptions.DequeueMode = OracleAQDequeueMode.Locked;

            queue.DequeueOptions.MessageId = rawBytesFromOracle;


            // Set message type for the queue
            queue.MessageType = OracleAQMessageType.Udt;

            queue.UdtTypeName = "TRIGGER_AUDIT_TYPE";


            Console.WriteLine("DeQueueing ............ ");

            // Dequeue message
            OracleAQMessage deqMsg = queue.Dequeue();

            TRIGGER_AUDIT_TYPE data = (TRIGGER_AUDIT_TYPE)deqMsg.Payload;

            Console.WriteLine("Dequeued message...." + data.CMESSAGEBODY );