Hi, I have 2 schemas: (A and B) 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 TESTING
DECLARE
BEGIN
enq_data('sdadasd');
END;
/
select * from test_table;
connect with user B
---- THIS PART IS FOR TESTING
DECLARE
BEGIN
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?