Producing RSS from PL/SQL

First things first. The idea and the bulk of the code for this post are not mine, they are Sean Dillon’s. Sean is a good friend of mine and originally wrote this code for AskTom. You can actually still find Sean’s code here. It’s a very cool idea that he came up with and it still works flawlessly today after at least 3 years.

I needed to incorporate RSS into a project I’m working, so I grabbed Sean’s code. The problem is, it’s based on the AskTom table structure which means it won’t run on your database without immediately re-writing the query. Additionally, the nature of this code relies on a pretty lengthy query to generate the XML. I’ll admit, when I first looked at it, I thought “Wow, this is going to be more complex than I thought.” After looking at it for a little while longer, I realized it was actually very simple. Sean also included support for several versions of RSS, improving the functionality, but again, adding to the complexity.

So, I created an example table and simplified the code as much as possible to make it easier for everyone to understand. The table, “PLSQL_PACKAGES”, stores information about some of the built-in PL/SQL packages I use on a regular basis. The links in this table point back to the online Oracle Documentation.

This block of code is just the DDL for the sample table and the insert statements to populate it:

create table plsql_packages(
    id          varchar2(32),
    title       varchar2(255),
    description varchar2(4000),
    link        varchar2(1000),
    updated_by  varchar2(100),
    updated_on  date)
/

create or replace trigger  biu_plsql_packages before insert or update on plsql_packages
for each row
begin
	if inserting then
		:new.id := sys_guid();
	end if;
        :new.updated_by := nvl(v('APP_USER'),user);
        :new.updated_on := sysdate;
end;
/

