Skip Headers
Oracle® Application Server Portal Developer's Guide
10g Release 2 (10.1.4)
B14135-02
  Go To Documentation Library
Home
Go To Product List
Solution Area
Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
Next
Next
 

16 Using the Content Management Event Framework

This chapter describes the OracleAS Portal Content Management Event Framework (CMEF). It contains the following sections:

16.1 What Is the Content Management Event Framework?

CMEF enables you to extend OracleAS Portal's content management functionality by adding programmatic hooks to certain pre-defined portal events. The framework publishes these events to an Oracle database queue. This allows third party programs to subscribe to these events and to use the APIs to extend your portal. In this way, you can use page and item related events within a portal to trigger actions within one or more external applications.

16.2 How Does the Content Management Event Framework Work?

CMEF uses Oracle Streams Advanced Queuing (AQ) technology. Oracle Streams AQ is an Oracle database component that provides a message-queue system with a rich and industry standard feature set. Oracle Streams AQ offers:

More on OTN

For more information, refer to the Oracle Streams Advanced Queuing page on the Oracle Technology Network (OTN):

http://www.oracle.com/technology/products/aq

Actions within a portal (through the OracleAS Portal user interface, the Web-based Distributed Authoring and Versioning (WebDAV) protocol, or PL/SQL APIs) trigger CMEF events. These events cause OracleAS Portal to publish CMEF messages to a queue. You can create subscribers to consume the messages on the queue and perform actions based on them. For example, you could create a subscriber to verify that when a user adds an item to a portal page, its display name is less than 80 characters. The subscriber may process events as they occur or process them based on some time interval.

There are three major types of CMEF events: ADD, UPDATE, and DELETE. Every content management action falls under one of these events. An event can be in one of several different states. For example, when a user adds an item to a page, the state of the ADD event indicates whether the item is available immediately (PUBLISHED) or at some later date (NOT_PUBLISHED). Refer to Section 16.3, "Using the Content Management Event Framework" to learn more about how subscribers can use this information to retrieve messages of interest to them.

Using the events published by CMEF involves the following five basic queuing operations:

You use the DBMS_AQ package to perform queuing operations.

16.2.1 Enqueuing Messages

CMEF enqueues messages to the WWSBR_EVENT_Q queue, specifying AQ message properties that subscriber applications can then use at dequeue time (Table 16-1).

Table 16-1 AQ Message Properties Set by CMEF

Message Property Type Value

PRIORITY

BINARY_INTEGER

1

DELAY

BINARY_INTEGER

NO_DELAY

EXPIRATION

BINARY_INTEGER

NEVER

CORRELATION

VARCHAR2(128)

NULL

RECIPIENT_LIST

AQ$_RECIPIENT_LIST_T

NULL

EXCEPTION_QUEUE

VARCHAR2(51)

WWSBR_EVENT_ERR_Q

ORIGINAL_MSGID

RAW(16)

NULL


The WWSBR_EVENT_Q queue is a multiconsumer queue. This enables more than one subscriber to consume a single message, allowing for multiple subscribers without having to make multiple copies of each message. Since all messages in the queue have the same priority, this queue works on a first-in first-out basis.

Each message that CMEF enqueues to the WWSBR_EVENT_Q queue contains a payload that contains information about the portal object to which the event relates, such as page ID or page group ID. Subscribers can use this payload information to perform actions on the portal object. The contents of the CMEF message payload are described in more detail in Section 16.3.6, "CMEF Message Payload".

CMEF enqueues messages for immediate consumption, that is, a message is posted on the WWSBR_EVENT_Q queue as soon as an action occurs in the portal; there is no delay. Messages created by CMEF do not have an expiration time.

16.2.2 Subscribers and Dequeuing Messages

On the consuming end, subscribers have various mechanisms to consume the messages produced by CMEF. Subscribers can process messages as they arrive, and thus must wait for the messages to arrive. Alternatively, subscribers can choose to be notified when the messages arrive. These notifications can be Oracle Call Interface (OCI) callback functions, PL/SQL functions, or even e-mails.

16.2.2.1 Adding a Subscriber to the WWSBR_EVENT_Q Queue

For a subscriber to be able to consume messages produced by CMEF, you need to add it to the WWSBR_EVENT_Q queue using the DBMS_AQADM.ADD_SUBSCRIBER procedure. Example 16-1 adds the JAY subscriber to the WWSBR_EVENT_Q queue.

Example 16-1 Adding a Subscriber to WWSBR_EVENT_Q

subscriber := sys.aq$_agent('JAY', null, null);
dbms_aqadm.add_subscriber(
  queue_name => 'portal.wwsbr_event_q',
  subscriber => subscriber
);

Since Oracle Streams AQ supports a maximum of 1024 subscribers for each multiconsumer queue, you can add up to a maximum of 1024 subscribers to the WWSBR_EVENT_Q queue. All consumers that are added as subscribers to this queue must have unique values for the AQ$_AGENT parameter.

You can remove a subscriber using the DBMS_AQADM.REMOVE_SUBSCRIBER procedure.

For an example of a simple subscriber, refer to Section 16.3.1, "Creating Subscriber Code".

16.2.2.2 Subscriber Queue Management

Oracle Enterprise Manager DBA Studio enables you to manage Oracle Streams AQ. You can use DBA Studio to create queue tables, create queues, browse AQ messages, archive or purge AQ messages, add AQ subscribers, and manage propagation. DBA Studio also shows the topology for the propagation of messages between queues at database level and queue level.

The Oracle Diagnostics and Tuning pack supports alerts and monitoring for AQ queues. You can set up alerts for when the number of messages for a particular subscriber exceeds a threshold, or when there is an error in propagation. In addition, you can monitor queues for the number of messages in a ready state or the number of messages for each subscriber, and so on.

You can also manage the subscriber queue using the standard AQ APIs. For more information, refer to your Oracle Enterprise Manager documentation.

16.2.2.3 Dequeuing Messages

The operation of retrieving messages from a queue is known as dequeuing (Figure 16-1).

Figure 16-1 The Dequeuing Process

Flow chart illustrating the dequeue process.
Description of "Figure 16-1 The Dequeuing Process"

You use the DBMS_AQ.DEQUEUE procedure for dequeuing messages from the WWSBR_EVENT_Q queue. Example 16-2 illustrates dequeuing for the subscriber JAY.

Example 16-2 Dequeuing Messages

...
dequeue_options.wait          := dbms_aq.NO_WAIT;
dequeue_options.consumer_name := 'JAY';
dequeue_options.navigation    := dbms_aq.FIRST_MESSAGE;
dequeue_options.dequeue_mode  := dbms_aq.BROWSE;
dbms_aq.dequeue(
  queue_name         => 'WWSBR_EVENT_Q',
  dequeue_options    => dequeue_options,
  message_properties => message_properties,
  payload            => message,
  msgid              => message_handle
);
...

  • NAVIGATION: Use the NAVIGATION parameter of the DBMS_AQ.DEQUEUE operation to determine the sequence in which you want to dequeue the messages. The default NAVIGATION parameter for the dequeue request is NEXT_MESSAGE. This means that the subsequent dequeue operation will retrieve the messages from the queue based on the snapshot obtained in the first dequeue. In particular, a message that is enqueued after the dequeue command will be processed only after processing all the messages already enqueued before in the queue. This is sufficient for messages enqueued for the WWSBR_EVENT_Q queue since it does not have priority-based ordering.


    Note:

    NEXT_MESSAGE with some delay is the optimal way of processing AQ messages. When the first message in the queue needs to be processed by every dequeue command, subscribers must explicitly use the FIRST_MESSAGE navigation option.

  • DEQUEUE_MODE: A dequeue request can either view a message or delete a message. To view a message, the subscriber can use the BROWSE or LOCK modes. To consume a message, the subscriber can use the REMOVE or REMOVE_NODATA modes. If a subscriber browses a message, the message remains available for further processing. Similarly a locked message remains available for further processing after the subscriber releases the lock by performing a transaction commit or rollback. To prevent a viewed message from being dequeued by a concurrent user, you should view the message in locked mode. After a subscriber consumes a message using either of the REMOVE modes, the message is no longer available for dequeue requests.

    When a subscriber dequeues a message using REMOVE_NODATA mode, the request does not retrieve the payload of the message. This mode is useful when the user has already examined the message payload, possibly by means of a previous BROWSE dequeue. In this way, you can avoid the overhead of payload retrieval that can be substantial for large payloads.


    Note:

    One event is enqueued for each subscriber. Thus removing an event from one subscriber's queue does not remove it from the queues of other subscribers.

  • CONSUMER_NAME: A subscriber can dequeue a message from the WWSBR_EVENT_Q queue by supplying this queue name:

    • In PL/SQL you supply the consumer name using the CONSUMER_NAME field in the DEQUEUE_OPTIONS_T record.

    • In OCI you supply the consumer name using the OCISetAttr procedure to specify a text string as the OCI_ATTR_CONSUMER_NAME of an OCI_DTYPE_AQDEQ_OPTIONS descriptor.

    • In Visual Basic (OO4O) you supply the consumer name by setting the consumer property of the OraAQ object.

    Multiple processes or operating system threads can use the same to dequeue concurrently from a queue. Unless the message ID of a specific message is specified during dequeue, the consumers can dequeue messages that are in the READY state.


Note:

You should not need to use the Search parameters to dequeue CMEF events.

16.2.3 Exception Handling

A message is considered processed when all intended consumers have successfully dequeued the message. If a message cannot be processed for some reason, it moves to an exception queue.

