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:
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?
Post a Comment