Hi, I have messages in my exception queue and what I want is enqueue them again as normal messages so they get processed once more. I've tried different variations of this script and it's not working, messages are enqueued again directly to the exception queue and not the “normal” one. I've noticed that they get enqueued normally again if I change some param like ‘correlation’ but for some reason when I try to enqueue again the exact same message, Oracle won't let me.
DECLARE
l_enqueue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
l_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
l_dequeue_options DBMS_AQ.DEQUEUE_OPTIONS_T;
l_message_handle RAW(16);
l_payload AQ$_JMS_OBJECT_MESSAGE;
BEGIN
l_message_handle := HEXTORAW('12345');
l_dequeue_options.msgid := l_message_handle;
l_dequeue_options.wait := DBMS_AQ.NO_WAIT; -- do not block if message not found
DBMS_AQ.DEQUEUE(
queue_name => 'USER.AQ$_MY_QUEUE_TABLE_E',
dequeue_options => l_dequeue_options,
message_properties => l_message_properties,
payload => l_payload,
msgid => l_message_handle
);
COMMIT;
DBMS_AQ.ENQUEUE(
queue_name => 'USER.MY_QUEUE',
enqueue_options => l_enqueue_options,
message_properties => l_message_properties,
payload => l_payload,
msgid => l_message_handle
);
COMMIT;
END;