Oracle Mutli Consumer queue message not removed
Using a Oracle Multi Consumer Queue the messages remains in the queue table after it was dequeued. retention is set to 0.
Creating the Queue Table:
BEGIN DBMS_AQADM.CREATE_QUEUE_TABLE(
Queue_table => '"ZEE_EXEC"."ZEE_SYNC"',
Queue_payload_type => 'ZEE_EXEC.T_SYNC',
storage_clause => 'PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE ZEE_GENERAL',
Sort_list => 'ENQ_TIME',
Multiple_consumers => TRUE,
Compatible => '10.0.0');
END;
Creating the Queue:
BEGIN DBMS_AQADM.CREATE_QUEUE(
Queue_name => 'ZEE_EXEC.SYNCQUEUE',
Queue_table => 'ZEE_EXEC.ZEE_SYNC',
Queue_type => 0,
Max_retries => 5,
Retry_delay => 0,
dependency_tracking => FALSE);
END;
Dequeuing using PLSQL:
DECLARE
dequeue_options DBMS_AQ.dequeue_options_t;
message_properties DBMS_AQ.message_properties_t;
message_handle RAW(16);
message T_SYNC;
BEGIN
dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE;
dequeue_options.consumer_name := 'ZEE_TOPIC';
DBMS_AQ.DEQUEUE(
queue_name => 'ZEESYNCQUEUE',
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => message,
msgid => message_handle);
COMMIT;
END;
Dequeue Using JAVA (sample):
q_sess = q_conn.createQueueSession(true, Session.AUTO_ACKNOWLEDGE);
topic = ((AQjmsSession) q_sess).getTopic("ZEE_EXEC", "SYNCQUEUE");
ORADataFactory orad = TSync.getORADataFactory();
receiver = ((AQjmsSession) q_sess).createTopicReceiver(topic, "ZEE_TOPIC", null, orad);
I have tried using different implementations. I can explicitly set session transaction to true or false, acknowledgement to either AUTO_ACKNOWLEDGEMENT, CLIENT_ACKNOWLEDGEMENT, SESSION_TRANSACTED it makes no difference. The messages are received when dequeueing using PLSQL and JAVA but the message remains in the queue. The Retention is set to 0. Using JAVA acknowledgement is done on the message, the topicSession and the session and makes no difference.
Im not strong on the Database side, however research revealed:
SELECT * FROM dba_tab_privs WHERE grantee = 'ZEE_EXEC' AND table_name = 'dbms_aqjms';
Should show some results but it doesn't. Is it possible that I don't have enough privileges to dequeue the message? if so how come I can see it but not acknowledge it?
Debugging the application I get no exceptions, it acknowledges on the message and commits on the sessions fine, the only time I do get an exception is when I set session transacted to false.
链接地址: http://www.djcxy.com/p/76430.html上一篇: Oracle高级队列监听.Net WCF