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

4221528

Hello,

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

Alex Keh-Oracle

ODP.NET AQ does not support JMS types.

Alex Keh-Oracle

ODP.NET AQ does not support JMS types.