Advanced Queuing In Oracle9i, Database Articles, Global Guide Line Technology is a Mega IT Portal.
Google
Join Global Guide Line community

     Home                    

   First Website Guide       

   Learn HTML                 

   Learn CSS                    

 

   Learn XML                    

   Learn XSLT                  

   Learn Java Script          

   Learn SEO                   

   Learn SQL                   

   Database Articles         

   Web Hosting Guide      

   Services                       

   Contacts                       

Advanced Queuing In Oracle9i
     Back             Next     

This article is a basic primer to get you started using Advanced Queuing in Oracle9i and Oracle8i:
 

Environment Setup

Administration and access privileges for advanced queuing are controled using two roles:
 

  • AQ_ADMINISTRATOR_ROLE - Allows creation and administration of queuing infrastructure.
  • AQ_USER_ROLE - Allows access to queues for enqueue and dequeue operations.

In the following examples I have used two schemas, one owning the queuing infrastructure and another with access to it:
 

CONNECT system/password@tsh9i
CREATE USER aq_admin IDENTIFIED BY aq_admin DEFAULT TABLESPACE users;
GRANT connect TO aq_admin;
GRANT create type TO aq_admin;
GRANT aq_administrator_role TO aq_admin;
ALTER USER aq_admin QUOTA UNLIMITED ON users;

CREATE USER aq_user IDENTIFIED BY aq_user DEFAULT TABLESPACE users;
GRANT connect TO aq_admin;
GRANT aq_user_role TO aq_user;

Define Payload

The content, or payload, of a message is defined using an object type which must be defined before a queue is created:
 

CONNECT aq_admin/aq_admin@tsh9i

CREATE OR REPLACE TYPE event_msg_type AS OBJECT (
  name            VARCHAR2(10),
  current_status  NUMBER(5),
  next_status     NUMBER(5)
);
/

GRANT EXECUTE ON event_msg_type TO aq_user;

Create Queue Table And Queue

Once the payload type is created the queuing infrastructure can be created. Queues are implemented using a queue table which can hold multiple queues with the same payload type. First the queue table must be defined using the payload type, then the queue can be defined and started. These operations are all performed using the DBMS_AQADM package:
 

CONNECT aq_admin/aq_admin@tsh9i

EXECUTE DBMS_AQADM.create_queue_table ( -
   queue_table            =>  'aq_admin.event_queue_tab', -
   queue_payload_type     =>  'aq_admin.event_msg_type');

EXECUTE DBMS_AQADM.create_queue ( -
   queue_name            =>  'aq_admin.event_queue', -
   queue_table           =>  'aq_admin.event_queue_tab');



EXECUTE DBMS_AQADM.start_queue ( -
   queue_name         => 'aq_admin.event_queue', -
   enqueue            => TRUE);

Grant Privilege On Queue

The DBMS_AQADM package is also used to grant privileges on queues so that other users can access them:
 

CONNECT aq_admin/aq_admin@tsh9i

EXECUTE DBMS_AQADM.grant_queue_privilege ( -
   privilege     =>     'ALL', -
   queue_name    =>     'aq_admin.event_queue', -
   grantee       =>     'aq_user', -
   grant_option  =>      FALSE);

At this point the queue can be used for enqueue and dequeue operations by the AQ_USER user.
 

Enqueue Message

Messages can be written to the queue using the DBMS_AQ.ENQUEUE procedure:
 

CONNECT aq_user/aq_user@tsh9i

DECLARE
  l_enqueue_options     DBMS_AQ.enqueue_options_t;
  l_message_properties  DBMS_AQ.message_properties_t;
  l_message_handle      RAW(16);
  l_event_msg           AQ_ADMIN.event_msg_type;
BEGIN
  l_event_msg := AQ_ADMIN.event_msg_type('REPORTER', 1, 2);

  DBMS_AQ.enqueue(queue_name          => 'aq_admin.event_queue',        
                  enqueue_options     => l_enqueue_options,     
                  message_properties  => l_message_properties,   
                  payload             => l_event_msg,             
                  msgid               => l_message_handle);

  COMMIT;
END;
/

Dequeue Message

Messages can be read from the queue using the DBMS_AQ.DEQUEUE procedure:
 

CONNECT aq_user/aq_user@tsh9i

SET SERVEROUTPUT ON

DECLARE
  l_dequeue_options     DBMS_AQ.dequeue_options_t;
  l_message_properties  DBMS_AQ.message_properties_t;
  l_message_handle      RAW(16);
  l_event_msg           AQ_ADMIN.event_msg_type;
BEGIN
  DBMS_AQ.dequeue(queue_name          => 'aq_admin.event_queue',
                  dequeue_options     => l_dequeue_options,
                  message_properties  => l_message_properties,
                  payload             => l_event_msg,
                  msgid               => l_message_handle);

  DBMS_OUTPUT.put_line ('Event Name          : ' || 
l_event_msg.name);
  DBMS_OUTPUT.put_line ('Event Current Status: ' || 
l_event_msg.current_status);
  DBMS_OUTPUT.put_line ('Event Next Status   : ' || 
l_event_msg.next_status);
  COMMIT;
END;
/

Variations

The DEQUEUE_OPTIONS_T, DEQUEUE_OPTIONS_T and MESSAGE_PROPERTIES_T types can be used to vary the way messages are enqueued and dequeued. This is where the real flexibility of advanced queuing becomes evident. The discussion of these options is beyond the scope of this article.

Hope this helps. Regards Tim...



     Back             Next     
 

[ About ] [ Contact ] [ Home ]
[ Links ] [ Site Map ] [ Services ] [ Privacy ]
Copyright © 2005-2006 www.globalguideline.com All rights reserved. Join Global Guide Line community.