This discussion is archived
3 Replies Latest reply: Nov 29, 2011 1:55 PM by MarcoGralike RSS

Intro into XDB Repository Events

MarcoGralike Oracle ACE Director
Currently Being Moderated
To give you guys a hats up on XDB Repository Events, one of the coolest features IMHO of Oracle 11g and onwards, hereby a very simple but good code start of handling XDB Events via their "EventHandlers"

The simplified code is based on some package info I got from Mark with some possible future additions I noticed. This simplified code will be used in a small code demo with the following "strange" file copy usage which once came to my mind when I had to work in an openVMS environment, where I as a external consultant, was not allowed to do anything on the server and apparently the person who was allowed had no idea how to get around those typical openVMS change directory commands... So the following popped in my head. What if you could copy the needed files into the XDB Repository, pick them up automatically via XDB Repository events and move them on via an Oracle directory alias into a specific operating system directory. In short, miss use the database, to copy files on to the database server.

The following will give you an idea how to do this via XDB Repository event handling, but more importantly, its a good start for your own coding (and I am gladly passing on Mark's good incentive helping me along). It defines all Event Handlers as they are now known in 11.2. The following has the intention to only pickup CSV files in a XDB Repository folder called "BATCH" (processing). Therefore this specific "CSV" reference in the "Event Listener" PL/SQL package. Be aware: although this compiles in 11.2 XE (with some extra grants), that I did not properly tested it yet. Therefore "DEMO" code.

;-)
  • 1. Re: Intro into XDB Repository Events
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    --------------------------------------------------------
    --  DDL for Package HANDLE_CSV_FILES
    --------------------------------------------------------
    
      CREATE OR REPLACE PACKAGE "HANDLE_CSV_FILES" 
    AS
      -- ----------------------------------------------------------------------------
      -- Created    : Marco Gralike, AMIS Services BV
      -- Purpose    : Central package to demonstrate XDB Event handling.
      -- Version    : 1.0
      -- ----------------------------------------------------------------------------
      --
      BINARY_CONTENT_SCHEMA CONSTANT VARCHAR2(700) := 'http://xmlns.oracle.com/xdb/XDBSchema.xsd#binary';
      TEXT_CONTENT_SCHEMA   CONSTANT VARCHAR2(700) := 'http://xmlns.oracle.com/xdb/XDBSchema.xsd#text';
      --
      -- --------------------------------------------
      -- OS Utilities
      -- --------------------------------------------
    PROCEDURE CreateBlobFile(
        p_blob BLOB ,
        p_directory VARCHAR2 ,
        p_filename  VARCHAR2 );  
      --
      -- --------------------------------------------  
      -- Event Handler procedures    
      -- --------------------------------------------
      --
      -- Create
    PROCEDURE handlePreCreate(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
    PROCEDURE handlePostCreate(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
      --  Delete
    PROCEDURE handlePreDelete(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
    PROCEDURE handlePostDelete(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
      -- Update
    PROCEDURE handlePreUpdate(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
    PROCEDURE handlePostUpdate(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
      -- Lock
    PROCEDURE handlePreLock(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
    PROCEDURE handlePostLock(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
      -- Unlock
    PROCEDURE handlePreUnlock(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
    PROCEDURE handlePostUnlock(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
      -- LinkIn
    PROCEDURE handlePreLinkIn(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
    PROCEDURE handlePostLinkIn(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
      -- LinkTo
    PROCEDURE handlePreLinkTo(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
    PROCEDURE handlePostLinkTo(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
      -- UnlinkIn
    PROCEDURE handlePreUnlinkIn(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
    PROCEDURE handlePostUnlinkIn(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
      -- UnlinkFrom
    PROCEDURE handlePreUnlinkFrom(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
    PROCEDURE handlePostUnlinkFrom(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
      -- CheckIn
    PROCEDURE handlePreCheckIn(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
    PROCEDURE handlePostCheckIn(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
      -- CheckOut
    PROCEDURE handlePreCheckOut(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
    PROCEDURE handlePostCheckOut(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
      -- UncheckOut
    PROCEDURE handlePreUncheckOut(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
    PROCEDURE handlePostUncheckOut(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
      -- VersionControl
    PROCEDURE handlePreVersionControl(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
    PROCEDURE handlePostVersionControl(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
      -- Render
    PROCEDURE handleRender(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
      -- NFS Open
    PROCEDURE handlePreOpen(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
    PROCEDURE handlePostOpen(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
      -- NFS Inconsistant Update
    PROCEDURE handlePreInConUpdate(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
    PROCEDURE handlePostInConUpdate(
        P_EVENT dbms_xevent.XDBRepositoryEvent);
      --
    END HANDLE_CSV_FILES;
    
    /
  • 2. Re: Intro into XDB Repository Events
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    --------------------------------------------------------
    --  DDL for Package Body HANDLE_CSV_FILES
    --------------------------------------------------------
    
      CREATE OR REPLACE PACKAGE BODY "HANDLE_CSV_FILES" 
    AS
      -- ----------------------------------------------------------------------------
      -- Created    : Marco Gralike, AMIS Services BV
      -- Purpose    : Central package to demonstrate XDB Event handling.
      -- Version    : 1.0
      -- ----------------------------------------------------------------------------
      --
      -- Goal here is to pick up newly created file and automatically move it to the
      -- operating system side, while keeping the file "as is" regarding content.
      -- So demonstrate file handling in the XDB Repository while making use of the
      -- XDB Repository Event framework of Oracle's XMLDB database functionality. In
      -- principal this could also be used for all NON hierarchical enabled content;
      -- Content which resides in the LOB column of the XDB$Resource XMLType table
      --
      -- Thanks to:
      --
      -- *) Mark Drake, Senior Product Manager, Oracle XMLDB Development, USA, for
      --    ideas and initial event handling code.
      --    OTN Forum handle: https://forums.oracle.com/forums/profile.jspa?userID=126058
      -- *) Anton Scheffer, Principal Consultant, AMIS Services BV, for learning me
      --    once again new stuff regarding LOB handling / code writing.
      --    OTN Forum handle: https://forums.oracle.com/forums/profile.jspa?userID=252978
      --
      -- ----------------------------------------------------------------------------
      --
      -- handleMain
      --
    PROCEDURE handleMain(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
      --
      BINARY_CONTENT_SCHEMA CONSTANT VARCHAR2(700) := 'http://xmlns.oracle.com/xdb/XDBSchema.xsd#binary';
      TEXT_CONTENT_SCHEMA   CONSTANT VARCHAR2(700) := 'http://xmlns.oracle.com/xdb/XDBSchema.xsd#text';
      --
      v_xdb_resource DBMS_XDBRESOURCE.XDBResource;
      --
      v_resource_path VARCHAR2(700);
      v_event_type    NUMBER;
      v_event_name    VARCHAR2(32);
      v_filename      VARCHAR2(128);
      v_directory     VARCHAR2(30) := 'CSV_STORE';
      v_blob_content  BLOB;
      --
    BEGIN
      -- ------------------------------------------
      -- Get full repository path and event triggered
      -- ------------------------------------------
      v_resource_path := DBMS_XEVENT.getName(DBMS_XEVENT.getPath(P_EVENT));
      v_event_type    := DBMS_XEVENT.getEvent(DBMS_XEVENT.getXDBEvent(P_EVENT));
      -- ------------------------------------------
      -- Associate fired event with action
      -- ------------------------------------------
      CASE v_event_type
      WHEN dbms_xevent.PRE_CREATE_EVENT THEN
        NULL; --example
      WHEN dbms_xevent.POST_CREATE_EVENT THEN
        BEGIN
          --
          V_EVENT_NAME := 'PostCreate';
          -- ------------------------------------------
          -- Get BLOB content and filename
          -- ------------------------------------------
          SELECT extractValue(res,'/Resource/XMLLob') ,
            extractValue(res,'/Resource/DisplayName')
          INTO v_blob_content ,
            v_filename
          FROM RESOURCE_VIEW
          WHERE equals_path(res,v_resource_path) = 1;
          -- ------------------------------------------
          -- Create OS file via BLOB content and filename
          -- ------------------------------------------
          CREATEBLOBFILE(v_blob_content, v_directory, v_filename);
          -- ------------------------------------------
          -- Delete XDB Resource (file) if successful
          -- ------------------------------------------
          DBMS_XDB.DELETERESOURCE(v_resource_path,DBMS_XDB.DELETE_FORCE);
          --
        EXCEPTION WHEN OTHERS THEN
          RAISE;
        END;
    WHEN dbms_xevent.PRE_DELETE_EVENT THEN
      NULL; -- example
    WHEN dbms_xevent.POST_DELETE_EVENT THEN
      NULL; -- example
    END CASE;
    --
    END handleMain;
    -- -----------------------------
    -- CreateBlobFile
    -- -----------------------------
    PROCEDURE CreateBlobFile(
        p_blob BLOB ,
        p_directory VARCHAR2 ,
        p_filename  VARCHAR2 )
    IS
      t_filehandle UTL_FILE.FILE_TYPE;
      t_length pls_integer := 32767;
    BEGIN
      --
      t_filehandle := UTL_FILE.FOPEN( p_directory, p_filename, 'wb' );
      --
      FOR i IN 0 .. TRUNC( ( DBMS_LOB.GETLENGTH( p_blob ) - 1 ) / t_length )
      LOOP
        UTL_FILE.PUT_RAW( t_filehandle, DBMS_LOB.SUBSTR( p_blob, t_length, i * t_length + 1 ) );
      END LOOP;
      --
      UTL_FILE.FCLOSE( t_filehandle );
      --
    END CreateBlobFile;
    -- -----------------------------
    -- handlePreCreate
    -- -----------------------------
    PROCEDURE handlePreCreate(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handlePreCreate;
    -- -----------------------------
    -- handlePostCreate
    -- -----------------------------
    PROCEDURE handlePostCreate(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handlePostCreate;
    -- -----------------------------
    -- handlePreDelete
    -- -----------------------------
    PROCEDURE handlePreDelete(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handlePreDelete;
    -- -----------------------------
    -- handlePostCreate
    -- -----------------------------
    PROCEDURE handlePostDelete(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handlePostDelete;
    -- -----------------------------
    -- handlePreUpdate
    -- -----------------------------
    PROCEDURE handlePreUpdate(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handlePreUpdate;
    -- -----------------------------
    -- handlePostUpdate
    -- -----------------------------
    PROCEDURE handlePostUpdate(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handlePostUpdate;
    -- -----------------------------
    -- handlePreLock
    -- -----------------------------
    PROCEDURE handlePreLock(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handlePreLock;
    -- -----------------------------
    -- handlePostLock
    -- -----------------------------
    PROCEDURE handlePostLock(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handlePostLock;
    -- -----------------------------
    -- handlePreUnlock
    -- -----------------------------
    PROCEDURE handlePreUnlock(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handlePreUnlock;
    -- -----------------------------
    -- handlePostUnlock
    -- -----------------------------
    PROCEDURE handlePostUnlock(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handlePostUnlock;
    -- -----------------------------
    -- handlePreLinkIn
    -- -----------------------------
    PROCEDURE handlePreLinkIn(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handlePreLinkIn;
    -- -----------------------------
    -- handlePostLinkIn
    -- -----------------------------
    PROCEDURE handlePostLinkIn(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
    END handlePostLinkIn;
    -- -----------------------------
    -- handlePreLinkTo
    -- -----------------------------
    PROCEDURE handlePreLinkTo(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handlePreLinkTo;
    -- -----------------------------
    -- handlePostLinkTo
    -- -----------------------------
    PROCEDURE handlePostLinkTo(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handlePostLinkTo;
    -- -----------------------------
    -- handlePreUnlinkIn
    -- -----------------------------
    PROCEDURE handlePreUnlinkIn(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handlePreUnlinkIn;
    -- -----------------------------
    -- handlePostUnlinkIn
    -- -----------------------------
    PROCEDURE handlePostUnlinkIn(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
    END handlePostUnlinkIn;
    --
    -- handlePreUnlinkFrom
    --
    PROCEDURE handlePreUnlinkFrom(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handlePreUnlinkFrom;
    -- -----------------------------
    -- handlePostUnlinkFrom
    -- -----------------------------
    PROCEDURE handlePostUnlinkFrom(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handlePostUnlinkFrom;
    -- -----------------------------
    -- handlePreCheckIn
    -- -----------------------------
    PROCEDURE handlePreCheckIn(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handlePreCheckIn;
    -- -----------------------------
    -- handlePostCheckIn
    -- -----------------------------
    PROCEDURE handlePostCheckIn(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handlePostCheckIn;
    --
    -- handlePreCheckOut
    --
    PROCEDURE handlePreCheckOut(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handlePreCheckOut;
    -- -----------------------------
    -- handlePostCheckOut
    -- -----------------------------
    PROCEDURE handlePostCheckOut(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handlePostCheckOut;
    --
    -- handlePreUncheckOut
    --
    PROCEDURE handlePreUncheckOut(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handlePreUncheckOut;
    -- -----------------------------
    -- handlePostUncheckOut
    -- -----------------------------
    PROCEDURE handlePostUncheckOut(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handlePostUncheckOut;
    --
    -- handlePreVersionControl
    --
    PROCEDURE handlePreVersionControl(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handlePreVersionControl;
    -- -----------------------------
    -- handlePostVersionControl
    -- -----------------------------
    PROCEDURE handlePostVersionControl(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
    END handlePostVersionControl;
    --
    -- handleRender
    --
    PROCEDURE handleRender(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handleRender;
    -- -----------------------------
    -- handlePreOpen
    -- -----------------------------
    PROCEDURE handlePreOpen(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handlePreOpen;
    --
    -- handlePostOpen
    --
    PROCEDURE handlePostOpen(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handlePostOpen;
    -- -----------------------------
    -- handlePreInConUpdate
    -- -----------------------------
    PROCEDURE handlePreInConUpdate(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handlePreInConUpdate;
    -- -----------------------------
    -- handlePostInConUpdate
    -- -----------------------------
    PROCEDURE handlePostInConUpdate(
        P_EVENT dbms_xevent.XDBRepositoryEvent)
    AS
    BEGIN
      --
      handleMain(P_EVENT);
      --
    END handlePostInConUpdate;
    --
    END HANDLE_CSV_FILES;
    
    /
  • 3. Re: Intro into XDB Repository Events
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Information about Oracle XMLDB Repository Events can be found here:

    http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_repos_events.htm#BABGADEH

    An introduction into can be found here: http://www.liberidu.com/blog/?p=3291

    Another great example from Mark here: XMLIndex Value Index on repository