A message is considered expired if one or more consumers does not dequeue it before the expiration time. Expired messages also move to an exception queue. An exception queue is a repository for all expired or unserviceable messages.

Applications cannot directly enqueue into exception queues. Also, an exception queue cannot have subscribers associated with it. However, an application that intends to handle these expired or unserviceable messages must dequeue from the exception queue.

CMEF exceptions are sent to the WWSBR_EVENT_ERR_Q exception queue. Expired messages from the WWSBR_EVENT_Q multiconsumer queue cannot be dequeued by the intended recipients of the message. However, they can be dequeued in REMOVE mode once by specifying a NULL consumer name in the dequeue options. The queue monitor removes expired messages from multiconsumer queues. This allows dequeuers to complete the dequeue operation by not locking the message in the queue table.

Since the queue monitor removes messages that have been processed by all consumers from multiconsumer queues at regular intervals, users may see a delay between when the messages have been completely processed and when they are physically removed from the queue.


Note:

The WWSBR_EVENT_ERR_Q exception queue, like all exception queues, is a single-consumer queue.

16.2.4 Listening for Messages

Oracle Streams AQ can monitor multiple queues for messages with a single LISTEN call. A subscriber can use LISTEN to wait for messages for multiple subscriptions. It can also be used by gateway applications to monitor multiple queues. If the LISTEN call returns successfully, a dequeue must be used to retrieve the message. Without the LISTEN call, an application which sought to dequeue from a set of queues would have to continuously poll the WWSBR_EVENT_Q queue to determine if there is a message.

Alternatively, you could design your subscriber to have a separate dequeue process for each queue. However, if there are long periods with no traffic in any of the queues, including WWSBR_EVENT_Q, these approaches will create unacceptable overhead. The LISTEN call is well suited for such subscribers. When there are messages for multiple agents in the agent list, LISTEN returns with the first agent for whom there is a message.

You can use the LISTEN call to monitor receipt of messages on one or more queues on behalf of a list of agents. The call takes a list of agents as an argument. You specify the queue to be monitored in the address field of each agent listed. You also must specify the name of the agent when monitoring multiconsumer queues.

Example 16-3 Listening to Messages on Multiple Queues

declare
  agent_w_msg   aq$agent;
  qlist         dbms_aq.agent_list_t;
begin
  -- MYQ1, MYQ2, MYQ3 are multiconsumer queues in the SCOTT schema.
  qlist(1) := aq$agent('agent1', 'scott.MYQ1', null);
  qlist(2) := aq$agent(null, 'scott.MYQ2', null);
  qlist(3) := aq$agent('agent3', 'scott.MYQ3', null);
  -- Listen with a timeout of 100 seconds.
  dbms_aq.listen(
    agent_list => qlist,
    wait       => 100,
    agent      => agent_w_msg
  );
  dbms_output.put_line('MSG in Q: '||agent_w_msg.address||'for '
    ||agent_w_msg.name);
  dbms_output.put_line('');
end;
/

This is a blocking call that returns when there is a message ready for consumption for an agent in the list. If there are messages for more than one agent, only the first agent listed is returned. If there are no messages found when the wait time expires, an error is raised.

A successful return from the call is only an indication that there is a message for one of the listed agents in one of the specified queues. The interested agent should dequeue the relevant message. Example 16-4 illustrates the dequeue process combined with listening. Here, we dequeue the messages for the subscriber, JAY, for a certain time period.

Example 16-4 Listening and Dequeuing Messages

begin
  agent_list(1)     := sys.aq$_agent('JAY', 'WWSBR_EVENT_Q', null);
  wait_time integer := 100;
  loop
    -- Wait for order status messages.
    dbms_aq.listen(
      agent_list => agent_list,
      wait       => wait_time,
      agent      => agent_w_message
    );
    -- If there are messages for JAY, dequeue them.
    if (agent_w_message.name = 'JAY') then
      dequeue_options.wait          := dbms_aq.NO_WAIT;
      dequeue_options.consumer_name := 'JAY';
      dequeue_options.navigation    := dbms_aq.FIRST_MESSAGE;
      dequeue_options.dequeue_mode  := dbms_aq.BROWSE;
      dbms_aq.dequeue(
        queue_name         => 'item_queue',
        dequeue_options    => dequeue_options,
        message_properties => message_properties,
        payload            => message,
        msgid              => message_handle
      );
    end if;
  end loop;
exception
  when NO_MESSAGES then
    dbms_output.put_line('No more messages for Jay');
end;
/

16.3 Using the Content Management Event Framework

Every portal item and page action generates a CMEF message that is enqueued to the WWSBR_EVENT_Q queue. A subscriber can use the information contained within this message to perform various actions using the OracleAS Portal PL/SQL APIs.

There are three basic steps in handling CMEF events, each of which is described and illustrated later in this section:

  1. Creating Subscriber Code

  2. Adding a Subscriber to WWSBR_EVENT_Q

  3. Enabling CMEF Events at the Page Group Level

This section also provides a description of the message payload, followed by several examples of common portal actions and the events they generate.

16.3.1 Creating Subscriber Code

Oracle Streams AQ offers a content-based subscription model. Subscriber applications can specify interest based on message content. You should execute CMEF event subscriber code in the OracleAS Portal schema.

Example 16-5 An Example Subscriber

create or replace <procedure name> as
  agent_list         dbms_aq.aq$_agent_list_t;
  wait_time          integer       := <time in seconds>;
  agent_w_message    sys.aq$_agent;
  dequeue_options    dbms_aq.dequeue_options_t;
  message_properties dbms_aq.message_properties_t;
  message_handle     raw(16);
  message            <portal schema>.wwsbr_event;
  l_subscriber       varchar2(30)  := '<subscriber name>';
  l_queue            varchar2(30)  := 'PORTAL.WWSBR_EVENT_Q';
  l_mode             binary_integer := dbms_aq.[BROWSE|LOCK|REMOVE|REMOVE_NODATA];
  ...
  <additional parameters>
  ...
BEGIN
  agent_list(1) := sys.aq$_agent(l_subscriber, l_queue, null);
  loop
    -- Listen for messages.
    dbms_aq.listen(
      agent_list => agent_list,
      wait       => wait_time,
      agent      => agent_w_message
    );
    -- If there are messages for the subscriber then dequeue them.
    if (agent_w_message.name = l_subscriber) then
      dequeue_options.wait          := dbms_aq.NO_WAIT;
      dequeue_options.consumer_name := l_subscriber;
      dequeue_options.navigation    := dbms_aq.[NEXT_MESSAGE|FIRST_MESSAGE];
      dequeue_options.dequeue_mode  := l_mode;
      -- Dequeue messages.
      dbms_aq.dequeue(
        queue_name         => l_queue,
        dequeue_options    => dequeue_options,
        message_properties => message_properties,
        payload            => message,
        msgid              => message_handle
      );
      -- Determine the type of event that occurred and act accordingly.
      ...
      <your code here>
      ...
    end if;
  end loop;
  -- Process cache invalidation messages.
  wwpro_api_invalidation.execute_cache_invalidation;
end;
/

16.3.2 Adding a Subscriber to WWSBR_EVENT_Q

A subscriber subscribes to a queue from where it consumes messages. You have to add a subscriber to the WWSBR_EVENT_Q queue in order to process CMEF event messages.

Example 16-6 Adding a Subscriber to WWSBR_EVENT_Q

declare
  subscriber sys.aq$_agent;
begin
  subscriber := sys.aq$_agent('<subscriber>', null, null);
  dbms_aqadm.add_subscriber(
    queue_name => '<portal schema>.wwsbr_event_q',
    subscriber => subscriber
  );
end;
/

16.3.3 Enabling CMEF Events at the Page Group Level

In OracleAS Portal, CMEF is enabled or disabled at the page group level. By default, CMEF is enabled when a user creates a page group, and thus, events are triggered whenever changes occur within the OracleAS Portal user interface or WebDAV.

To enable or disable CMEF for a page group:

  1. Go to any page of the page group and switch to Edit mode.

  2. In the toolbar at the top of the page, click the Properties link next to Page Group.


    Note:

    Make sure you click the link next to Page Group and not the one next to Page (Figure 16-2).

    Figure 16-2 The Page Group Properties Link on the Edit Mode Toolbar

    The Page Group Properties link.
    Description of "Figure 16-2 The Page Group Properties Link on the Edit Mode Toolbar"

  3. Click the Configure tab to bring it forward.

  4. In the Content Management Event Framework section you can see whether CMEF is enabled or disabled. If you want to change this setting, click the Edit link.

    Figure 16-3 Status of CMEF for a Page Group

    The CMEF status of a page group.
    Description of "Figure 16-3 Status of CMEF for a Page Group"

  5. To enable CMEF, select the Enable Content Management Event Framework check box. To disable CMEF, clear the check box.

    Figure 16-4 Enabling or Disabling CMEF for a Page Group

    The Enable Content Management Event Framework page.
    Description of "Figure 16-4 Enabling or Disabling CMEF for a Page Group"

  6. Click OK to save your changes.

  7. Click Close to return to the page.

16.3.4 Examining CMEF Events

Use the CMEF Events page (Figure 16-5) to examine the subscribers that have been added to the WWSBR_EVENTS_Q queue.

