How to create and purge a AQ Queue/Topic
I was looking for testing the AQ Adapter and that forced me to look for steps on how to create and purge a AQ Queue/Topic? I referred the Article which explains all about AQ but in brief the steps are as under,Creating a AQ Queue:
1. Grant the privileges to the Database User
2. Create a Queue/Topic table
3. Create a Queue/Topic
4. Start the Queue/Topic
In SQL terms,
1. Grant the privileges:
connect as sys;
grant connect, resource, aq_administrator_role to [USER] identified by [USER];
grant execute on sys.dbms_aqadm to [USER];
grant execute on sys.dbms_aq to [USER];
grant execute on sys.dbms_aqin to [USER];
grant execute on sys.dbms_aqjms to [USER];
2. Combining steps 2,3 and 4 - Create a Queue table, Queue and Start the Queue:
connect as [USER];
begin
DBMS_AQADM.CREATE_QUEUE_TABLE ( queue_table => 'XMLType_Payload_In', queue_payload_type => 'SYS.XMLType');
DBMS_AQADM.CREATE_QUEUE ( queue_name => 'XMLType_Payload_In', queue_table => 'XMLType_Payload_In');
DBMS_AQADM.START_QUEUE ( queue_name => 'XMLType_Payload_In');
end;
commit;
This will create a XMLType_Payload_In table to hold the messages of XMLType in the database.
Purging a AQ Queue:
DECLARE
po dbms_aqadm.aq$_purge_options_t;
BEGIN
po.block := FALSE;
DBMS_AQADM.PURGE_QUEUE_TABLE(
queue_table => 'XMLTYPE_PAYLOAD_IN',
purge_condition => NULL,
purge_options => po);
END;
The above block will purge all the records from the Queue.
Source: http://ora-soa.blogspot.se/2008/06/steps-to-create-aq-queuetopic.html
No comments:
Post a Comment