Oracle Advance Queue - Dequeue callback not working

Raivis

Hi, I have 2 schemas: (A and In schema A i created advance queue and dequeue callback function. From schema A I granted schema B to execute enqueue procedure. When i enqueue data from schema A -> all is working fine. When i call A.enqueue data procedure from schema B -> data are enqueued, but they remain in READY state.... The dequeue callback function isn't triggered. Here is test script:

create table test_table (id number, event_name varchar2(200), descr nvarchar2(200));/create or replace type test_type as object (id number, event_name varchar2(200), descr nvarchar2(200))/create or replace procedure test_proc(context  in raw,                                      reginfo  in sys.aq$_reg_info,                                      descr    in sys.aq$_descriptor,                                      payload  in raw,                                      payloadl in number) as  v_dequeue_options    dbms_aq.dequeue_options_t;  v_message_id         raw(16);  v_payload_message    test_type;  v_message_properties dbms_aq.message_properties_t;begin  v_dequeue_options.msgid         := descr.msg_id;  v_dequeue_options.consumer_name := descr.consumer_name;  v_dequeue_options.wait          := dbms_aq.no_wait;  DBMS_AQ.DEQUEUE(queue_name         => 'event_queue',                  dequeue_options    => v_dequeue_options,                  message_properties => v_message_properties,                  payload            => v_payload_message,                  msgid              => v_message_id);  insert into test_table    (id, event_name, descr)  values    (v_payload_message.id, v_payload_message.event_name, v_payload_message.descr);  commit;end;/create or replace procedure enq_data(p_msg in varchar2) is  l_enqueue_options    DBMS_AQ.enqueue_options_t;  l_message_properties DBMS_AQ.message_properties_t;  l_message_handle     raw(16);  l_event_msg          test_type;begin  l_event_msg := test_type(2, p_msg, null);  DBMS_AQ.enqueue(queue_name         => 'cp.event_queue',                  enqueue_options    => l_enqueue_options,                  message_properties => l_message_properties,                  payload            => l_event_msg,                  msgid              => l_message_handle);  commit;end;/begin DBMS_AQADM.create_queue_table(     queue_table        =>  'event_queue_tab',     --sort_list => 'COMMIT_TIME',    multiple_consumers => false,    message_grouping => sys.dbms_aqadm.none,    compatible => '10.0.0',    primary_instance => 0,      secondary_instance => 0,    queue_payload_type =>  'test_type');   DBMS_AQADM.create_queue(     queue_name         =>  'event_queue',     queue_table        =>  'event_queue_tab');  DBMS_AQADM.start_queue(     queue_name         => 'event_queue',     enqueue            => TRUE);end;/begin  dbms_aq.register (     sys.aq$_reg_info_list (        sys.aq$_reg_info (user || '.' || upper('event_queue'),                          dbms_aq.namespace_aq,                          'plsql://' || user || '.test_proc',                          hextoraw ('FF'))),     1);  commit;end;/

grant execute on enq_data to B;grant select on test_table to B;

connect with user A

---- THIS PART IS FOR TESTINGDECLAREBEGIN  enq_data('sdadasd');END;/select * from test_table;

connect with user B

---- THIS PART IS FOR TESTINGDECLAREBEGIN  A.enq_data('sdadasd');END;/select * from A.test_table;

I see that records are in READY state:

select * from aq$event_queue_tab;

Why so....What am i doing wrong?

User_XLYER

I might be wrong, can you try giving grant to execute the dequeue proc and also DML to B on A and give a try