HOWTO: XDB Repository Events – An Introduction

Oracle XMLDB Repository Events, IMHO, was one of the coolest functionalities introduced in Oracle 11.1. In principal they are a kind of event “triggers” that get fired during actions / methods on objects in the XDB Repository. One of the disadvantages of this functionality is that they are very “sparsely” documented in the Oracle XMLDB Developers Guide to say the least, so one of the consequences is, is that it isn’t broadly known and/or how to implement it. That probably sounds a bit strange, even more if you start searching and notice that the Oracle XMLDB Development Guide has a complete chapter about it: “Oracle XML DB Repository Events“. The problem with this chapter is that a vital part is missing, the link between the Event Handlers and Events… But lets start with the beginning, lets start with a simple example…

XDB Repository Events are events that get triggered by Event Listeners on files or folder for specific “actions/methods”. These triggered events can be handled via their counterparts, the so called Event Handlers. For example, when a text file (aka a resource) gets deleted (=event) from the XDB Repository, one or more Event Handlers will be fired. In such a case, for example, for this text file: “PreUnlinkIn”, “PreUnlinkFrom”, “PostUnlinkFrom”, “PostUnlinkIn”, “PreDelete ” and “PostDelete” events. One or more of these events can be used to programmatically do something, for example, insert audit information in a table that this file has been deleted by user X on timestamp Y. XDB Resource Events are always assigned on files or folders (=the resources).

In principal their are three parts that needs to be defined to make this work:

1. An Event Listener
2. An Event Handler
3. A Resource Configuration file

As described in the Oracle XMLDB Developers Guide:

A repository event listener is a Java class or a PL/SQL package or object type. It comprises a set of PL/SQL procedures or Java methods, each of which is called an event handler. Each event handler processes a single event…You associate a repository event listener with a resource by mapping a resource configuration file to the resource…

In the latest documentation there is now a very small hint that points to one of the missing pieces, that is the specific naming that has to be used for the PL/SQL procedures to match those of the XDB Events.

As described in the Oracle XMLDB Developers Guide:

For the same reason, the event-type names used here are derived from the Java interface XDBRepositoryEventListener by dropping the prefixes handlePre and handlePost…

Something that it is easy overlooked and becomes a complete mystery (aka misary/annoyance) if you want to match the events to the event handlers (PL/SQL Procedures). Package procedures must follow the naming of the event handlers as described here: Java XDK Doc for XDBRepositoryEventListener.

So if you now create a package that handles the events then you now know where the mysterious “handlePostLinkedIn” procedure names comes from…

--
create or replace PACKAGE EVENT_PACKAGE 
AS
    PROCEDURE handlePostLinkIn (eventObject DBMS_XEVENT.XDBRepositoryEvent);
END EVENT_PACKAGE;
/
--
create or replace PACKAGE BODY EVENT_PACKAGE 
AS
  PROCEDURE handlePostLinkIn (eventObject DBMS_XEVENT.XDBRepositoryEvent) AS
    BEGIN
        insert into event_logging 
        ( DTIME
        , EVENT
        )
        values
        ( SYSDATE
        , 'XML File created in Repository'
        );
    END;
END;
/
--

To associate such an event package that handles those XDB events, you must also create a resource configuration file. This “resource configuration file” MUST follow the associated XML Schema for those resource configuration files called the XDBResConfig.xsd XML Schema. This resource configuration file associates the database schema, the method (the PL/SQL package to be fired in this example), for which event or events it is applicable and possible pre-conditions.

In the following example, those pre-conditions determine that the event handler (PL/SQL) only gets fired for the defined event when it matches a MIME type for “text/xml” (XML, XSD, XSLT files). My blog post code handler has issues with combinations of XML Schemas and XML tags, so to show the content at least correct in the following example, all <> tags are replaced by { } (see for the full correct syntax, the attached SQL example to this post)