insert into plsql_packages(title,description,link)
     values ('DBMS_CRYPTO','DBMS_CRYPTO provides an interface to encrypt and decrypt stored data, and can be used in conjunction with PL/SQL programs running network communications. It provides support for several industry-standard encryption and hashing algorithms, including the Advanced Encryption Standard (AES) encryption algorithm. AES has been approved by the National Institute of Standards and Technology (NIST) to replace the Data Encryption Standard (DES).','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_crypto.htm#i1005082' 
/

insert into plsql_packages(title,description,link)
     values ('DBMS_EPG','The DBMS_EPG package implements the embedded PL/SQL gateway that enables a web browser to invoke a PL/SQL stored procedure through an HTTP listener.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_epg.htm#sthref3481' 
/

insert into plsql_packages(title,description,link)
     values (' OWA_UTIL','The OWA_UTIL package contains utility subprograms for performing operations such as getting the value of CGI environment variables, printing the data that is returned to the client, and printing the results of a query in an HTML table.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/w_util.htm#sthref17019' 
/

insert into plsql_packages(title,description,link)
     values ('UTL_MAIL','The UTL_MAIL package is a utility for managing email which includes commonly used email features, such as attachments, CC, BCC, and return receipt.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/u_mail.htm#i1001258' 
/

insert into plsql_packages(title,description,link)
     values ('DBMS_STATS','With the DBMS_STATS package you can view and modify optimizer statistics gathered for database objects.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_stats.htm#sthref9773' 
/

insert into plsql_packages(title,description,link)
     values ('DBMS_LDAP_UTL','The DBMS_LDAP_UTL package contains the Oracle Extension utility functions.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_ldaputl.htm#i997505' 
/

insert into plsql_packages(title,description,link)
     values ('DBMS_FLASHBACK','Using DBMS_FLASHBACK, you can flash back to a version of the database at a specified wall-clock time or a specified system change number (SCN).','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_flashb.htm#i998870' 
/

insert into plsql_packages(title,description,link)
     values ('DBMS_FGA','The DBMS_FGA package provides fine-grained security functions.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_fga.htm#i1001938' 
/

insert into plsql_packages(title,description,link)
     values ('APEX_UTIL','The APEX_UTIL package provides utilities for getting and setting session state, getting files, checking authorizations for users, resetting different states for users, and also getting and setting preferences for users.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/a_hdbutl.htm#sthref40' 
/

insert into plsql_packages(title,description,link)
     values ('DBMS_RLS','The DBMS_RLS package contains the fine-grained access control administrative interface, which is used to implement Virtual Private Database (VPD). DBMS_RLS is available with the Enterprise Edition only.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_rls.htm#i1000830' 
/

This is the important block of code as it creates the RSS procedure. Don’t be intimidated by it though as you only need to modify a few lines. The only lines you need to customize to make it work against your own table are 4-7 and 41-43!

create or replace procedure rss
is
    -- customizable parameters
    l_title         varchar2(255) := 'Oracle PL/SQL Packages';
    l_link          varchar2(255) := 'http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/toc.htm';
    l_description   varchar2(255) := 'This is a feed of changes to PL/SQL Package Documentation';
    l_language      varchar2(255) := 'en-us';
    -- end customizable parameters
    l_version       varchar2(10)  := '2.0';
    l_clob          clob;
    l_idx           pls_integer := 1;
    l_len           pls_integer := 255;
    l_defrows       pls_integer := 10;
    l_maxrows       pls_integer := 30;
    l_desclen       pls_integer := 250;
begin
    for i in (
      select xmlelement( "rss",
               -- Begin XML Header Block
               xmlattributes( l_version as "version"),
                 xmlelement( "channel",
                   xmlforest( l_title as "title",
                              l_link as "link",
                              l_description as "description",
                              l_language as "language"),
                 -- End XML Header Block
                 -- Begin List of Individual Articles or Items
                 xmlagg(
                     xmlelement( "item",
                       xmlelement("title", x.title),
                       xmlelement("link", x.link),
                       xmlelement("description", x.description),
                       xmlelement("pubDate", to_char(x.updated_on,'Dy, DD Mon RRRR hh24:mi:ss')),
                       xmlelement("guid", XMLATTRIBUTES('false' as "isPermaLink"),x.id||to_char(x.updated_on,'JHH24MISS'))
                     )
                   )
                   -- End List of Individual Articles or Items
                 )
             ) as result
        from (  -- Actual Database Query that populates the list of Items
                select id,title,link,description,updated_on
                  from plsql_packages
                 where rownum < (l_maxrows+1)) x)
    loop
        l_clob := xmltype.extract(i.result,'/').getclobval;
        exit;
    end loop; --i

    --- OUTPUT RESULTS
    owa_util.mime_header('application/xml', false);
    owa_util.http_header_close;
    for i in 1..ceil(dbms_lob.getlength(l_clob)/l_len) loop
        htp.prn(substr(l_clob,l_idx,l_len));
        l_idx := l_idx + l_len;
    end loop; --i
end rss;
/

Note on line 34 the “guid” element. This is an optional element (documented here) that an aggregator can use to uniquely identify the item. I’m concatenating the ID column from the table with Julian date concatenated with hours, minutes, and seconds - to_char(sysdate,‘JHH24MISS’). This means that when you update a row, the date will change causing your aggregator to see a new guid and display a new item for the changed row.The easiest way to test this procedure is using the “OWA Output” tab in SQL Developer:

If you’re running XE or 11g and you want to call this procedure directly through the APEX DAD, you’ll need to edit the FLOWS_XXXXXX.wwv_flow_epg_include_mod_local function and comment-out the first line as well as your procedure to the IN list. Dietmar Aust has a nice post on this.

Once you have your feed displayed in a reader, try updating one of the rows so the updated_on changes. Now refresh the feed in your feed reader and you should see a new item for row you updated. Here’s a link to the code in action on the beta site of apex.oracle.com (this is an RSS feed, so you might want to test it in a reader such as Thunderbird, NetVibes, or Google Reader).

---

Possibly related posts: (automatically generated)

• Image Caching in PL/SQL Applications
• oracle-developer.net’s feed