Currently Being Moderated
Hi,
Thanks for your reply. Can you tell me specifically how can I notify in PLSQL that a message has been enqueued?
I have read that we can use DBMS_AQ.REGISTER to notify. I have created the required procedures but it does not automatically notify when message arrives in queue.(nothing happens to the enqueued message)
I have followed the steps as:
1) Created Object Type, Created queue table of this object type, and queue of this table type.
2) Written a procedure to enqueue the data to the queue:
DECLARE
r_enqueue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
r_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
v_message_handle RAW(16);
o_payload aq_test_subs_type;
BEGIN
o_payload := aq_test_subs_type('<EMPLOYEES>
<EMP>
<EMPNO>7369</EMPNO>
<ENAME></ENAME>
<JOB>CLERK</JOB>
<HIREDATE>17-DEC-80</HIREDATE>
<SAL>800</SAL>
</EMP>
<EMP>
<EMPNO>7499</EMPNO>
<ENAME>ALLEN</ENAME>
<JOB>SALESMAN</JOB>
<HIREDATE>20-FEB-81</HIREDATE>
<SAL>1600</SAL>
<COMM>300</COMM>
</EMP>
<MGR>
<MGRNO>7369</MGRNO>
<MGRNAME>SMITH</MGRNAME>
<MGRJOB>CLERK</MGRJOB>
<MGRHIREDATE>17-DEC-80</MGRHIREDATE>
<MGRSAL>800</MGRSAL>
</MGR>
</EMPLOYEES>');
DBMS_AQ.ENQUEUE(
queue_name => 'aq_test_subs_queue',
enqueue_options => r_enqueue_options,
message_properties => r_message_properties,
payload => o_payload,
msgid => v_message_handle
);
COMMIT;
END;
3) Created pl sql callback.
create or replace procedure dequeue_using_agent
2 ( context in raw
3 , reginfo in sys.aq$_reg_info
4 , descr in sys.aq$_descriptor
5 , payload in aq_test_subs_type
6 , payloadl in number
7 )
8 is
9 begin
10 /*logic to dequeue data*/
11 end dequeue_using_agent;
4) Registered the call back:
declare
l_reg_info_list sys.aq$_reg_info_list;
begin
l_reg_info_list := sys.aq$_reg_info_list(sys.aq$_reg_info('XXCUS.aq_test_subs_queue'
, DBMS_AQ.NAMESPACE_AQ
, 'plsql://XXCUS.dequeue_using_agent?PR=0'
, hextoraw('FF')));
dbms_aq.register(l_reg_info_list, 1);
end;
Can you hep me by telling if I have missed any steps?
Thanks,
Ritika
Edited by: Ritika on Nov 10, 2010 10:46 PM
