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.
Download the Source Code for this article: rss.zip.
- Building an RSS Feed Reader in PL/SQL – using dbms_xmlparser, dbms_xmldom and dbms_xslprocessor for parsing and transforming
- Publishing PL/SQL Based WebServices
- Summary results for all dates, including the ones that I do not have data for – example of using Partition Outer Join – Oracle10g SQL Feature
- Trick for multiple and flexible parameter passing with MOD_PLSQL and Web PL/SQL toolkit
- Publishing PL/SQL "Services" as WebService using Oracle BPEL