Wednesday, June 4, 2008

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.

1 comment:

V.Vijayaratha said...

Hi,
Im stuck with creating queuetable.Im always getting"ORA-01017: invalid username/password; logon denied".
I tried with number of users but samething happens..
This is my sample code;
QFac = AQjmsFactory.getQueueConnectionFactory(hostname, oracle_sid, portno, driver);
System.out.println("Connection factory = " + QFac.toString());
// create connection
QCon = QFac.createQueueConnection(userName, password);
System.out.println("Created connection = " + QCon.toString());
// create session
session = QCon.createQueueSession(true, Session.CLIENT_ACKNOWLEDGE);
System.out.println("Created session = " + session.toString());
// start connection
QCon.start();

q_table =
((AQjmsSession) session).createQueueTable("ratha", "test_queue_table",
qt_prop);

And getting;

oracle.jms.AQjmsException: ORA-01017: invalid username/password; logon denied
ORA-06512: at "SYS.DBMS_AQADM", line 81
ORA-06512: at line 1


at oracle.jms.AQjmsSession.createQueueTable(AQjmsSession.java:4803)
at oracle.jms.AQjmsSession.createQueueTable(AQjmsSession.java:4778)

Can you provide any clue to overcome this issue?

Search This Blog