Database Software

I have created the queue table, queue and added the subscriber.

The subscriber agent is also an advanced queue. I have been facing problems with the Dequeueing bit. When I execute the Dequeue procedure, am getting this error.

Error Code -25228
Error msg ORA-25228: timeout or end-of-fetch during message dequeue from "queue_name"

So no messages are dequeued to the Subscriber Queue Table.
Could you please help me in finding out why it’s not dequeueing to the Queue Table?


DECLARE
dequeue_options DBMS_AQ.dequeue_options_t;
message_properties DBMS_AQ.message_properties_t;
message_handle RAW(16);
message RAW(16);
no_messages exception;
pragma exception_init (no_messages, -25228);
cnt INTEGER:=0;

BEGIN
dequeue_options.wait := DBMS_AQ.NO_WAIT;

/* Consumer will get MESSAGE: */
dequeue_options.consumer_name := 'SUBSCRIBER_QUEUE';
DBMS_OUTPUT.PUT_LINE ('Queue name - '||dequeue_options.consumer_name);

dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE;

LOOP
DBMS_AQ.DEQUEUE(queue_name => 'MAIN_QUEUE',
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => message,
msgid => message_handle);

DBMS_OUTPUT.PUT_LINE ('Msg id '||message_handle);
dequeue_options.navigation := DBMS_AQ.NEXT_MESSAGE;
COMMIT;
cnt:=cnt+1;
DBMS_OUTPUT.PUT_LINE ('No of Messages '||cnt);

END LOOP;

EXCEPTION
WHEN no_messages THEN
DBMS_OUTPUT.PUT_LINE ('Error Code '||sqlcode||' Error msg '||sqlerrm);
COMMIT;
END;