Oracle AQ c# and dequeueing a message from oracle queue in c#

Question

I am using Oracle 12.01, ODP.NET x64, version 4, and referencing Oracle.DataAccess.dll in .net framework 4.6. Problem appears when I try to dequeue a message in visual studio. My user has granted dequeue right and the queue is part of client's another schema. In c# code queue name is equals X_SHEMA.X.QUEUE_NAME.

sql script that executes successfuly in SQL Developer:

DECLARE
  queueopts dbms_aq.dequeue_options_t;
  msgprops  dbms_aq.message_properties_t;
  msg_id    RAW(16);
  message   sys.aq$_jms_text_message;
  msg_text  CLOB;
  msg_line  VARCHAR2(255);
  msg_count INTEGER;

  no_subscribers EXCEPTION;
  no_messages    EXCEPTION;
  PRAGMA EXCEPTION_INIT(no_subscribers, -24033);
  PRAGMA EXCEPTION_INIT(no_messages, -25228);
BEGIN
  queueopts.wait          := DBMS_AQ.NO_WAIT;
  queueopts.navigation    := DBMS_AQ.FIRST_MESSAGE;
  queueopts.dequeue_mode  := DBMS_AQ.LOCKED;
  queueopts.consumer_name := '&receiver';
  msg_count := 0;
  WHILE (queueopts.navigation = DBMS_AQ.FIRST_MESSAGE OR msg_id IS NOT NULL) LOOP
    BEGIN
      dbms_aq.dequeue(queue_name         => '&queue',
                      dequeue_options    => queueopts,
                      message_properties => msgprops,
                      payload            => message,
                      msgid              => msg_id);
      message.get_text(msg_text);
    EXCEPTION
      WHEN no_subscribers THEN
        -- Ignorieren.
        msg_text := NULL;
        msg_id := NULL;
      WHEN no_messages THEN
        -- Fertig.
        msg_text := NULL;
        msg_id := NULL;
    END;
    IF msg_id IS NULL THEN
      dbms_output.put_line('---------------==========##+##==========---------------');
      dbms_output.put_line(to_char(msg_count) || ' message(s) received');
    ELSE
      msg_count := msg_count + 1;
      dbms_output.put_line('---------------==========##+##==========---------------');
      dbms_output.put_line(':msg_nb     = ' || to_char(msg_count));
      dbms_output.put_line(':msg_id     = ' || RAWTOHEX(msg_id));
      dbms_output.put_line(':attempts   = ' || msgprops.attempts);
      dbms_output.put_line(':nl_msgtype = ' || message.get_string_property('NL_MSGTYPE'));
      dbms_output.put_line(':sender     = ' || message.get_string_property('SENDER'));
      dbms_output.put_line(':msg_text   = #' || length(msg_text));
      WHILE (length(msg_text) > 0) LOOP
        msg_line := substr(msg_text||chr(10),1,instr(msg_text||chr(10),chr(10)));
        msg_text := substr(msg_text,length(msg_line)+1);
        dbms_output.put_line(substr(msg_line,1,length(msg_line)-1));
      END LOOP;
    END IF;
    queueopts.navigation := DBMS_AQ.NEXT_MESSAGE;
  END LOOP;
  rollback;
END;
/

c# code:

private Response ReceiveFromQueue()
        {
            #region data

            Response response = new Response ();

            OracleAQDequeueOptions options = new OracleAQDequeueOptions
            {
                DequeueMode = OracleAQDequeueMode.Locked,
                Wait = 0,
                NavigationMode = OracleAQNavigationMode.FirstMessage,
                ConsumerName = string.Empty,
                MessageId = new byte[16],
                ProviderSpecificType = true
            };

            string _connString = "data source=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = X_IP_ADDRESS)(PORT = X_PORT)) (CONNECT_DATA = (ORACLE_SID = X_SID)));User Id=X_USER;Password=X_PASSWORD;";

            OracleAQQueue queue = new OracleAQQueue(queueName)
            {
                MessageType = OracleAQMessageType.Raw,
                DequeueOptions = new OracleAQDequeueOptions
                {
                    Visibility = OracleAQVisibilityMode.OnCommit,
                    DequeueMode = OracleAQDequeueMode.Locked,
                    NavigationMode = OracleAQNavigationMode.FirstMessage,
                    ConsumerName = string.Empty,
                    Wait = 0,
                    MessageId = new byte[16],
                    ProviderSpecificType = true,
                },
            };

            #endregion

            try
            {
                OracleConnection conn = new OracleConnection(_connString);
                conn.Open();

                queue.Connection = conn;

                OracleTransaction tnx = conn.BeginTransaction();

                OracleAQMessage deqMsg = queue.Dequeue(options);

                tnx.Commit();

                conn.Close();
                conn.Dispose();
                conn = null;
            }
            catch (Exception ex) { Console.WriteLine(ex.Message); }

            return response;
        }

Dequeue throws exception ORA-25215: user_data type and queue type do not match

When I change the options:

OracleAQQueue queue = new OracleAQQueue(queueName)
            {
                MessageType = OracleAQMessageType.Udt,
                DequeueOptions = new OracleAQDequeueOptions
                {
                    Visibility = OracleAQVisibilityMode.OnCommit,
                    DequeueMode = OracleAQDequeueMode.Locked,
                    NavigationMode = OracleAQNavigationMode.FirstMessage,
                    ConsumerName = string.Empty,
                    Wait = 0,
                    MessageId = new byte[16],
                    ProviderSpecificType = true,
                },
                UdtTypeName = "sys.aq$_jms_text_message"
            };

I get the next exception OCI-22303: type "sys"."aq$_jms_text_message" not found

I am wondering whether you have an idea what might be an issue (c# code above, Dequeue method, queue etc.), how to solve it and dequeue a message, should I contact db admins, or you have any other suggestion?

Best regards

I'm not a C# person, but in the database, double quotes implies case sensitivity, so perhaps try: UdtTypeName = "SYS.AQ$_JMS_TEXT_MESSAGE" — Connor McDonald
– Connor McDonald, Commented Apr 1, 2020 at 5:52

Dominic Cotton · Accepted Answer · 2020-05-29 13:34:20Z

I think you may need to create a C# class with the type SYS.AQ$_JMS_TEXT_MESSAGE specified. You will need a Factory class:

[OracleCustomTypeMapping("SYS.AQ$_JMS_TEXT_MESSAGE")]
public class OraclePayloadFactory : IOracleCustomTypeFactory
{
    public IOracleCustomType CreateObject()
    {
        return new OraclePayload();
    }
}

And then a Type as below:

public class OraclePayload : IOracleCustomType, INullable
{
    [OracleObjectMapping("FIELD")]
    public string Field{ get; set; }

    public void FromCustomObject(OracleConnection con, IntPtr pUdt)
    {
        OracleUdt.SetValue(con, pUdt, "FIELD", this.Field);
    }

    public void ToCustomObject(OracleConnection con, IntPtr pUdt)
    {
        if (OracleUdt.GetValue(con, pUdt, "FIELD") != null)
            Field = OracleUdt.GetValue(con, pUdt, "FIELD").ToString();
    }
}

Obviously you will need to match the elements of the oracle type to a C# equivalent.

Collectives™ on Stack Overflow

Oracle AQ c# and dequeueing a message from oracle queue in c#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related