To access the CMEF Events page:

  1. Login to your portal as the portal schema owner.

  2. Enter the following URL in the browser Address field:

    http://<host>:<port>/portal/pls/<dad>/<schema>.wwsbr_event_dbg.show
    
    
    • host is the machine on which your portal is installed

    • port is the port used by your portal

    • dad is the Database Access Descriptor (DAD) for your OracleAS Portal installation.

    • schema is the schema in which OracleAS Portal is installed.


Note:

The CMEF Events page is not supported by Oracle, but it is included within OracleAS Portal for debugging purposes. It is only accessible by the portal schema owner.

Figure 16-5 CMEF Events Page

The CMEF Events page.
Description of "Figure 16-5 CMEF Events Page"

16.3.5 Running a CMEF Subscriber

To run a subscriber, issue the command shown in Example 16-7 at a SQL prompt.

Example 16-7 Running a Subscriber

begin
  <subscriber procedure>();
end;
/

16.3.6 CMEF Message Payload

Every OracleAS Portal user interface or PL/SQL content management action falls under one of the three main CMEF events: INSERT, UPDATE, DELETE. States provide more meaning to events. For example, there are several types of ADD/INSERT ITEM events:

  • Add an item and publish it immediately.

  • Add an item and publish it at a later date.

  • Add an item that requires approval, because the user has Manage Items With Approval privileges.

For a detailed list of the actions and related events and states, refer to Appendix F, "Content Management Event Framework Events".

Each CMEF event has an associated CMEF message payload as shown in Table 16-2.

Table 16-2 CMEF Message Payload Properties

Message Property Type Description

ACTION

VARCHAR2(30)

The portal action that triggered the event.

RAW_EVENT

VARCHAR2(30)

The event produced as a result of the portal action.

STATE

VARCHAR2(30)

Provides additional information related to the event.

OBJECT_ID

NUMBER

The ID of the object to which the event relates (for example, item, page, category, and so on).

OBJECT_SITE_ID

NUMBER

The ID of the page group to which the object belongs.

OBJECT_LANGUAGE

VARCHAR2(30)

The session language.

NULL if the action is independent of language.

PAGE_ID

NUMBER

For items, the ID of the page on which the item appears.

NULL for other objects.

PAGE_SITE_ID

NUMBER

For items, the ID of the page group to which the page identified in PAGE_ID belongs.

NULL for non-item related events.

OBJECT_CLASS

VARCHAR2(30)

Records the class of object about which an event has been raised.

EVENTS_USER

VARCHAR2(256)

The name of the user who performed the portal action.

EVENTS_DATE

VARCHAR2(60)

The date on which the event occurred. Format: dd-mon-yyyy HH12:MI PM

ID1 (OVERLOADED)

NUMBER

For items, the item type ID.

For pages, the page type ID.

For item and page types, the base type ID.

SITE_ID1 (OVERLOADED)

NUMBER

For item types, the ID of the page group to which the item type belongs.

For pages, the ID of the page group to which the page type belongs.

For item and page types, the ID of the page group to which the base type belongs.

GROUP_ID

NUMBER

When multiple messages are associated with a particular event, related messages have the same group ID.

OBJECT PATH

VARCHAR2(4000)

A unique path to the portal object being referenced by this message on the queue (this can be NULL). It is used only for pages, items, categories, perspectives, item types, and page types.

OBJECT UID

VARCHAR2(4000)

A unique immutable identifier to the portal object being referenced by this message. It is used only for pages, items, categories, perspectives, item types, and page types.


16.3.7 OracleAS Portal Actions and CMEF Events

This section describes some of the most common portal actions and shows how to include code in your subscriber to detect these actions. These actions may occur through the OracleAS Portal user interface, the OracleAS Portal PL/SQL APIs, or WebDAV.

For a more detailed list of portal actions and the events and message payloads that they generate, refer to Appendix F, "Content Management Event Framework Events".

16.3.7.1 Page and Page Group Actions

In OracleAS Portal, a portal is a collection of one or more page groups. A page group is a hierarchical collection of pages for which common attributes and mechanisms can be established.

16.3.7.1.1 Creating a Page

Creating a page produces the following CMEF message payload:

Action Event State Object Class
ADD_PAGE INSERT PUBLISHED PAGE
ADD_ITEM INSERT PUBLISHED ITEM

The first message is for the page itself, and the second is for the portlet instance that displays the default navigation page on the page.

If you want your subscriber to respond to the creation of a page, perform the following check:

