2.4 AQ Operations and Workspace Manager Events

This section describes Advanced Queuing objects and techniques relevant to developers of applications that work with captured Workspace Manager events.

• Workspace Manager Event Queue Administration
  
• Privileges and Access Control for Queues
  
• Rule-Based Subscription
  
• Listening for Events
  
• Asynchronous Notification
  

2.4.1 Workspace Manager Event Queue Administration

Workspace Manager creates a multiconsumer queue named WMSYS.WM$EVENT_QUEUE based on a queue table named WMSYS.WM$EVENT_QUEUE_TABLE. The queue payload type is WMSYS.WM$EVENT_TYPE, which is an object type.

AQ creates some views for the queue that can be used for administrative purposes. Table 2-3 describes the views of interest to developers of Workspace Manager applications.

Table 2-3 AQ Administrative Views for Workspace Manager

View Name	Description
WMSYS.AQ$WM$EVENT_QUEUE_TABLE	Describes the queue table in which events are stored. This view can be used for querying the events. The AQ_ADMINISTRATOR_ROLE role and the WM_ADMIN system privilege are granted select privileges on this view.
WMSYS.AQ$WM$EVENT_QUEUE_TABLE_S	Displays all the subscribers for the event queue; also displays the transformation for the subscriber if it was created with one. The AQ_ADMINISTRATOR_ROLE role and the WM_ADMIN system privilege are granted select privileges on this view.
WMSYS.AQ$WM$EVENT_QUEUE_TABLE_R	Displays only the rule-based subscribers for all queues in a given queue table, as well as the text of the rule defined by each subscriber. Also displays the transformation for the subscriber if one was specified. The AQ_ADMINISTRATOR_ROLE role and the WM_ADMIN system privilege are granted select privileges on this view.

2.4.2 Privileges and Access Control for Queues

The database administrator has several options for granting privileges and access to queues. Some possible scenarios include:

• Grant the system privileges ENQUEUE ANY QUEUE and DEQUEUE ANY QUEUE directly to a database user by using the DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE procedure, and optionally later revoke privileges by using the DBMS_AQADM.REVOKE_SYSTEM_PRIVILEGE procedure.

• Grant the queue privileges ENQUEUE and DEQUEUE to the event queue WMSYS.WM$EVENT_QUEUE to a database user by using the DBMS_AQADM.GRANT_QUEUE_PRIVILEGE procedure, and optionally later revoke privileges by using the DBMS_AQADM.REVOKE_QUEUE_PRIVILEGE procedure.

• Grant the role AQ_ADMINISTRATOR_ROLE to a database user to give that user administrative privileges on any queue.

Example 2-2 shows privileges being granted for a user to subscribe to the event queue and dequeue events.

Example 2-2 Granting Privileges for Queue Access

-- Do the following while connected as SYSDBA.
-- These privileges are required for the user to execute AQ packages.
grant execute on DBMS_AQ to SCOTT ;
grant execute on DBMS_AQADM to SCOTT ;

-- Grant privilege to SCOTT for subscribing to the event queue.
exec DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE('MANAGE_ANY','SCOTT') ;

-- Grant privilege to SCOTT to dequeue events. (As an alternative, you could use
-- DBMS_AQADM.GRANT_QUEUE_PRIVILEGE to grant the DEQUEUE privilege on
-- WMSYS.WM$EVENT_QUEUE.)
exec DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE('DEQUEUE_ANY','SCOTT') ;

2.4.3 Rule-Based Subscription

An event can be delivered to multiple recipients based on event parameters. You can define a rule-based subscription for the event queue as the mechanism for specifying interest in receiving events. Subscriber rules are then used to evaluate recipients for event delivery. A null rule indicates that the subscriber wishes to receive all events.

Example 2-3 creates a rule-based subscription for user SCOTT to deliver WORKSPACE_MERGE_WO_REMOVE events when the parent workspace is the LIVE workspace.

Example 2-3 Rule-Based Subscription for Workspace Manager Events

rem =================================================
rem Create queue subscribers
rem Register for MergeWorkspace event when
rem a workspace is merged to LIVE
rem =================================================

connect scott
-- Enter password when prompted.

DECLARE
    subscriber sys.aq$_agent;
