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