Oracle Portal Content Management Event Framework

I recently put together a little demo that might be helpful for people starting out with the Oracle Portal Content Management Event Framework (CMEF). The documentation is here. While the documentation is good, it can be a bit overwhelming. My example gives you everything you need to get started, and nothing you don't. So, let's get started.

Goal: Capture all events from the CMEF and store the content in a table. If you can store it in a table, you can do just about anything with it.

Overview:

1. Create a sequence to be our table's primary key
2. Create the table and associated triggers
3. Create a package and procedure that will be notified when an event occurs, dequeue the event and put the contents in the table.
4. Create a subscriber to the CMEF queue.
5. Register the subscriber and the procedure created in step 3.

Full steps
All scripts run in the portal schema

1. Create a sequence to be our table's primary key

CREATE SEQUENCE "C2_EVENT_LOG_SEQ"
MINVALUE 1
MAXVALUE 999999999999999999999999999
INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE
/

2. Create the table and associated triggers

CREATE TABLE "C2_EVENT_LOG"
( "ID" NUMBER NOT NULL ENABLE,
"EVENT" VARCHAR2(4000),
"EVENT_DATE" DATE,
"EVENT_PAYLOAD" CLOB,
CONSTRAINT "C2_EVENT_LOG_PK" PRIMARY KEY ("ID") ENABLE
)
/

CREATE OR REPLACE TRIGGER "BI_C2_EVENT_LOG"
before insert on "C2_EVENT_LOG"
for each row
begin
if :NEW."ID" is null then
select "C2_EVENT_LOG_SEQ".nextval into :NEW."ID" from dual;
end if;
end;

/
ALTER TRIGGER "BI_C2_EVENT_LOG" ENABLE
/

3. Create package and procedure

CREATE OR REPLACE PACKAGE c2_ptl
is

PROCEDURE event_notify(context raw,reginfo sys.aq$_reg_info,descr sys.aq$_descriptor,payload raw, payloadl number);

end;
/

CREATE OR REPLACE PACKAGE body c2_ptl
is

PROCEDURE event_notify(context raw,reginfo sys.aq$_reg_info,descr sys.aq$_descriptor,payload raw, payloadl number)
as
dequeue_options dbms_aq.dequeue_options_t;
message_properties dbms_aq.message_properties_t;
message_handle RAW(16);
message portal.wwsbr_event;
lclob clob;
pmsg varchar2(100);
BEGIN
dequeue_options.msgid := descr.msg_id;
dequeue_options.consumer_name := descr.consumer_name;

-- Get the Message and remove it from the queue
-- Note: this will dequeue the message. If you don't do anything with i
-- it will be gone and you won't be able to retrieve it again
DBMS_AQ.DEQUEUE(queue_name => descr.queue_name,
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => message,
msgid => message_handle);

-- Insert message contents into the table

insert into c2_event_log (event, event_date, event_payload)
values ('A',sysdate, 'message.action: '||message.action
|| ' message.object_class: '|| message.object_class);

-- there are a log of other message properties, look at the portal.wwsbr_event type
-- for a full list of the contents of the message

COMMIT;
END event_notify;

end;
/

4. Create a subscriber to the CMEF queue
begin
dbms_aqadm.add_subscriber
( queue_name => 'PORTAL.WWSBR_EVENT_Q',
subscriber => sys.aq$_agent( 'C2_SUB', null, null ) );
end;
/

5. Register the subscriber and the procedure created in step 3
begin
dbms_aq.register
( sys.aq$_reg_info_list(
sys.aq$_reg_info('PORTAL.WWSBR_EVENT_Q:C2_SUB',
DBMS_AQ.NAMESPACE_AQ,
'plsql://c2_ptl.event_notify',
HEXTORAW('FF')) ) ,
1 );
end;
/

Let me know if this works for you!

Just a little note... The following query will give you all queue registrations:
SELECT * FROM sys.reg$

The following URL will give you portal queue subscribers (you must be logged in as portal):
http://machine:port/portal/pls/portal/PORTAL.wwsbr_event_dbg.show

And finally . . . if you want to remove those subscriptions, do the following:
/* *** UNREGISTER SUBSCRIBER *** */
begin
dbms_aq.unregister
( sys.aq$_reg_info_list(
sys.aq$_reg_info('PORTAL.WWSBR_EVENT_Q:C2_SUB',
DBMS_AQ.NAMESPACE_AQ,
'plsql://c2_ptl.event_notify',
HEXTORAW('FF')) ) ,
1 );
end;
/

/* **** REMOVE SUBSCRIBER **** */
begin
dbms_aqadm.remove_subscriber
( queue_name => 'PORTAL.WWSBR_EVENT_Q',
subscriber => sys.aq$_agent( 'c2_SUB', null, null ) );
end;
/