| Working with two queues owned by different users [message #606152] |
Mon, 20 January 2014 07:50  |
 |
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  |
 |
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 |
 |
Littlefoot
Messages: 21823
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