if ((message.object_class = 'PAGE') and
    (message.raw_event    = wwsbr_event_q_access.EVENT_INSERT) then
. . .
end if;

16.3.7.1.2 Updating the Access Control List of a Page

Changing the ACL of a page so that it does not inherit from that of its page group, then clicking Apply or OK produces the following message payload:

Action Event State Object Class
SPECIFY_PAGE_ACL UPDATE GENERAL PAGE

Now, adding a user to the ACL of a page, then clicking Add produces the following message payload:

Action Event State Object Class
ADD_PAGE_ACL UPDATE GENERAL PAGE

Changing the ACL of a page so that it does not inherit from that of its page group, immediately adding a user or group to the ACL, and then clicking Apply or OK produces the following message payload:

Action Event State Object Class
SPECIFY_AND_ADD_PAGE_ACL UPDATE GENERAL PAGE

Changing the ACL of a page so that it inherits that of the page group then clicking Apply or OK produces the following message payload:

Action Event State Object Class
INHERIT_PAGE_ACL UPDATE GENERAL PAGE

Clicking Apply or OK on the Page Properties page, produces an additional message:

Action Event State Object Class
UPDATE_PAGE_ACL UPDATE GENERAL PAGE

16.3.7.1.3 Updating the Access Control List of a Page Group

Updating the access control list (ACL) of a page group by adding a user or group, or deleting a user or group produces the following message payload:

Action Event State Object Class
ADD_PAGEGROUP_ACL

or

DELETE_PAGE_GROUP_ACL

UPDATE GENERAL PAGE_GROUP
UPDATE_PAGE_GROUP_ACL UPDATE GENERAL PAGE_GROUP

If you want your subscriber to respond to general ACL updates on a page group, perform the following check:

if ((message.action       = 'UPDATE_PAGE_GROUP_ACL') and
    (message.object_class = 'PAGE_GROUP') and
    (message.raw.event    = wwsbr_event_q_access.EVENT_UPDATE)) then
. . .
end if;

However, if you are more interested in filtering for actual update and delete events on the page group's ACL, then your subscriber should perform the following checks:

if ((message.object_class = 'PAGE_GROUP') and
    (message.raw_event    = wwsbr_event_q_access.EVENT_UPDATE)) then
  if (message.action = 'ADD_PAGE_GROUP_ACL') then
    . . .
  end if;
  if (message.action = 'DELETE_PAGE_GROUP_ACL') then
    . . .
  end if;
  . . .
end if;


Note:

The ADD_PAGE_GROUP_ACL and DELETE_PAGE_GROUP_ACL events are triggered when the user clicks Add for each user that is added to/deleted from the ACL. The UPDATE_PAGE_GROUP_ACL is generated when the user clicks Apply or OK.

16.3.7.1.4 Deleting a Page

Deleting a page produces the following message payload:

Action Event State Object Class
DELETE_PAGE DELETE PURGED PAGE


Note:

Events are not generated for the sub-pages or items that are on the page.

16.3.7.2 Item Actions

Items are one of the basic components of a portal page. Items in a portal are based on item types. An action on an item triggers a CMEF event irrespective of the item type.

For example, an ADD_ITEM action occurs whenever a user adds an item of a base, extended, or custom item type. This ensures that there is consistent CMEF messaging behavior when an item action occurs.

16.3.7.2.1 Creating an Item and Publishing it at the Same Time

If a user has Manage privileges, creating an item of any type on a page produces the following message payload:

Action Event State Object Class
ADD_ITEM INSERT PUBLISHED ITEM

If you want you subscriber to respond to this action, perform the following check:

if ((message.action       = 'ADD_ITEM') and
    (message.object_class = 'ITEM') and
    (message.raw_event    = 'INSERT') and
    (message.state        = 'PUBLISHED')) then
   . . .
end if;

16.3.7.2.2 Adding an Item That Requires Approval

If approvals and notifications are enabled for a page (or page group), and a user with Manage Items With Approval privileges, adding an item to a page produces the following message payload:

Action Event State Object Class
SUBMIT_ITEM_FOR_APPROVAL INSERT NOT_PUBLISHED ITEM

If you want your subscriber to respond to this action, perform the following check:

if ((message.action       = 'SUBMIT_ITEM_FOR_APPROVAL') and
    (message.object_class = 'ITEM') and
    (message.raw_event    = 'INSERT') and
    (message.state        = 'NOT_PUBLISHED')) then
   . . .
end if;

16.3.7.2.3 Approving an Item

Approving an item triggers either an INSERT or UPDATE event, which may also be followed by either a DELETE or UPDATE event depending upon whether or not versioning is enabled.

Item approved; versioning disabled

This is the simplest case. If the item still has approval steps to pass through, then an UPDATE event is triggered:

Action Event State Object Class
APPROVE_ITEM_STEP UPDATE GENERAL ITEM

If the item has completed all approval steps, then an INSERT event is triggered:

Action Event State Object Class
APPROVE_ITEM INSERT PUBLISHED ITEM

Item approved; versioning enabled; current version overwritten

If item versioning is set to Simple at the page or page group level, and a user selects Overwrite Current Version when editing an item, on approval of the item, two events are generated. The first event is for the item that is approved, and a second DELETE event for the item that is overwritten as a result of the item being approved:

Action Event State Object Class
APPROVE_ITEM INSERT PUBLISHED ITEM
APPROVE_ITEM DELETE PURGED ITEM

Item approved; versioning enabled; new and current version

If item versioning is set to Audit at the page or page group level, or it is set to Simple and a user selects Add Item As New And Current Version, on approval of the item an INSERT event is generated. An UPDATE event is also generated that is related to marking the previous version of the item as UNPUBLISHED:

Action Event State Object Class
APPROVE_ITEM INSERT PUBLISHED ITEM
APPROVE_ITEM UPDATE UNPUBLISHED ITEM

If you are writing a subscriber that sends a notification when an item is approved or has passed through a stage of being approved, you should perform the following check:

-- If an item is approved.
if ((message.action       = 'APPROVE_ITEM') and
    (message.object.class = 'ITEM') and
    (message.raw_event    = 'INSERT') and
    (message.state        = 'PUBLISHED')) then
   . . .
-- If an item has passed an approval step.
elsif ((message.action       = 'APPROVE_ITEM_STEP') and
       (message.object_class = 'ITEM') and
       (message.raw_event    = 'UPDATE') and
       (message.state        = 'PUBLISHED')) then
   . . .
end if;

Item approved, versioning enabled; new but not current version

An INSERT event occurs for the item that is added, but the state of the item is marked as NOT_PUBLISHED to indicate that it is not published as the current version:

Action Event State Object Class
APPROVE_ITEM INSERT NOT_PUBLISHED ITEM

16.3.7.2.4 Applying a Category or Perspective to an Item

Applying a different category or perspective to an item produces the same message payload as editing an item:

Action Event State Object Class
EDIT_ITEM UPDATE GENERAL ITEM


Note:

No specific event is generated when the category or perspectives applied to an item are changed, and no additional information is provided.

If you want your subscriber to respond to this action, perform the following check:

if ((message.action       = 'EDIT_ITEM') and
    (message.object_class = 'ITEM') and
    (message.raw_event    = 'UPDATE') and
    (message.state        = 'GENERAL') then
   . . .
end if;

16.3.7.2.5 Deleting an Item

Deleting an item from a page group that retains deleted items (that is, items are marked for deletion, but not actually deleted) produces the following message payload:

Action Event State Object Class
DELETE_ITEM DELETE MARKED_FOR_DELETE ITEM

Deleting an item from a page group that does not retain deleted items (that is, deleted items are immediately and permanently removed) produces the following message payload:

Action Event State Object Class
DELETE_ITEM DELETE PURGED ITEM

Your subscriber can use the state value within the message payload to determine what type of delete action occurred:

if ((message.action       = 'DELETE_ITEM') and
    (message.object_class = 'ITEM') and
    (message.raw_event    = 'DELETE')) then
  . . .
  -- If item is in a page group that does not actually delete items.
  if (message.state = 'MARKED_FOR_DELETE') then
      . . .
  -- If item is in a page group that actually deletes items.
  elsif (message.state = 'PURGED') then
      . . .
  end if;
end if;

16.4 Installing the Examples

If you would like to deploy and use the examples within the next few sections, we recommend that you create them in a separate schema, called CMEFSAMPLES. To create this schema, use the following steps:

  1. Create the database schema CMEFSAMPLES. You need to do this as the SYS user, since you need to grant permissions packages to which only SYS has access. This database schema must be in the same database as that in which the OracleAS Portal repository resides. For example:

    @connect "/ as sysdba"
    drop user cmefsamples cascade;
    create user cmefsamples identified by oracle1
    default tablespace users temporary tablespace temp;
    grant connect, resource to cmefsamples;
    
    
  2. As the SYS schema, grant the following privileges to CMEFSAMPLES:

    @connect "/ as sysdba"
    grant create table                  to cmefsamples;
    grant create sequence               to cmefsamples;
    grant create view                   to cmefsamples;
    grant create procedure              to cmefsamples;
    grant create trigger                to cmefsamples;
    grant create indextype              to cmefsamples;
    
    grant create synonym                to cmefsamples;
    grant create public synonym         to cmefsamples;
    
    grant create database link          to cmefsamples;
    grant create public database link   to cmefsamples;
    grant execute on dbms_utility       to cmefsamples;
    grant aq_administrator_role         to cmefsamples;
    grant aq_user_role                  to cmefsamples;
    grant execute  on dbms_aqadm        to cmefsamples;
    grant execute  on dbms_aq           to cmefsamples;
    grant execute  on aq$_agent         to cmefsamples;
    grant execute  on dbms_job          to cmefsamples;
    
    execute dbms_aqadm.grant_type_access('cmefsamples');
    execute dbms_aqadm.grant_system_privilege('ENQUEUE_ANY','cmefsamples',FALSE);
    execute dbms_aqadm.grant_system_privilege('DEQUEUE_ANY','cmefsamples',FALSE);
    execute dbms_aqadm.grant_system_privilege('MANAGE_ANY', 'cmefsamples', FALSE);
    EXECUTE DBMS_AQADM.GRANT_QUEUE_PRIVILEGE('ENQUEUE','portal.WWSBR_EVENT_Q','cmefsamples', FALSE);
    EXECUTE DBMS_AQADM.GRANT_QUEUE_PRIVILEGE('DEQUEUE','portal.WWSBR_EVENT_Q','cmefsamples', FALSE);
    
    
  3. Grant the CMEFSAMPLES schema the permission to call the OracleAS Portal PL/SQL APIs. For information about how to do this, refer to Section 9.3, "Providing Access to the APIs and Secure Views".

  4. Log into the portal schema and grant permissions on the following:

    EXECUTE DBMS_AQADM.GRANT_QUEUE_PRIVILEGE('DEQUEUE','WWSBR_EVENT_Q','cmefsamples', FALSE);
    grant execute on wwsbr_event_q_access to cmefsamples;
    grant execute on wwpob_page_util to cmefsamples;
    grant select on wwsbr_all_folders to cmefsamples;
    grant execute on wwsbr_thing_types to cmefsamples;
    grant execute on wwv_thingdb to cmefsamples;
    grant execute on wwsbr_event to cmefsamples;
    
    
  5. Log into the CMEFSAMPLES schema and run the following:

    sqlplus cmefsamples/<password>
    create synonym aq$_agent for sys.aq$_agent;
    
    
More on OTN

You can download the code for the following examples from OTN:

http://www.oracle.com/technology/products/ias/portal/files/cm_overview_10g1014_cmef_samples.zip

16.5 Example: Portal Object Event Logging

The LOG_PORTAL_EVENT subscriber in Example 16-9 listens to CMEF events and then writes them to a log database table called CMEF_LOG_TABLE.

Example 16-8 The CMEF_LOG_TABLE

create table cmef_log_table(
  action          varchar2(30),
  event           varchar2(30),
  state           varchar2(30),
  object_type     varchar2(30),
  object_id       number,
  object_site_id  number,
  object_language varchar2(30),
  page_id         number,
  page_site_id    number,
  performed_by    varchar2(30)
)

Example 16-9 The LOG_PORTAL_EVENT Subscriber

create or replace procedure log_portal_event as
  agent_list         dbms_aq.aq$_agent_list_t;
  wait_time          integer        := 5;
  agent_w_message    sys.aq$_agent;
  dequeue_options    dbms_aq.dequeue_options_t;
  message_properties dbms_aq.message_properties_t;
  message_handle     raw(16);
  message            portal.wwsbr_event;
  l_subscriber       varchar2(30)   := 'CMEF_LOG_PORTAL_EVENT';
  l_queue            varchar2(30)   := 'PORTAL.WWSBR_EVENT_Q';
  l_mode             binary_integer := dbms_aq.REMOVE;
begin
  agent_list(1) := sys.aq$_agent(l_subscriber, l_queue, null);
  loop
    -- Wait for messages.
    dbms_aq.listen(
      agent_list => agent_list,
      wait       => wait_time,
      agent      => agent_w_message
    );
    if (agent_w_message.name = l_subscriber) then
      dequeue_options.wait          := dbms_aq.NO_WAIT;
      dequeue_options.consumer_name := l_subscriber;
      dequeue_options.navigation    := dbms_aq.FIRST_MESSAGE;
      dequeue_options.dequeue_mode  := l_mode;
      dbms_aq.dequeue(
        queue_name         => l_queue,
        dequeue_options    => dequeue_options,
        message_properties => message_properties,
        payload            => message,
        msgid              => message_handle
      );
      insert into cmef_log_table values(
        message.action,
        message.raw_event,
        message.state,
        message.object_class,
        message.object_id,
        message.object_site_id,
        message.object_language,
        message.page_id,
        message.page_site_id,
        message.events_user
      );
      commit;
    end if;
  end loop;
end;
/

The LOG_PORTAL_EVENT subscriber continuously listens for events on the WWSBR_EVENT_Q queue until the wait time of 5 seconds (as specified in the wait_time variable) is reached. It then dequeues CMEF events in REMOVE mode and then inserts the message payload values into the log table.

You could use the code in Example 16-9 to build an HTML page that displays the results of the log table. For example, the table in Figure 16-6 shows the CMEF message payload for an edit item action:

Figure 16-6 HTML Table Displaying CMEF Message Payload Values

Example output for the portal event logging subscriber.
Description of "Figure 16-6 HTML Table Displaying CMEF Message Payload Values"

The properties of the CMEF message payload (for example, message.raw_event) are described in Section 16.3.6, "CMEF Message Payload".

If you want the LOG_PORTAL_EVENT subscriber to continually remove messages off the WWSBR_EVENT_Q queue, then you need to remove the following from Example 16-9:

dbms_aq.listen(
  agent_list => agent_list,
  wait       => wait_time,
  agent      => agent_w_message
);
if (agent_w_message.name = l_subscriber) then
  dequeue_options.wait          := dbms_aq.NO_WAIT;
  dequeue_options.consumer_name := l_subscriber;
  dequeue_options.navigation    := dbms_aq.FIRST_MESSAGE;
  dequeue_options.dequeue_mode  := l_mode;
END IF;

Example 16-10 shows how to add the LOG_PORTAL_EVENT subscriber created in the previous section to the WWSBR_EVENT_Q queue.

Example 16-10 Adding the LOG_PORTAL_EVENT Subscriber to WWSBR_EVENT_Q

declare
  subscriber sys.aq$_agent;
begin
  subscriber := sys.aq$_agent('CMEF_LOG_PORTAL_EVENT', null, null);
  dbms_aqadm.add_subscriber(
    queue_name => 'portal.wwsbr_event_q',
    subscriber => subscriber
  );
end;
/

To run the LOG_PORTAL_EVENT subscriber, issue the command shown in Example 16-11.

Example 16-11 Running the LOG_PORTAL_EVENT CMEF Subscriber

begin
  log_portal_event();
end;
/

16.6 Example: Item Notification

The item_notify subscriber in Example 16-12 sends an e-mail notification whenever a user adds, updates, or deletes an item on a specified page:

Example 16-12 The CMEF_ITEM_NOTIFY Subscriber

create or replace procedure item_notify as
  MIME_TYPE_TEXT      constant varchar(30)  := 'text/plain';
  CUSTOM_ATTRIBUTE_ID constant number := 1020;
  ADDED               constant varchar2(20) := 'added';
  UPDATED             constant varchar2(20) := 'updated';
  DELETED             constant varchar2(20) := 'deleted';
  ITEM                constant varchar2(10) := 'ITEM';
  agent_list          dbms_aq.aq$_agent_list_t;
  wait_time           integer               := 5;
  begin_time          pls_integer;
  end_time            pls_integer;
  agent_w_message     sys.aq$_agent;
  dequeue_options     dbms_aq.dequeue_options_t;
  message_properties  dbms_aq.message_properties_t;
  message_handle      raw(16);
  message             portal.wwsbr_event;
  l_subscriber        varchar2(30)          := 'CMEF_ITEM_NOTIFY';
  l_queue             varchar2(30)          := 'PORTAL.WWSBR_EVENT_Q';
  l_page_id           number                := 33; -- Page ID of page.
  l_mode              binary_integer        := dbms_aq.REMOVE;
  l_rec               varchar2(256);
  l_body              varchar2(4000)        := null;
  l_from_user         varchar2(30)          := '<from-email-address>';
  l_to_user           varchar2(30)          := '<to-email-address>';
  l_event             varchar2(30)          := '';
  l_portal_user_name  varchar2(30)          := 'portal';
  l_portal_password   varchar2(30)          := '<portal password>';
  l_display_name      varchar2(256)         := '';

begin
  begin_time :=  dbms_utility.get_time;
  agent_list(1) := sys.aq$_agent(l_subscriber, l_queue, null);
  loop
    -- Wait for messages.
    dbms_aq.listen(
      agent_list => agent_list,
      wait       => wait_time,
      agent      => agent_w_message
    );
    if (agent_w_message.name = l_subscriber) then
      dequeue_options.wait          := DBMS_AQ.NO_WAIT;
      dequeue_options.consumer_name := l_subscriber;
      dequeue_options.navigation    := dbms_aq.FIRST_MESSAGE;
      dequeue_options.dequeue_mode  := l_mode;
      dbms_aq.dequeue(
        queue_name         => l_queue,
        dequeue_options    => dequeue_options,
        message_properties => message_properties,
        payload            => message,
        msgid              => message_handle
      );
      if ((message.object_class = ITEM)) then
        -- Determine the type of event that occurred.
        if (message.raw_event = portal.wwsbr_event_q_access.EVENT_INSERT) then
          l_event  := ADDED;
        elsif (message.raw_event = portal.wwsbr_event_q_access.EVENT_UPDATE) then
          l_event  := UPDATED;
        elsif (message.raw_event = portal.wwsbr_event_q_access.EVENT_DELETE) then
          l_event  := DELETED;
        end if;
        if ((l_event = ADDED) or
            (l_event = UPDATED) or
            (l_event = DELETED)) then
          -- Set the Portal Context.
          portal.wwctx_api.set_context(l_portal_user_name, l_portal_password);

          begin
            -- Get the Item Display name from the all items view.
            select display_name
            into l_display_name
            from portal.wwsbr_all_items
            where id=message.object_id 
            and caid=message.object_site_id;
          exception
            when NO_DATA_FOUND then
              dbms_output.put_line(sqlerrm);
              exit;
          end;

          -- Only send an e-mail if the item has a display name.
          if (l_display_name is not null) then
            -- Construct the body of the message.
            l_body := l_body || '<br>An item titled '
            || CSBR_UTIL.file_viewer_hyperlink(
            message.object_id, message.page_site_id, l_display_name)
            || ' was '
            || l_event || ' on '
            || to_char(message.events_date,'dd-mon-yyyy hh12:mi pm')
            || ' by ' || message.events_user || '. ';
            -- Send the message.
            html_email(
              p_from => l_from_user,
              p_to => l_to_user,
              p_subject => 'Item ' || l_event || ' Notification',
              p_text => 'Text',
              p_html => l_body
            );
          end if;
        end if;
      end if;
    end if;
    end_time :=  dbms_utility.get_time;
    if (end_time - begin_time) > 3000 then
      exit;
    end if;
  end loop;
end;
/

The ITEM_NOTIFY CMEF subscriber removes the actual events from the queue so that multiple e-mails are not sent to the user.


Note:

For descriptions of the file_viewer_hyperlink function, which gets the URL of the item, and the html_email procedure, which handles the actual sending of the message, refer to Section 16.6.1, "Additional Code".

More on OTN

For further information on the performance and timing of AQ events, refer to the Streams Advanced Queuing page on OTN:

http://www.oracle.com/technology/products/aq

For an example of how to add a subscriber to the WWSBR_EVENT_Q queue, refer to Example 16-10.

For an example of how to run a subscriber, refer to Example 16-11.

The ITEM_NOTIFY CMEF subscriber runs for 3 seconds. This ensures that the subscriber does not continuously run and consume system resources. Alternatively, you could modify the wait_time value used in the subscriber to a higher value, say 60 seconds, so that the subscriber listens for new events every minute. You could also use a DBMS_JOB to have the subscriber run at a specified interval, for example, 30 minutes:

Example 16-13 Using DMBS_JOB

declare
  v_job number;
begin
  dbms_job.submit(
    job      => v_job,
    what     => 'item_notify()',
    interval => 'SYSDATE + (30/(24*60))'
  );
end;
/

16.6.1 Additional Code

Example 16-14 shows the CSBR_UTIL package.

Example 16-14 CSBR_UTIL Package

create or replace package CSBR_UTIL as
  function file_viewer_hyperlink(
    p_item_id in number,
    p_caid    in number,
    p_text    in varchar2) return varchar2;
end;

create or replace package body CSBR_UTIL as

  function file_viewer_url(
    p_item_id in number,
    p_caid    in number) return varchar2 is

    l_return      varchar2(10000);
    l_item_name   varchar2(100);
    l_folder_id   number;
    l_folder_name varchar2(100);
    l_portal_url  varchar2(1000);
  begin
    select name, folder_id
    into l_return, l_folder_id
    from portal.wwsbr_all_items
    where id   = p_item_id
    and caid   = p_caid
    and active = 1;
    begin
      while 1=1 loop
        select parent_id, name
        into l_folder_id, l_folder_name
        from portal.wwsbr_all_folders
        where id = l_folder_id
        and caid = p_caid;
        l_return := l_folder_name||'/'||l_return;
      end loop;
    exception
      when NO_DATA_FOUND then
        null; -- Exit loop at no rows found, this is expected.
    END;
    -- Set the Portal URL.
    l_portal_url := 'http://<host>:<port>/portal/page/<dad>/';
    return l_portal_url||l_return;
    exception
      when OTHERS then
        dbms_output.put_line(sqlerrm);
        return 'Error';
  end file_viewer_url;

  function file_viewer_hyperlink(
    p_item_id in number,
    p_caid    in number,
    p_text    in varchar2 return varchar2 is
  begin
    return ('<a href="'||file_viewer_url(
      p_item_id => p_item_id,
      p_caid    => p_caid)||'">'||p_text||'</a>');
  end file_viewer_hyperlink;

end;
/

Example 16-15 shows the html_email procedure.


Note:

For this procedure to work you will need to deploy the Oracle Database Sendmail package on your database.

Example 16-15 The HTML E-mail Procedure

create or replace procedure html_email(
  p_to      in varchar2,
  p_from    in varchar2 default '',
  p_subject in varchar2,
  p_text    in varchar2 default null,
  p_html    in varchar2 default null
)
is
  l_boundary   varchar2(255) default 'a1b2c3d4e3f2g1';
  l_connection utl_smtp.connection;
  l_body_html  clob := empty_clob; -- This LOB will be the e-mail message.
  l_offset     number;
  l_ammount    number;
  l_temp       varchar2(32767) default null;
begin
  l_connection := utl_smtp.open_connection('<Mail Server here>');
  utl_smtp.helo( l_connection, '<Mail Server here>' );
  utl_smtp.mail( l_connection, p_from );
  utl_smtp.rcpt( l_connection, p_to );
  l_temp := l_temp || 'MIME-Version: 1.0' || chr(13) || chr(10);
  l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10);
  l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10);
  l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10);
  l_temp := l_temp || 'Reply-To: ' || p_from || chr(13) || chr(10);
  l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' || chr(34) || l_boundary || chr(34) || chr(13) || chr(10);
  ------------------------------------------------------------------------------
  -- Write the headers.
  dbms_lob.createtemporary( l_body_html, false, 10 );
  dbms_lob.write(l_body_html, length(l_temp), 1,l_temp);

  -------------------------------------------------------------------------------
  -- Write the text boundary.
  l_offset := dbms_lob.getlength(l_body_html) + 1;
  l_temp   := '--' || l_boundary || chr(13) || chr(10);
  l_temp   := l_temp || 'content-type: text/plain; charset=us-ascii' || chr(13) || chr(10) || chr(13) || chr(10);
  dbms_lob.write(l_body_html, length(l_temp), l_offset, l_temp);

  ------------------------------------------------------------------------------
  -- Write the plain text portion of the e-mail.
  l_offset := dbms_lob.getlength(l_body_html) + 1;
  dbms_lob.write(l_body_html, length(p_text), l_offset, p_text);

  ------------------------------------------------------------------------------
  -- Write the HTML boundary.
  l_temp := chr(13) || chr(10) || chr(13) || chr(10) || '--' || l_boundary || chr(13) || chr(10);
  l_temp := l_temp || 'content-type: text/html;' || chr(13) || chr(10) || chr(13) || chr(10);
  l_offset := dbms_lob.getlength(l_body_html) + 1;
  dbms_lob.write(l_body_html, length(l_temp), l_offset, l_temp);

  ------------------------------------------------------------------------------
  -- Write the HTML portion of the message.
  l_offset := dbms_lob.getlength(l_body_html) + 1
  dbms_lob.write(l_body_html, length(p_html), l_offset, p_html);

  ------------------------------------------------------------------------------
  -- Write the final HTML boundary.
  l_temp := chr(13) || chr(10) || '--' || l_boundary || '--' || chr(13);
  l_offset := dbms_lob.getlength(l_body_html) + 1;
  dbms_lob.write(l_body_html, length(l_temp), l_offset, l_temp);

  ------------------------------------------------------------------------------
  -- Send the e-mail in 1900 byte chunks to UTL_SMTP.
  l_offset := 1
  l_ammount := 1900;
  utl_smtp.open_data(l_connection);
  while l_offset < dbms_lob.getlength(l_body_html) loop
    utl_smtp.write_data(l_connection, dbms_lob.substr(l_body_html,l_ammount,l_offset));
    l_offset := l_offset + l_ammount;
    l_ammount := least(1900, dbms_lob.getlength(l_body_html) - l_ammount);
  end loop;
  utl_smtp.close_data(l_connection);
  utl_smtp.quit( l_connection );
  dbms_lob.freetemporary(l_body_html);