--
{ResConfig xmlns="http://xmlns.oracle.com/xdb/XDBResConfig.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://xmlns.oracle.com/xdb/XDBResConfig.xsd  http://xmlns.oracle.com/xdb/XDBResConfig.xsd"}
   {event-listeners}
     {listener}
       {description}Logging of created xml files in target directory{/description}
       {schema}EVENT{/schema}
       {source}EVENT_PACKAGE{/source}
       {language}PL/SQL{/language}
       {events}
         {Post-LinkIn/}
       {/events}
       {pre-condition}
         {existsNode}
           {XPath}/Resource[ContentType="text/xml"]{/XPath}
         {/existsNode}
       {/pre-condition}
     {/listener}
   {/event-listeners}
 {/ResConfig}
--

So now we have some PL/SQL code and a Resource Configuration file. The next step we need to do is to assign this resource configuration file to a file or a folder.

This can be done via using the DBMS_RESCONFIG PL/SQL package

--
 DBMS_RESCONFIG.APPENDRESCONFIG(
    respath      => '/home/EVENT',
    rcpath       => '/public/event.xml',
    appendoption => DBMS_RESCONFIG.APPEND_RESOURCE
  );
--

In the example above, ‘/home/EVENT’ is the folder the EVENT_PACKAGE package must fire when all criteria described in the Resource Configuration file match (event Post-LinkedIn/MIME Type/etc). The “/public/event.xml” is an XML file with the content described above for the Resource Configuration file. The DBMS_RESCONFIG.APPEND_RESOURCE option associates the Resource Configuration file to the /home/EVENT folder, so in other words, associates the EVENT_PACKAGE and events to this folder (resource).

The whole assignment looks like the following

----------------------------------------------------------
--
-- Associate event handlers with the directory
-- 
----------------------------------------------------------
-- 
DECLARE
  b        BOOLEAN;
  resfile  VARCHAR2(100)   := '/public/event.xml';
BEGIN
  -- 
  BEGIN  -- Remove any references to the Resource Config file
    --
    DBMS_RESCONFIG.DELETERESCONFIG(
      respath      =} '/home/EVENT',
      rcpath       =} resfile,
      deleteOption =} DBMS_RESCONFIG.DELETE_RECURSIVE
    );
    --
    EXCEPTION
        WHEN OTHERS THEN
            NULL;
  END;
  --
  -- Create the config file that references the event handlers and associates them with the Post-LinkIn event
  --
  if (dbms_xdb.existsResource(resfile)) 
  then
    dbms_xdb.deleteResource(resfile, dbms_xdb.DELETE_RECURSIVE_FORCE);
  end if;
  --
  --
  b := DBMS_XDB.createresource(
    resfile,
 '{ResConfig xmlns="http://xmlns.oracle.com/xdb/XDBResConfig.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://xmlns.oracle.com/xdb/XDBResConfig.xsd  http://xmlns.oracle.com/xdb/XDBResConfig.xsd"}
   {event-listeners}
     {listener}
       {description}Logging of created xml files in target directory{/description}
       {schema}EVENT{/schema}
       {source}EVENT_PACKAGE{/source}
       {language}PL/SQL{/language}
       {events}
         {Post-LinkIn/}
       {/events}
       {pre-condition}
         {existsNode}
           {XPath}/Resource[ContentType="text/xml"]{/XPath}
         {/existsNode}
       {/pre-condition}
     {/listener}
   {/event-listeners}
 {/ResConfig}');
 --
 DBMS_RESCONFIG.APPENDRESCONFIG(
    respath      =} '/home/EVENT',
    rcpath       =} resfile,
    appendoption =} DBMS_RESCONFIG.APPEND_RESOURCE
  );
  --
  COMMIT;
  --
END;
/
-- 
commit;
--

If something happens in /home/EVENTS, creation of files, folders, etc, the EVENT_PACKAGE will now fire for the event “Post-LinkedIn”, but only when the contentType is of MIME type “text/xml”.

This simple example described here, was once part of a UKOUG 2008 presentation. You can find the full example code here: ukoug08.sql. It demonstrates how to create a use, some file and folders in the XDB Repository, how to create a simple event package that will be triggered when a file gets inserted in the specific created folder (and is of “text/xml” mime type) and how to associate the resource file to the folder structure. Afterwards, when a file will be created or copied in the XDB folder, a new row will be inserted in the EVENT table, demonstrating a very simple audit mechanism for XDB Repository objects.

Next time a more detailed example about Event Handlers and resource configuration file parameters.