3 Replies Latest reply: Nov 29, 2011 3:55 PM by Marco Gralike RSS

    Intro into XDB Repository Events

    Marco Gralike
      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
          Marco Gralike
          --------------------------------------------------------
          --  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
            Marco Gralike
            --------------------------------------------------------
            --  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
              Marco Gralike
              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