Oracle AQ - pl/sql notification does not work

 I fought  with the issue of pl/sql notification in the Advanced Queuing. Sending a message to the queue, received the status of "READY". Clearing the queue did not help. The problem was only with the  PL/SQL notification and for mail notification, everything worked without problems.

I noticed that the automatic job AQ$_PLSQL_NTFN calling the callback function waits and locks row in the table  SYS.AQ$AQ_SRVNTFN_TABLE_1.

Helped clear the table by a modified procedure  from http://riyazmsm.blogspot.com:

Appropriate queue name  for pl/sql notification you can check using:
select * from sys.AQ_SRVNTFN_TABLE_1 


EXEC DBMS_AQADM.START_QUEUE('AQ$_AQ_SRVNTFN_TABLE_1_E',false, true); 

DECLARE
  dequeue_options DBMS_AQ.dequeue_options_t;
  message_properties DBMS_AQ.message_properties_t;
  dq_msgid RAW(16);
  payload RAW(1);
  no_messages EXCEPTION;
  pragma exception_init (no_messages, -25263);
  msg_count NUMBER(2);
  CURSOR c_msg_ids
  is
    SELECT MSG_ID FROM sys.AQ$AQ_SRVNTFN_TABLE_1 WHERE queue = 'AQ$_AQ_SRVNTFN_TABLE_1_E';
BEGIN
  dequeue_options.wait         := DBMS_AQ.NO_WAIT;
  dequeue_options.navigation   := DBMS_AQ.FIRST_MESSAGE;
  dequeue_options.dequeue_mode := dbms_aq.remove_nodata;
  FOR v_msg_id                 IN c_msg_ids
  LOOP
    dequeue_options.msgid := v_msg_id.msg_id;
    MSG_COUNT             := 0;
    DBMS_AQ.DEQUEUE(queue_name => 'sys.AQ$_AQ_SRVNTFN_TABLE_1_E', dequeue_options => dequeue_options, message_properties => message_properties, payload => payload, msgid => dq_msgid);
    dbms_output.put_line('Message id : '||v_msg_id.msg_id||' removed');
    msg_count                  := msg_count + 1;
    dequeue_options.msgid      := NULL;
    dequeue_options.navigation := DBMS_AQ.NEXT_MESSAGE;
  END LOOP;
commit;
EXCEPTION
WHEN no_messages THEN
  DBMS_OUTPUT.PUT_LINE ('No of Messages Removed: '||msg_count);
  COMMIT;
END;
/

or

 EXEC DBMS_AQADM.START_QUEUE('AQ_SRVNTFN_TABLE_Q_1',false, true);  

DECLARE
  dequeue_options DBMS_AQ.dequeue_options_t;
  message_properties DBMS_AQ.message_properties_t;
  dq_msgid RAW(16);
  payload RAW(1);
  no_messages EXCEPTION;
  pragma exception_init (no_messages, -25263);
  msg_count NUMBER(2);
  CURSOR c_msg_ids
  is
    SELECT MSG_ID FROM sys.AQ$AQ_SRVNTFN_TABLE_1 WHERE queue = 'AQ_SRVNTFN_TABLE_Q_1';
BEGIN
  dequeue_options.wait         := DBMS_AQ.NO_WAIT;
  dequeue_options.navigation   := DBMS_AQ.FIRST_MESSAGE;
  dequeue_options.dequeue_mode := dbms_aq.remove_nodata;
  FOR v_msg_id                 IN c_msg_ids
  LOOP
    dequeue_options.msgid := v_msg_id.msg_id;
    MSG_COUNT             := 0;
    DBMS_AQ.DEQUEUE(queue_name => 'sys.AQ_SRVNTFN_TABLE_Q_1', dequeue_options => dequeue_options, message_properties => message_properties, payload => payload, msgid => dq_msgid);
    dbms_output.put_line('Message id : '||v_msg_id.msg_id||' removed');
    msg_count                  := msg_count + 1;
    dequeue_options.msgid      := NULL;
    dequeue_options.navigation := DBMS_AQ.NEXT_MESSAGE;
  END LOOP;
commit;
EXCEPTION
WHEN no_messages THEN
  DBMS_OUTPUT.PUT_LINE ('No of Messages Removed: '||msg_count);
  COMMIT;
END;
/

After that database should be restarted.

It is interesting that in this table were the messages until the queue to work properly.