end;
/

16.7 Example: Item Validation

The item_valication CMEF subscriber in Example 16-16 validates that a URL item added to a specified portal page includes a URL. If the item includes a URL, then the item is approved using the wwsbr_api.approve API. Otherwise the item is rejected using the wwsbr_api.reject API.

Example 16-16 The ITEM_VALIDATION CMEF Subscriber

create or replace procedure item_validation as
  MIME_TYPE_TEXT      constant varchar2(30) := 'text/plain';
  agent_list          dbms_aq.aq$_agent_list_t;
  wait_time           integer               := 5;
  begin_time          pls_integer;
  end_time            pls_integer;
  agent_w_message     sys.aq$_agent;
  dequeue_options     dbms_aq.dequeue_options_t;
  message_properties  dbms_aq.message_properties_t;
  message_handle      raw(16);
  message             portal.wwsbr_event;
  l_subscriber        varchar2(30)          := 'CMEF_ITEM_VALIDATION';
  l_queue             varchar2(30)          := 'PORTAL.WWSBR_EVENT_Q';
  l_mode              binary_integer        := dbms_aq.REMOVE;
  l_attribute_site_id number                := 0;
  l_event             varchar2(30);
  l_doc               wwdoc_api.document_record;
  l_desc              varchar2(4000);
  l_portal_user_name  varchar2(30)          := 'portal';
  l_portal_password   varchar2(30)          := ' <portal password>';
  l_itemtype          varchar2(30)          := '';
  l_url               varchar2(4000)        := '';
