Initial Setup

Users

Create a user to own and administrate the AQ implementation...

sqlplus / as sysdba

CREATE USER aq_admin IDENTIFIED BY "&password" 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 a user who will use the AQ implementation. In a live database there may be several AQ users who may also be schema owners...

CREATE USER aq_user1 IDENTIFIED BY "&password" DEFAULT TABLESPACE users;

GRANT connect TO aq_user1;

GRANT aq_user_role TO aq_user1;

For demonstration purposes we will create a second user who will communicate via AQ with the first user...

CREATE USER aq_user2 IDENTIFIED BY "&password" DEFAULT TABLESPACE users;

GRANT connect TO aq_user2;

GRANT aq_user_role TO aq_user2;

Types

The payload of a message is defined by a TYPE...

CONNECT aq_admin

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_user1;

GRANT EXECUTE ON event_msg_type TO aq_user2;

Queue

CONNECT aq_admin

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 privileges on queues to all users (with aq_user_role as above) who you want to access them...

CONNECT aq_admin

EXECUTE DBMS_AQADM.grant_queue_privilege ( -

   privilege     =>     'ALL', -

   queue_name    =>     'aq_admin.event_queue', -

   grantee       =>     'aq_user1', -

   grant_option  =>      FALSE);

EXECUTE DBMS_AQADM.grant_queue_privilege ( -

   privilege     =>     'ALL', -

   queue_name    =>     'aq_admin.event_queue', -

   grantee       =>     'aq_user2', -

   grant_option  =>      FALSE);

Enqueue Message

CONNECT aq_user1

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;

/

To see the message in the queue...

connect aq_admin

SELECT *

FROM AQ$event_queue_tab;

Dequeue Message

CONNECT aq_user2

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;

/

Check Setup

These queries will help you verify how AQ has been setup on your system...

Users

SELECT granted_role, grantee

FROM dba_role_privs

WHERE granted_role IN ('AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE');

Who can ENQUEUE?

SELECT grantee, privilege

FROM dba_sys_privs

WHERE privilege = 'ENQUEUE ANY QUEUE';

SET LINESIZE 200

SELECT grantee, owner, table_name, grantor, privilege

FROM dba_tab_privs

WHERE privilege = 'ENQUEUE';

Who can DEQUEUE?

SELECT grantee, privilege

FROM dba_sys_privs

WHERE privilege = 'DEQUEUE ANY QUEUE';

SELECT grantee, owner, table_name, grantor, privilege

FROM dba_tab_privs

WHERE privilege ='DEQUEUE';

Who can MANAGE Queues?

SELECT grantee, privilege

FROM dba_sys_privs

WHERE privilege = 'MANAGE ANY QUEUE';

SELECT grantee, owner, table_name, grantor, privilege

FROM dba_tab_privs

WHERE privilege ='MANAGE';

Types

SELECT object_type 

FROM dba_queue_tables

WHERE object_type IS NOT NULL

AND SUBSTR(object_type,1,3) != 'SYS'

Queues

SELECT *

FROM dba_queue_tables;

SELECT *

FROM dba_queues;

Bibliography