Search This Blog

Saturday, August 11, 2012

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