begin
  begin_time :=  dbms_utility.get_time;
  agent_list(1) := sys.aq$_agent(l_subscriber, l_queue, null);
  loop
    -- Wait for messages.
    dbms_aq.listen(
      agent_list => agent_list,
      wait       => wait_time,
      agent      => agent_w_message
    );
    if (agent_w_message.name = l_subscriber) then
      dequeue_options.wait := DBMS_AQ.NO_WAIT;
      dequeue_options.consumer_name := l_subscriber;
      dequeue_options.navigation := dbms_aq.FIRST_MESSAGE;
      dequeue_options.dequeue_mode := l_mode;
      dbms_aq.dequeue(
        queue_name         => l_queue,
        dequeue_options    => dequeue_options,
        message_properties => message_properties,
        payload            => message,
        msgid              => message_handle
      );
      -- If the event is an ITEM INSERT event and marked for later publication.
      if ((message.action       = 'SUBMIT_ITEM_FOR_APPROVAL') and
          (message.object_class = 'ITEM') and
          (message.raw_event    = 'INSERT') and
          (message.state        = 'NOT_PUBLISHED')) then

        -- Set the Portal Context.
        portal.wwctx_api.set_context(l_portal_user_name, l_portal_password);
        -- Get the Object Information.
        begin
          select itemtype, url
          into l_itemtype, l_url
          from portal.wwsbr_all_items
          where id=message.object_id 
            and caid=message.object_site_id;
        exception
          when NO_DATA_FOUND then
            dbms_output.put_line(sqlerrm);
            exit;
        end;
        -- If the item type is a URL and no URL is specified.
        if (l_itemtype = portal.wwsbr_thing_types.BASE_ITEM_TYPE_URL) and
           (l_url is null) then
          begin
            -- Update item to indicate that a URL needs to be specified.
            l_desc := '<font color=RED>NO URL SPECIFIED</font>';
            -- Reset the CMEF global variables.
            wwsbr_api.clear_cmef_context;
            wwsbr_api.set_attribute(
              p_site_id           => message.object_site_id,
              p_thing_id          => message.object_id,
              p_attribute_site_id => l_attribute_site_id,
              p_attribute_id      => wwsbr_api.ATTRIBUTE_DESCRIPTION,
              p_attribute_value   => l_desc
              p_log_cmef_message  => FALSE
            );
            -- Reject the item.
            wwsbr_api.reject(
              p_item_id => message.object_id,
              p_site_id => message.object_site_id,
              p_comment => 'Rejected'
            );
            -- Reset the CMEF global variables.
            wwsbr_api.clear_cmef_context;
          exception
            when OTHERS then
              dbms_output.put_line(sqlerrm);
          end;
        else
          -- Approve the item.
          begin
            wwsbr_api.approve(
              p_item_id => message.object_id,
              p_site_id => message.object_site_id,
              p_comment => 'Approved'
            );
            -- Reset the CMEF global variables.
            wwsbr_api.clear_cmef_context;
          exception
            when OTHERS then
              dbms_output.put_line(sqlerrm);
          end;
        end if;
        commit;
      end if;
    end if;
    end_time :=  dbms_utility.get_time;
    if (end_time - begin_time) > 3000 then
      exit;
    end if;
  end loop;
  -- Process cache invalidation messages.
  wwpro_api_invalidation.execute_cache_invalidation;
end;
/

The following check in the item_validation CMEF subscriber determines whether the item requires approval:

-- If the event is an ITEM INSERT event and marked for later publication.
if ((message.action       = 'SUBMIT_ITEM_FOR_APPROVAL') and
    (message.object_class = 'ITEM') and
    (message.raw_event    = 'INSERT') and
    (message.state        = 'NOT_PUBLISHED')) then
   . . .
end if;

The item_validation CMEF subscriber uses the wwsbr_api.set_attribute API to set the item description to indicate that the user did not specify a URL. The subscriber also chooses not to log CMEF messages when calling this API (p_log_cmef_messages = FALSE).

-- Update item to indicate that a URL needs to be specified.
l_desc := '<font color=RED>No FILE NAME SPECIFIED</font>';
wwsbr_api.set_attribute(
  p_site_id           => l_thing.siteid,
  p_thing_id          => l_thing.id,
  p_attribute_site_id => l_attribute_site_id,
  p_attribute_id      => wwsbr_api.ATTRIBUTE_DESCRIPTION,
  p_attribute_value   => l_desc
  p_log_cmef_message  => FALSE
);

Approving or rejecting an item with the approve or reject APIs triggers an event just the same as if the item were approved or rejected through the OracleAS Portal UI. For example, calling the wwsbr_api.approve API triggers an INSERT event with an action of APPROVE_ITEM.

wwsbr_api.approve(
  p_item_id => l_thing.id,
  p_site_id => l_thing.siteid,
  p_comment => 'Approved'
);

