Publishing Data Manipulation as an RSS Feed – using Oracle MOD_PLSQL and Flashback

Events in the database – such as Data Manipulation or DML, Database Object Manipulation or DDL, session connect and disconnect, database startup and shutdown, a user querying for specific data – can easily be spotted. The database has an extensive Event Trigger mechanism that we can hook into. Besides that it has various Audit options, including Fine Grained Auditing with dbms_fga, that also allows us to trigger our own code upon select or DML operations. We can have our triggers fired for the appropriate events have collect information and record them in our custom archive. Then create ways to report the data from that archive.

However, most recent information is already available without a need for additional archiving. The ALL_OBJECTS data dictionary view has a column LAST_DDL_TIME that we can use to find which objects were most recently changed. Furthermore, thanks to the Flashback mechanism and the VERSIONS_… pseudo columns provided to us in the 10g Release of the database, we can look back in time (a little bit, depending on the Retention Time, the Undo Space and the frequency of data manipulations in our database).

This article shows how we can leverage this information very easily to publish an RSS Feed that describes recent changes in a particular table. That could look something like:

Crucial for publishing these changes are the pseudo-columns versions_starttime, versions_endtime, versions_operation that were introduced in Oracle 10g. These pseudo-columns are part of the Flashback mechanism that also allows us to undo the accidental drop of a table or recover from recent data manipulation. The core query we use in the RSS Feed we publish for table DEPT is the following:

select versions_starttime
,      versions_endtime
,      versions_xid
,      nvl(versions_operation,'I') versions_operation
,      dname
,      lead(dname) over (partition by deptno order by versions_starttime desc nulls last) previous_dname
,      deptno
,      loc
,      lead(loc) over (partition by deptno order by versions_starttime desc nulls last) previous_loc
from   depts versions between timestamp minvalue and maxvalue
order
by     versions_starttime desc nulls last

This query retrieves all records from DEPT, either the ones currently in that table or the ones recently deleted from it. And also the state of records as it existed prior to recent Update operations. In so far the UNDO Tablespace still contains that information, the clause

from   depts versions between timestamp minvalue and maxvalue

returns historic records in addition to the current ones.

Creating an RSS Feed

An RSS feed is a very (RSS=Real Simple Syndication) straightforward XML document, that is deliverd on a URL as response to simple Http GET Request. The Oracle Database is easily opened up for such requests with the MOD_PLSQL option on the HttpServer. You will probably have set it up, as various services including Application Express (fka HTML DB) and the Oracle Designer Repository Object Browser make use of it.

I will not go into the details of configuring MOD_PLSQL – in brief it requires a DAD (Database Access Descriptor) to be set up that connects a URL to a database schema. Subsequently, URLs can be created that directly translate to Package calls. These Packages are to return content through the HTP package. The buffer of the HTP package is in turn wrapped in (or as) a Http Response. Note that the response from a MOD_PLSQL request is usually HTML content but can also be XML or any other type of ascii or even binary content.

The core of the RSS creating code looks as follows:

create or replace package rss_dml
is
procedure feed;
end;
/
create or replace package body rss_dml
is

procedure channel
( p_title in varchar2
, p_description in varchar2
) is
begin
  htp.p('
	<channel>
<title>'||p_title||'</title>
<description>'||p_description||'</description>
<pubDate>'||to_char(systimestamp)||'</pubDate>
<language>en</language>');

end channel;

procedure item
( p_title in varchar2
, p_pubDate in varchar2
, p_guid in varchar2
, p_description in varchar2
) is
begin
    htp.p('<item>
    	  <title>'||p_title||'</title>
          <pubDate>'||p_pubDate||'</pubDate>
          <creator>John Doe</creator>
          <category>DML Modifications</category>
          <guid>'||p_guid||'</guid>
	  <description>'||p_description||'
           </description>
       </item>');
end item;

procedure feed
is
begin
  owa_util.mime_header('text/xml', FALSE);
  owa_util.http_header_close;
  htp.p('<?xml version=''1.0'' ?>');
  htp.p('<rss version="2.0">');
  channel
  ( p_title => p_table_name||' DML Events'
  , p_description => 'Overview of the most recent DML operations in our database on table DEPT'
  );
  loop ... -- over all recently changed records in DEPT
    item
    ( p_title => 'Change History for Department '||o.deptno
    , ...
    );
   end loop;
   htp.p('</channel>');
   htp.p('</rss>');
end;

end;
/

The output of the procedure feed is something like:

We have used a little logic to show the changes that have taken place as well as properly label Insert and Delete events:

  for o in (select versions_starttime
            ,      versions_endtime
            ,      versions_xid
            ,      nvl(versions_operation,'I') versions_operation
            ,      dname
            ,      lead(dname) over (partition by deptno order by versions_starttime desc nulls last) previous_dname
            ,      deptno
            ,      loc
            ,      lead(loc) over (partition by deptno order by versions_starttime desc nulls last) previous_loc
            from   depts versions between timestamp minvalue and maxvalue
            order
            by     versions_starttime desc nulls last
           ) loop
      if nvl(o.previous_loc,'X') <> nvl(o.loc,'X')
         or
         nvl(o.previous_dname,'X') <> nvl(o.dname,'X')
         or
         o.versions_operation in ('I','D')
      then
        item
        ( p_title => 'Change History for Department '||o.deptno
        , p_pubDate => to_char(nvl(o.versions_starttime,o.versions_endtime),'DAY, MON, DD, YYYY hh24:mi' )
        , p_guid => o.deptno||'-'||o.versions_xid
        , p_description => 'Department '||o.deptno||' was '||
	  case o.versions_operation
	  when 'I' then 'inserted'
	  when 'U' then 'updated'
	  when 'D' then 'deleted'
	  end
	  ||':	'||
	case
	when nvl(o.previous_loc,'X') <> nvl(o.loc,'X')
	then '* The location was changed from '||o.previous_loc||' to '||o.loc
	end
	||
	case
	when nvl(o.previous_dname,'X') <> nvl(o.dname,'X')
	then '* The dname was changed from '||o.previous_dname||' to '||o.dname
	end
        );
     end if;
   end loop;
 

In a similar fashion, we can create an RSS Feed for DDL operations in our database: 

RSS Feed of DDL operations in the database

The code for this feed is almost the same as for the DML feed. Only the query that collects the change data is obviously another:

select last_ddl_time
,      owner
,      object_name
,      object_type
,      object_id
from   ( select *
         from   all_objects
         where  owner <> 'SYS'
         and    object_name not like 'BIN%'
         order
         by     last_ddl_time desc
       )

Note: this feed does currently not include DROP operations on objects no longer with us. Also it has at the most a single entry for every object in our database. I am not sure whether we can use VERSIONS BETWEEN with ALL_OBJECTS. Let's try:

select last_ddl_time
,      owner
,      object_name
,      object_type
,      object_id
from   ( select *
         from   all_objects versions between timestamp minvalue and maxvalue
         where  owner <> 'SYS'
         and    object_name not like 'BIN%'
         order
         by     last_ddl_time desc
       )
where rownum < 50
/
ERROR at line 7:
ORA-30051: VERSIONS clause not allowed here

so I think not. Pity. 

Resources

Download the Source Code for this article: rss.zip.