BEGIN
    subscriber := sys.aq$_agent('MERGE_LISTENER', NULL, NULL);
    dbms_aqadm.add_subscriber(
      queue_name => 'WMSYS.WM$EVENT_QUEUE',
      subscriber => subscriber,
      rule => 'tab.user_data.event_name = ''WORKSPACE_MERGE_WO_REMOVE''
               and tab.user_data.parent_workspace_name = ''LIVE''');
END;
/

2.4.4 Listening for Events

The listen call is a blocking call that can be used to wait for events on a queue or a list of subscriptions. If the listen returns successfully, a dequeue must be used to retrieve the event.

Example 2-4 listens for events on an event queue.

Example 2-4 Listening for a Workspace Manager Event

rem ==============================================================
rem The following example shows how an application can listen for
rem an event. Explicit dequeue must be performed to get the actual
rem event parameters. The user SCOTT must have sufficient privileges 
rem as described in the "Access Control" section.
rem ==============================================================

connect scott
-- Enter password when prompted.

set serveroutput on

DECLARE
 qlist dbms_aq.aq$_agent_list_t;
 agent_w_msg sys.aq$_agent;
 listen_timeout exception;
 pragma exception_init(listen_timeout, -25254);
BEGIN
 qlist(0) := sys.aq$_agent('MERGE_LISTENER', 'WMSYS.WM$EVENT_QUEUE', NULL);

 dbms_output.put_line ('Listening on event queue.');

 BEGIN

 DBMS_AQ.LISTEN(
         agent_list => qlist,
         wait => 30,
         agent =>  agent_w_msg);

         dbms_output.put_line(agent_w_msg.name) ;

         /* The event can be dequeued here to get the event data */

 EXCEPTION
        when listen_timeout THEN
                null;
 END;

END;
/

2.4.5 Asynchronous Notification

Asynchronous notification allows clients to receive notification of an event of interest. The client can use it to monitor multiple subscriptions. The client does not have to be connected to the database to receive notifications regarding its subscriptions.

If an application registers for asynchronous notification of Workspace Manager events using callbacks, the minimum values for the following init.ora parameters should be:

• aq_tm_processes = 1

• job_queue_processes = 2

Example 2-5 registers for a callback to receive asynchronous notification of events.

Example 2-5 Receiving Asynchronous Notification of Events

rem =====================================================
rem Example of how to register for a callback to the event
rem queue on behalf of a subscriber. Subscriber has already
rem been defined in previous section. The callback is
rem invoked by the AQ framework whenever an event satisfying the
rem rule for the subscriber occurs. The minimum values for
rem the following init.ora parameters should be set as follows.
rem   aq_tm_processes = 1
rem   job_queue_processes = 2
rem The user SCOTT must have sufficient privileges.
rem ===========================================================

CONNECT scott
-- Enter password when prompted.

CREATE TABLE merge_log
(
  event_name      varchar2(128),
  workspace_name  varchar2(128),
  parent_workspace_name varchar2(128),
  user_name       varchar2(128)
);

CREATE OR REPLACE PROCEDURE scott.event_callback(
   context RAW , reginfo sys.aq$_reg_info, descr sys.aq$_descriptor, 
   payload VARCHAR2,  payloadl NUMBER)
AS
    deq_msgid           RAW(16);
    dopt                dbms_aq.dequeue_options_t;
    mprop               dbms_aq.message_properties_t;
    event               WMSYS.WM$EVENT_TYPE;
    no_messages         exception;
    pragma exception_init(no_messages, -25228);

BEGIN
    dopt.consumer_name := 'MERGE_LISTENER';
    dopt.wait := 30;
    dopt.msgid := descr.msg_id;

    dbms_aq.dequeue(
        queue_name => 'WMSYS.WM$EVENT_QUEUE',
        dequeue_options => dopt,
        message_properties => mprop,
        payload => event,
        msgid => deq_msgid);

    INSERT INTO merge_log VALUES (event.event_name, event.workspace_name,
      event.parent_workspace_name, event.user_name);

    /* Note: If there are additional parameters stored in
       "aux_params" attribute, it can be accessed using 
       event.aux_params(1).name, event.aux_params(1).value, 
       event.aux_params(2).name … and so on. The number of 
       parameters can be accessed using event.aux_params.count 
       when aux_params is not null.
     */
END;
/

grant execute on scott.event_callback to public ;

rem ==================================================
rem Register a callback for the event
rem Queue name and subscriber name have to be specified
rem while registering for a callback
rem ==================================================

DECLARE
  reginfo1            sys.aq$_reg_info;
  reginfolist         sys.aq$_reg_info_list;
BEGIN
  reginfo1 := sys.aq$_reg_info('WMSYS.WM$EVENT_QUEUE:MERGE_LISTENER',1,'plsql://scott.event_callback?PR=1',HEXTORAW('FF'));

  reginfolist := sys.aq$_reg_info_list(reginfo1);

  sys.dbms_aq.register(reginfolist, 1);

  COMMIT;

END;
/