For an example of how to add a subscriber to the WWSBR_EVENT_Q queue, refer to Example 16-10.

For an example of how to run a subscriber, refer to Example 16-11.

16.8 Example: Integrating External Workflow

Organizations often find that they want to integrate their business processes into their enterprise portal. Using the capabilities of Oracle Workflow, you can implement portals to route information of any type according to the compliance rules defined for your organization. With CMEF, you can integrate both traditional, applications-based workflow and e-business integration workflow with your OracleAS Portal content management system.

This section provides an example of how to integrate external workflow with the OracleAS Portal content management system.

16.8.1 Integrating Workflow with OracleAS Portal

Oracle Workflow is a component of Oracle Application Server and Oracle E-Business Suite that enables you to design internal business processes and store them in a central repository. You can use Oracle Workflow to support a wide variety of compliance mandates, designing processes that are both auditable and repeatable, and enforce pre-set approvals and limits. Oracle's newest compliance solution, Oracle Internal Controls Manager (Oracle ICM) works in conjunction with Oracle Workflow to monitor internal business processes and ensure they are performed as designed.

In general, there are five steps involved in integrating Oracle Workflow with the OracleAS Portal content management system for content approval:

  1. Enable approvals and notifications in OracleAS Portal.

  2. Grant users the Manage Items With Approval privileges.

  3. Create a portal user that will be used by the Oracle Workflow process to either approve or reject items and add that user to the portal approval process.

  4. Register the workflow process with Oracle Workflow. This workflow process calls your compliance process, or it may perform your actual compliance process.

  5. Create subscriber code and add the subscriber to WWSBR_EVENT_Q to process CMEF events. This subscriber initializes the workflow engine and then calls the workflow process.

We can see how this works by modifying the item validation example described in Section 16.7, "Example: Item Validation" to call a workflow process to perform the item validation.

Figure 16-7 Process Flow for Workflow Integration with OracleAS Portal

Process flow for workflow integration with OracleAS Portal.
Description of "Figure 16-7 Process Flow for Workflow Integration with OracleAS Portal"

Let's take a look at how this process would work, based on the diagram in Figure 16-7.

16.8.2 Example Overview

Figure 16-7 shows the process flow diagram for using CMEF to integrate Oracle Workflow with the OracleAS Portal content management system. When a user with Manage Items With Approval privileges adds an item to the portal, that item is marked as pending. Just as with the ITEM_VALIDATION subscriber in Example 16-16, an Oracle Streams Advanced Queuing subscriber is required to listen for events on the WWSBR_EVENT_Q queue (see Example 16-17). If the event is of type SUBMIT_ITEM_FOR_APPROVAL, the subscriber launches the workflow engine process, WF_CHECKURL, and passes the parameters for the portal item to the workflow process. The workflow process calls an external PL/SQL procedure (see Example 16-18) to perform its business logic, which in this case includes either approving or rejecting the portal item.

If the user specifies a URL, then Oracle Workflow approves the item using the wwsbr_api.approve API. Otherwise, Oracle Workflow rejects the item using the wwsbr_api.reject API.

This example assumes the following prerequisites:

  • The CMEFSAMPLES schema exists in the same database as the portal schema.

  • The Oracle OWF_MGR workflow schema is installed in the same database as the portal schema.

  • The AQ_TM_PROCESSES parameter should be set to at least five. To check this, log in to the database as the SYS user and execute the following query:

    select value
    from   v$parameter
    where  name = 'aq_tm_processes';
    
    

    If the value is less than five, then set the value as follows:

    alter system set aq_tm_processes=5;
    
    
  • OracleAS Portal 10.1.4 has been installed.

16.8.3 Detailed Example Description

When a portal user with Manage Items With Approval privileges adds an item to a page, a SUBMIT_ITEM_FOR_APPROVAL event is added to the WWSBR_EVENT_Q queue. The item is marked as pending on the portal page. This user's items must be approved before they are made visible on the page.

The Streams Advanced Queuing subscriber CMEF_WORKFLOW listens for events on WWSBR_EVENT_Q. If the event is of type SUBMIT_ITEM_FOR_APPROVAL, then it launches the workflow engine process and passes the parameters for the portal item to the workflow process.

The workflow process WF_CHECKURL performs its business logic, which in this case includes either approving or rejecting portal items.WF_ CHECKURL uses the views and APIs to approve or reject the item. If it approves the item, then an APPROVE_ITEM event is added to WWSBR_EVENT_Q. If it rejects the item, then a REJECT_ITEM event is added to WWSBR_EVENT_Q.

When the user refreshes the page, he or she will either see the item published on the page (if it was approved), or removed (if it was rejected).

The following six steps occur within this example:

  1. Enable Approvals and Notifications in OracleAS Portal

  2. Grant Users the Manage Items With Approval Privileges

  3. Run Scripts Required for the CMEF Workflow Integration Example

  4. Create Subscriber and Check Procedures

  5. Register the WF_CHECKURL Process with Oracle Workflow

  6. Add the CMEF_WORKFLOW Subscriber to the WWSBR_EVENT_Q Queue

Each of these steps is described in more detail in the following sections.

16.8.3.1 Enable Approvals and Notifications in OracleAS Portal

The first thing you need to do is enable approvals and notifications in the page group.

To enable approvals and notifications:

  1. Go to any page of the page group and switch to Edit mode.

  2. In the toolbar at the top of the page, click the Properties link next to Page Group.


    Note:

    Make sure you click the link next to Page Group and not the one next to Page (Figure 16-2).

  3. Click the Configure tab to bring it forward.

  4. In the Approvals and Notifications section you can see whether approvals and notifications are enabled or disabled. If you want to change this setting, click the Edit link.

    Figure 16-8 Status of Approvals and Notifications for a Page Group

    The approvals and notifications status of a page group.
    Description of "Figure 16-8 Status of Approvals and Notifications for a Page Group"

  5. To enable approvals and notifications, select the Enable Approvals and Notifications check box.

    Figure 16-9 Enabling or Disabling Approvals and Notifications for a Page Group

    This image shows the Enable Approvals and Notifications page
    Description of "Figure 16-9 Enabling or Disabling Approvals and Notifications for a Page Group"

  6. Click OK to save your changes.

  7. Click Close to return to the page.

16.8.3.2 Grant Users the Manage Items With Approval Privileges


Note:

You can skip this step if your external workflow process does not require the approval or rejection of portal items.

The next step is to grant Manage Items With Approval privileges to all users who need approval for their items. For the purposes of this example, let's specify that all users require approval for their items.

To specify that all users require approval for their items:

  1. Go to any page in the page group and switch to Edit mode.

  2. In the toolbar at the top of the page, click the Properties link next to Page Group.


    Note:

    Make sure you click the link next to Page Group and not the one next to Page (Figure 16-2).

  3. Click the Approval tab to bring it forward.

  4. Select the Require Approval for All Users check box.

    Figure 16-10 Specifying That All Users Require Approval for Their Items

    All users require approval.
    Description of "Figure 16-10 Specifying That All Users Require Approval for Their Items"

  5. Click OK.

16.8.3.3 Run Scripts Required for the CMEF Workflow Integration Example

Before you proceed to the next step, there are several tasks that you need to perform. These are described in the following section.

  1. Log in to the SYS schema and run the following command:

    GRANT EXECUTE ON owf_mgr.wf_engine to cmefsamples;
    
    
  2. Log in to the portal schema and run provsyns.sql. When prompted for the schema/provider name enter owf_mgr.

  3. Log in to the CMEFSAMPLES schema and run the following:

    drop sequence WF_SEQ
    
    create sequence WF_SEQ
    increment by 1
    start with 1
    maxvalue 1000000000
    minvalue 1
    cache 20
    
    grant select on wf_seq to owf_mgr;
    
    

    This creates the sequence required by workflow, and allows the OWF_MGR workflow schema SELECT privileges on the sequence.

  4. Create the synonym WF_ENGINE to the workflow schema:

    create synonym wf_engine for owf_mgr.wf_engine;
    
    

16.8.3.4 Create Subscriber and Check Procedures

You need to create a subscriber that listens to the WWSBR_EVENT_Q queue, waiting until a user adds an item that requires approval. The subscriber calls the WF_CHECKURL workflow process to perform the item validation and to send the e-mail notification.

Example 16-17 The WORKFLOW_APPROVAL Subscriber

create or replace procedure workflow_approval is
  MIME_TYPE_TEXT      constant varchar2(30) := 'text/plain';
  agent_list          dbms_aq.aq$_agent_list_t;
  wait_time           integer               := 30;
  begin_time          pls_integer;
  end_time            pls_integer;
  agent_w_message     aq$_agent;
  dequeue_options     dbms_aq.dequeue_options_t;
  message_properties  dbms_aq.message_properties_t;
  message_handle      raw(16);
  message             portal.wwsbr_event;
  l_subscriber        varchar2(30)          := 'CMEF_WORKFLOW';
  l_queue             varchar2(30)          := 'PORTAL.WWSBR_EVENT_Q';
  l_mode              binary_integer        := dbms_aq.REMOVE;
  l_attribute_site_id number                := 0;
  l_event             varchar2(30);
  l_doc               wwdoc_api.document_record;
  l_desc              varchar2(4000);
  l_itemkey           varchar2(100);
  l_job_nr            number;
  l_itemtype          varchar2(100)         := 'WF';
  l_wf_process        varchar2(100)         := 'WF_CHECKURL';
  l_is_indirect       boolean               := TRUE;
  l_portal_user_name  varchar2(30)          := 'portal';
  l_portal_password   varchar2(30)          := '<portal password>';
  l_url               varchar2(4000);       := '';

