Messages are successfully enqueueing but not dequeuing and are stuck in ready state (STATE = 0). The ENQ_TIME is 5 hours ahead of system time. In one environment, AQ is working (10g 10.2.0.4.0). In the other environment, it is not working (11g 11.2.0.3.0).
I just did the following:
1. Purged queue table
2. Stopped queues
3. Dropped queues
4. Dropped queue table
5. Created queue table
6. Created queues
7. Started queues
I tested once and a record was inserted in the queue table:
MSGID <msgid>
CORRID
PRIORITY 1
STATE 0
DELAY
EXPIRATION
TIME_MANAGER_INFO
LOCAL_ORDER_NO 0
CHAIN_NO 0
CSCN 0
DSCN 0
ENQ_TIME 12/23/2014 4:33:43.338902 PM
ENQ_UID <enq_uid>
ENQ_TID <enq_tid>
DEQ_TIME
DEQ_UID
DEQ_TID
RETRY_COUNT 0
EXCEPTION_QSCHEMA
EXCEPTION_QUEUE
STEP_NO 0
RECIPIENT_KEY 0
DEQUEUE_MSGID
SENDER_NAME
SENDER_ADDRESS
SENDER_PROTOCOL
USER_DATA <user_data>
USER_PROP
Notice the RETRY_COUNT is 0. The ENQ_TIME is 5 hours ahead. In the procedures to enqueue and dequeue, there are no errors.
Following is the plsql to enqueue:
CREATE OR REPLACE PACKAGE BODY
pkg_2
AS
FUNCTION queue_create_thing ( <parameters> )
RETURN NUMBER
IS
enqueue_options dbms_aq.enqueue_options_t;
message_properties dbms_aq.message_properties_t;
message_handle RAW(16);
v_message msg_type;
v_thing_id things.id%TYPE;
BEGIN
v_message := msg_type( <parameters> );
dbms_aq.enqueue(queue_name => '<queue name>',
enqueue_options => enqueue_options,
message_properties => message_properties,
payload => v_message,
msgid => message_handle);
RETURN v_thing_id;
EXCEPTION
WHEN OTHERS
THEN
errpkg.record_and_stop (SQLCODE);
END queue_create_thing;
PROCEDURE queue_delete_thing( <parameters> )
IS
enqueue_options dbms_aq.enqueue_options_t;
message_properties dbms_aq.message_properties_t;
message_handle RAW(16);
v_message msg_type;
BEGIN
v_message := msg_type( <parameters> );
dbms_aq.enqueue(queue_name => '<queue name>',
enqueue_options => enqueue_options,
message_properties => message_properties,
payload => v_message,
msgid => message_handle);
END;
END pkg_2;
Following is the code to dequeue:
CREATE OR REPLACE PACKAGE BODY
pkg_1
AS
PROCEDURE create_thing ( context IN RAW,
reginfo IN sys.aq$_reg_info,
descr IN sys.aq$_descriptor,
payload IN RAW,
payloadl IN NUMBER )
IS
dequeue_options dbms_aq.dequeue_options_t;
message_properties dbms_aq.message_properties_t;
message_handle RAW(16);
message msg_type;
BEGIN
dequeue_options.msgid := descr.msg_id;
dequeue_options.consumer_name := descr.consumer_name;
DBMS_AQ.DEQUEUE(queue_name => descr.queue_name,
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => message,
msgid => message_handle);
pkg_2.create_thing( p_thing_id => message.thing_id );
UPDATE table t
SET creation_complete = 1
WHERE id = message.thing_id;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
plog.error(SQLERRM);
plog.full_call_stack;
END create_thing;
PROCEDURE delete_thing ( context IN RAW,
reginfo IN sys.aq$_reg_info,
descr IN sys.aq$_descriptor,
payload IN RAW,
payloadl IN NUMBER )
IS
dequeue_options dbms_aq.dequeue_options_t;
message_properties dbms_aq.message_properties_t;
message_handle RAW(16);
message msg_type;
BEGIN
dequeue_options.msgid := descr.msg_id;
dequeue_options.consumer_name := descr.consumer_name;
DBMS_AQ.DEQUEUE(queue_name => descr.queue_name,
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => message,
msgid => message_handle);
pkg_2.delete_thing( p_thing_id => message.thing_id );
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
plog.error(SQLERRM);
plog.full_call_stack;
END delete_thing;
END pkg_1;