# 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](http://download.oracle.com/docs/cd/B14099_15/portal.1014/b14135/pdg_cm_cmef.htm). 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;  
/