Home » Server Options » Streams & AQ » AQ plsql callback won't work (9i) (oracle 9i win xp)
AQ plsql callback won't work (9i) [message #417450] Sat, 08 August 2009 23:46
mikeverkimpe
Messages: 30
Registered: April 2007
Location: Belgium
Member
I use this example...

connect / as sysdba

create user aq identified by aq
default tablespace ts_data
temporary tablespace temp
quota unlimited on ts_data;

grant connect,
create type,
create procedure,
aq_administrator_role
to aq;

grant execute on dbms_aq to aq;
grant execute on dbms_aqadm to aq;

begin
dbms_aqadm.grant_system_privilege('ENQUEUE_ANY','AQ',FALSE);
dbms_aqadm.grant_system_privilege('DEQUEUE_ANY','AQ',FALSE);
end;
/

connect aq/aq

create Type aq.message_typ as object (
subject Varchar2(30), text Varchar2(80)
);
/


begin
dbms_aqadm.create_queue_table(
queue_table => 'aq.objmsgs80_qtab',
queue_payload_type => 'aq.Message_typ',
multiple_consumers => true);

dbms_aqadm.create_queue(
queue_name => 'MSG_QUEUE',
queue_table => 'aq.objmsgs80_qtab');

dbms_aqadm.start_queue(
queue_name => 'MSG_QUEUE');
end;
/






create procedure enqueue_msg( p_msg in varchar2 )
as
enqueue_options dbms_aq.enqueue_options_t;
message_properties dbms_aq.message_properties_t;
message_handle RAW(16);
message aq.message_typ;
begin

message := message_typ('NORMAL MESSAGE', p_msg );

dbms_aq.enqueue(queue_name => 'msg_queue',
enqueue_options => enqueue_options,
message_properties => message_properties,
payload => message,
msgid => message_handle);
end;
/



create table message_table(
msg Varchar2(4000)
);


create or replace procedure notifyCB( context raw,
reginfo sys.aq$_reg_info,
descr sys.aq$_descriptor,
payload raw,
payloadl number)
as
dequeue_options dbms_aq.dequeue_options_t;
message_properties dbms_aq.message_properties_t;
message_handle raw(16);
message aq.message_typ;
begin
dequeue_options.msgid := descr.msg_id;
dequeue_options.consumer_name := descr.consumer_name;

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

insert into message_table values(
'Dequeued and processed "' || message.text || '"' );

commit;
end;
/


begin
dbms_aqadm.add_subscriber(
queue_name => 'aq.msg_queue',
subscriber => sys.aq$_agent('recipient', null, null));
end;
/

begin
dbms_aq.register(sys.aq$_reg_info_list(
sys.aq$_reg_info('AQ.MSG_QUEUE:RECIPIENT',
DBMS_AQ.NAMESPACE_AQ,
'plsql://AQ.notifyCB',
HEXTORAW('FF')) ) ,
1);
end;
/

begin
enqueue_msg('Hello');
end;
/

After a short while:

select * from message_table;




Somehow the callback procedure is never called. I have no idea why... I don't want to use a dbms_job to do the dequeuing of my messages. So i need a callback. Anyone got an idea?

Kind regards,

Mike.
Previous Topic: steam re-instantiation
Next Topic: Oracle streams
Goto Forum:
  


Current Time: Sat Dec 10 03:24:28 CST 2016

Total time taken to generate the page: 0.04812 seconds