Working with two queues owned by different users [message #606152] |
Mon, 20 January 2014 07:50 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/7127069b0710e39b0d46dc30ad1e5101?s=64&d=mm&r=g) |
Anush87
Messages: 1 Registered: January 2014 Location: Armenia
|
Junior Member |
|
|
Dear All,
I am new to AQ. I am implementing AQ within oracle database.
I have two users, test1 and test2.
I have queue1 owned by test1 user and queue2 owned by test2 user.
I want to implement AQ within this queues (queue2 dequeues messages of queue1). I have already implemented AQ for one queue (test2 enqueues and dequeues messages in the queue owned by test1 user.) And please tell me in which cases which one of this types of AQ should I use. Also I would like to know is there any way to dequeue messages from queue2 automatically when they enqueued by queue1 ?
|
|
|
Re: Working with two queues owned by different users [message #630181 is a reply to message #606152] |
Mon, 22 December 2014 04:17 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/b7abf4d7c462cf4ff409e1e95bc92f10?s=64&d=mm&r=g) |
m3025869
Messages: 3 Registered: December 2014
|
Junior Member |
|
|
Here is example to propagate message from Q1 to Q2 ( Q1 at Database-A & Q2 at Database-B)
1. Create database link from Database-A to Database-B (e.g. DBLINKATOB)
2.Create object type for message payload on both databases ( Database-A & Database-B )
CREATE OR REPLACE TYPE q_msg_tp1 AS OBJECT (
msg VARCHAR2(40)
);
3. CREATE Q1 on Database-A
Begin
DBMS_AQADM.create_queue_table(
queue_table => 'q1_tab',
queue_payload_type => 'q_msg_tp1',
multiple_consumers => TRUE);
DBMS_AQADM.create_queue(
queue_name => 'q1',
queue_table => 'q1_tab');
DBMS_AQADM.start_queue(
queue_name => 'q1',
enqueue => TRUE);
End;
4. Schedule propagation on Q1 on Database-A
DECLARE
v_qname varchar2(50):='q1';
v_dest varchar2(50):='DBLINKATOB';
BEGIN
DBMS_AQADM.Schedule_Propagation(Queue_Name => v_qname,
Destination => v_dest,
Start_Time => sysdate,
Latency => 0);
END;
/
--------------
5. CREATE Q2 on Database-B
Begin
DBMS_AQADM.create_queue_table(
queue_table => 'q2_tab',
queue_payload_type => 'q_msg_tp1',
multiple_consumers => TRUE);
DBMS_AQADM.create_queue(
queue_name => 'q2',
queue_table => 'q2_tab');
DBMS_AQADM.start_queue(
queue_name => 'q2',
enqueue => TRUE);
End;
---
6. ENQUEUE message in Q1
Declare
enqueue_options dbms_aq.enqueue_options_t;
message_properties dbms_aq.message_properties_t;
message_handle RAW(16);
message q_msg_tp1;
recipients DBMS_AQ.aq$_recipient_list_t;
p_add varchar2(100);
BEGIN
p_add:='SCHEMA.Q2@DBLINKATOB';
recipients(1) := SYS.aq$_agent('RECIPIENT', p_add, null);
message_properties.recipient_list := recipients;
message := q_msg_tp1('MSG_'||to_char(sysdate,'DD/MM/YYYY hh24:mi:ss'));
dbms_aq.enqueue(queue_name => 'Q1',
enqueue_options => enqueue_options,
message_properties => message_properties,
payload => message,
msgid => message_handle);
commit;
end;
/
--***************
7. Dequeue message from Q2 at Database-B
DECLARE
l_dequeue_options DBMS_AQ.dequeue_options_t;
l_message_properties DBMS_AQ.message_properties_t;
l_message_handle RAW(16);
l_event_msg q_msg_tp1;
BEGIN
l_dequeue_options.consumer_name:='RECIPIENT';
DBMS_AQ.dequeue(queue_name => 'Q2',
dequeue_options => l_dequeue_options,
message_properties => l_message_properties,
payload => l_event_msg,
msgid => l_message_handle);
DBMS_OUTPUT.put_line('Message : ' || l_event_msg.msg);
DBMS_OUTPUT.put_line('------');
COMMIT;
END;
/
|
|
|
Re: Working with two queues owned by different users [message #630215 is a reply to message #630181] |
Tue, 23 December 2014 03:37 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
As I never used AQ, I can't comment your code; I hope someone will benefit from it, though it probably won't be the original poster as this question is almost a year old, so he - hopefully - got the answer elsewhere.
However, I'd suggest you to spare 10 seconds of your time and take a look at this short tutorial which will show you how to use [code] tags; they are useful when you post code (obviously) as they keep code formatting and make it easier to read.
|
|
|