-- ---------------------------------------
-- Create a user and assign the privileges
-- ---------------------------------------
GRANT connect, resource TO ORACLEAQJMSUSER IDENTIFIED BY ORACLEAQJMSUSER;
GRANT aq_user_role TO ORACLEAQJMSUSER;
GRANT execute ON sys.dbms_aqadm TO ORACLEAQJMSUSER;
GRANT execute ON sys.dbms_aq TO ORACLEAQJMSUSER;
GRANT execute ON sys.dbms_aqin TO ORACLEAQJMSUSER;
GRANT execute ON sys.dbms_aqjms TO ORACLEAQJMSUSER;

-- --------------------
-- Create a queue table
-- --------------------
connect ORACLEAQJMSUSER/ORACLEAQJMSUSER;

BEGIN
dbms_aqadm.create_queue_table (
queue_table => 'AQ_MESSAGE_QUEUE_TABLE',
queue_payload_type => 'sys.aq$_jms_text_message',
multiple_consumers => true
);
END;

-- --------------
-- Create a queue
-- --------------
BEGIN
  DBMS_AQADM.CREATE_QUEUE (
    queue_name  => 'AQ_MESSAGE_QUEUE',
    queue_table => 'AQ_MESSAGE_QUEUE_TABLE'
  );
  DBMS_AQADM.START_QUEUE (
    queue_name => 'AQ_MESSAGE_QUEUE'
  );
END;



/* Drop queue: */  
EXECUTE dbms_aqadm.drop_queue (Queue_name => 'ORACLEAQJMSUSER.AQ_MESSAGE_QUEUE');  
EXECUTE dbms_aqadm.drop_queue (Queue_name => 'ORACLEAQJMSUSER.AQ_MESSAGE_QUEUE_TABLE');   

/*Drop Queue Table */
EXECUTE dbms_aqadm.drop_queue_table (queue_table => 'ORACLEAQJMSUSER.AQ_MESSAGE_QUEUE');  
EXECUTE dbms_aqadm.drop_queue_table (queue_table => 'ORACLEAQJMSUSER.AQ_MESSAGE_QUEUE_TABLE');   



-- -------------------
-- Create a subscriber (aka agent)
-- You only need a subscriber (or a list of recipients) for multiple consumers on the queue (subscribers are not needed for non-multiple consumers)
-- -------------------
/* Anonymous PL/SQL block for adding a subscriber at a designated queue in a 
designated schema at a database link: */ 
DECLARE 
   subscriber          sys.aq$_agent; 
BEGIN 
	/*sys.aq$_agent (, ,*/
   subscriber := sys.aq$_agent ('OIM_SUBSCRIBER', 'null', 0); 
   DBMS_AQADM.ADD_SUBSCRIBER(
     queue_name         => 'ORACLEAQJMSUSER.AQ_MESSAGE_QUEUE', 
      subscriber            =>  subscriber); 
 END; 
 

About the author