begin
  begin_time := dbms_utility.get_time;
  agent_list(1) := aq$_agent(l_subscriber, l_queue, null);
  loop
    -- Wait for messages.
    dbms_aq.listen(
      agent_list => agent_list,
      wait       => wait_time,
      agent      => agent_w_message
    );
    if (agent_w_message.name = l_subscriber) then
      dequeue_options.wait          := dbms_aq.NO_WAIT;
      dequeue_options.consumer_name := l_subscriber;
      dequeue_options.navigation    := dbms_aq.FIRST_MESSAGE;
      dequeue_options.dequeue_mode  := l_mode;
      dbms_aq.dequeue(
        queue_name         => l_queue,
        dequeue_options    => dequeue_options,
        message_properties => message_properties,
        payload            => message,
        msgid              => message_handle
      );
      -- If the event is an ITEM INSERT event and marked for later publication.
        if ((message.action       = 'SUBMIT_ITEM_FOR_APPROVAL') and
            (message.object_class = 'ITEM') and
            (message.raw_event    = 'INSERT') and
            (message.state        = 'NOT_PUBLISHED') then
          portal.wwctx_api.set_context(l_portal_user_name, l_portal_password);
          -- Get the URL property for the object.
          select url
          into l_url
          from portal.wwsbr_all_items
          where id = message.object_id
          and caid = message.object_site_id;
          -- Get the nextval of the workflow sequence for the itemkey.
          select wf_seq.nextval
          into l_job_nr
          from dual;
          l_itemkey := lpad(to_char(l_job_nr), 5, '0');
          -- Launch the workflow engine process and pass the paramters for 
          -- the portal item to the workflow process.
          wf_engine.createprocess(l_itemtype, l_itemkey, l_wf_process);
          -- Set the value for the portal URL.
          wf_engine.setitemattrtext(l_itemtype, l_itemkey, 'PORTAL_URL', l__url);
          -- Set the value for the portal item ID.
          wf_engine.setitemattrnumber(l_itemtype, l_itemkey, 'PORTAL_ITEM_ID', message.object_id);
          -- Set the value for the portal page group ID.
          wf_engine.setitemattrnumber(l_itemtype, l_itemkey, 'PORTAL_SITE_ID', message.object_site_id);
          -- Start the workflow process.
          wf_engine.startprocess(l_itemtype, l_itemkey);
        end if;
    end if;
    end_time := dbms_utility.get_time;
    if (end_time - begin_time) > 3000 then
      exit;
    end if;
  end loop;
exception
  when OTHERS then
    dbms_output.put_line(sqlerrm);
end workflow_approval;
/

You also need to create a procedure to check whether the user specified a file name for the item to determine whether or not it can be approved. The workflow process delegates the actual validation and approval of the item to this CHECK_URL procedure.

Example 16-18 The CHECK_URL Procedure

create or replace procedure check_url(
  itemtype  in  varchar2,
  itemkey   in  varchar2,
  actid     in  number,
  funcmode  in  varchar2,
  resultout out varchar2) is

  MIME_TYPE_TEXT      constant varchar2(30) := 'text/plain';
  agent_list          dbms_aq.aq$_agent_list_t;
  wait_time           integer               := 30;
  begin_time          pls_integer;
  end_time            pls_integer;
  agent_w_message     aq$_agent;
  dequeue_options     dbms_aq.dequeue_options_t;
  message_properties  dbms_aq.message_properties_t;
  message_handle      RAW(16);
  message             portal.wwsbr_event;
  l_subscriber        varchar2(30)      := 'CMEF_WORKFLOW';
  l_queue             varchar2(30)      := 'PORTAL.WWSBR_EVENT_Q';
  l_mode              BINARY_INTEGER    := dbms_aq.REMOVE;
  l_attribute_site_id number            := 0;
  l_event             varchar2(30);
  l_doc               wwdoc_api.document_record;
  l_desc              varchar2(4000);
  l_itemtype          varchar2(100)    := 'WF';
  l_itemkey           varchar2(100);
  l_url               varchar2(100);
  l_siteid            number;
  l_itemid            number;
  l_ignore            boolean          := FALSE;
  l_is_indirect       boolean          := TRUE;
  l_portal_user_name  varchar2(30)     := 'portal';
  l_portal_password   varchar2(30)     := '<portal password>');
begin
  -- Get the values for the attributes stored in the procedure.
  l_url := wf_engine.getitemattrtext(itemtype,itemkey,'PORTAL_URL',l_ignore);
  l_itemid := wf_engine.getitemattrnumber(itemtype,itemkey,'PORTAL_ITEM_ID',l_ignore);
  l_siteid := wf_engine.getitemattrnumber(itemtype,itemkey,'PORTAL_SITE_ID',l_ignore);
  -- Set the portal context.
  portal.wwctx_api.set_context(l_portal_user_name, l_portal_password);
  -- If the item type is a URL item and no URL is specified.
  if l_url is null then
    begin
      -- Update the item to indicate that the URL needs to be specified.
      l_desc := '<font color=RED>NO URL SPECIFIED</font>';
      -- Reset the CMEF global variables.
      wwsbr_api.clear_cmef_context;
      wwsbr_api.set_attribute(
        p_site_id           => l_siteid,
        p_thing_id          => l_itemid,
        p_attribute_site_id => l_attribute_site_id,
        p_attribute_id      => wwsbr_api.attribute_description,
        p_attribute_value   => l_desc
        p_log_cmef_message  => FALSE
      );
      -- Reject the item.
      wwsbr_api.reject(
        p_item_id     => l_itemid,
        p_site_id     => l_siteid,
        p_is_indirect => l_is_indirect,
        p_comment     => 'Rejected'
      );
      -- Reset the CMEF global variables.
      wwsbr_api.clear_cmef_context;
    end;
  else
    -- Approve the item.
    begin
      wwsbr_api.approve(
        p_item_id => l_itemid,
        p_site_id => l_siteid,
        p_comment => 'Approved'
      );
      -- Reset the CMEF global variables.
      wwsbr_api.clear_cmef_context;
    end;
  end if;
  commit;
  -- Process cache invalidation messages.
  wwpro_api_invalidation.execute_cache_invalidation;
exception
  when OTHERS then
    dbms_output.put_line(sqlerrm);
end check_url;
/

Note:

CMEFSAMPLES will need to grant execute on WORKFLOW_APPROVAL and CHECK_URL to OWF_MGR:
grant execute on workflow_approval to owf_mgr;
grant execute on check_filename to owf_mgr;

16.8.3.5 Register the WF_CHECKURL Process with Oracle Workflow

The WF_CHECKURL process actually approves or rejects the portal item and sends the e-mail notification. You need to register this process with Oracle Workflow using the Oracle Workflow Builder.

Figure 16-11 The CHECK_FILE Oracle Workflow Process

CHECK_FILE Oracle Workflow process in Workflow Builder.
Description of "Figure 16-11 The CHECK_FILE Oracle Workflow Process"

Use the following steps to install the WF_CHECKURL process into the OWF_MGR schema:

  1. Open the Oracle Workflow Builder.


    Tip:

    After installing the Workflow Builder, you may have to run it from the command line because you will have to set the ORACLE_HOME environment variable to that of the Workflow Builder. For example:
    set ORACLE_HOME=c:\oraclewf
    

  2. Start a new workflow project.

  3. Save the workflow project as wf_checkurl.wft.

  4. Close the Oracle Workflow Builder.

  5. Open wf_checkurl.wft in a text editor, for example, Notepad.

  6. Copy and paste the WF_CHECKURL code into the file. You can download this code from Portal Center:

    http://www.oracle.com/technology/products/ias/portal/files/cm_overview_10g1014_cmef_samples.zip
    
    
  7. Save the file and close the text editor.

  8. Open wf_checkurl.wft in Oracle Workflow Builder.

  9. Save wf_checkurl.wft into your portal database using the OWF_MGR schema.


    Tip:

    If you do not know the OWF_MGR password, see MetaLink Note 198800.1.

16.8.3.6 Add the CMEF_WORKFLOW Subscriber to the WWSBR_EVENT_Q Queue

Next, you need to add the CMEF_WORKFLOW subscriber to the WWSBR_EVENT_Q queue so that it can process the CMEF events and trigger the WF_CHECKURL Oracle Workflow process:

Example 16-19 Adding the CMEF_WORKFLOW Subscriber to WWSBR_EVENT_Q

declare
  subscriber sys.aq$_agent;
begin
  subscriber := sys.aq$_agent('CMEF_WORKFLOW', null, null);
  dbms_aqadm.add_subscriber(
    queue_name => 'portal.wwsbr_event_q',
    subscriber => subscriber
  );
end;
/

Now, when a user with Manage Items With Approval privileges add an item to a page, it is marked as Pending and a single INSERT event occurs, with a state of NOT_PUBLISHED:

Action Event State Object Class
SUBMIT_ITEM_FOR_APPROVAL INSERT NOT_PUBLISHED ITEM

The CMEF_WORKFLOW_APPROVAL subscriber responds to this action and invokes the PORTAL_ITEM_WORKFLOW Oracle Workflow process.

The PORTAL_ITEM_APPROVAL Oracle Workflow process performs its business logic and calls the OracleAS Portal approve or reject API to approve or reject the item. It also sends an e-mail notification to the specified e-mail ID indicating the approval status.