Simple RSS via mod_plsql

If anyone hasn't heard there's a website to submit various things to share with SQL Developer users and the development team. This site was built using APEX. The RSS feed is just plain plsql. Here's how simple can be to get an rss feed from the database.

Here's all I did for the feed of the exchange for the tip that have been submitted.

create or replace procedure RECENT_TIPS_RSS
is
begin
rss(p_title =>'SQL Developer Recent Tips and Tricks',
    p_base_link =>'http://www.oracle.com/technology/products/database/sql_developer/index.html',
    p_base_descr =>'This is a list of recently published Tips and Tricks for Oracle SQL Developer',
    p_item_link =>'http://htmldb.oracle.com/pls/otn/f?p=42626:54:::::P54_ID:',
    p_sql =>'select id,name,created_by,created_on,description from (select * from applications where status=''APPROVED''    order by created_on desc ) where rownum < 10 ');
end;

This uses a simple procedure I made which takes in the sql to be run. Most of the parameters are obvious however the item link and the sequence of the select list are the 2 that need to be explained.

The select list is expected to be in this order id , title , author , date , optionally a link to the item. Then the rest of the columns are all dumped into the description. The link is optional because in this tip example there's a p_item_link passed in. When this is passed in the link to the item is this link with the id concatted to the end. So in this feed an item link would be http://htmldb.oracle.com/pls/otn/f?p=42626:54:::::P54_ID:221 where the 221 is the id. If the p_item_link is not passed in, then column 5 is expected to be the link. This should be obvious that this is used when the links go to different urls.

Hopefully, someone find this useful. I also have this as an atom feed if people want that.

procedure rss(p_title varchar2,
              p_base_link varchar2,
              p_base_descr varchar2,
              p_item_link varchar2,
              p_sql varchar2,
              p_debug varchar2 default 'N')
AS
  l_descTbl dbms_sql.desc_tab2;
  l_colCnt NUMBER;
  l_cursor NUMBER;
  l_id varchar2(4000);
  l_title varchar2(4000);
  l_author varchar2(4000);
  l_link varchar2(4000);
  l_date date;
  l_ret number;
  l_col_val varchar2(32767);
  l_start number := 5;
  l_stub varchar2(2000) := ' ';
  i number;
  k number := 0;

  // format an oracle date
  function formatDate(l_d date) return varchar2 as
     begin
        return to_char(l_d,'RRRR-MM-DD')||'T'||to_char(l_d,'HH24:MI:SS')||'Z';
     end;
 begin
   owa_util.mime_header('text/xml', FALSE );
   htp.p('Cache-Control: no-cache');
   htp.p('Pragma: no-cache');
   owa_util.http_header_close;
   htp.p('<?xml version="1.0"?>');
   htp.p('<rss version="2.0">');

-- debug print the sql as a comment
   if ( p_debug = 'Y' ) then
      htp.p('<!-- '||p_sql ||'-->');
   end if;

-- print the header info
   htp.p('<channel>');
   htp.p('<title>'||p_title||'</title>');
   htp.p('<link>'||p_base_link||'</link>');
   htp.p('<description>'||p_base_descr||'</description>');
   htp.p('<language>enus</language>');
   htp.p('<lastBuildDate>Tue, 10 Jun 2003 09:41:01 GMT</lastBuildDate>');
   htp.p('<docs>http://blogs.law.harvard.edu/tech/rss</docs>');
   htp.p('<generator>mod_plsql rss</generator>');
   htp.p('<managingEditor>kris.rice@oracle.com</managingEditor>');
   htp.p('<webMaster>kris.rice@oracle.com</webMaster>');

-- open the cursor and start looping
   l_cursor := dbms_sql.open_cursor;
   dbms_sql.parse(l_cursor, p_sql||l_stub, dbms_sql.native);
   dbms_sql.describe_columns2( l_cursor,l_colCnt,l_descTbl );
   l_ret := dbms_sql.execute(l_cursor);
   -- define the columns
   for i in 1.. l_colcnt loop
      if ( i != 4 ) then
         dbms_sql.define_column(l_cursor, i, l_col_val, 32767 );
      else
         dbms_sql.define_column(l_cursor, i, l_date);
      end if;
   end loop;
-- 
-- Main cursor loop
-- 
loop
   if dbms_sql.fetch_rows(l_cursor) = 0 then
      exit;
   end if;
--
-- Expected sequence of columns is id , title , author , date , optionally a link to the item
--
   dbms_sql.column_value(l_cursor, 1, l_id);
   dbms_sql.column_value(l_cursor, 2, l_title);
   dbms_sql.column_value(l_cursor, 3, l_author);
   dbms_sql.column_value(l_cursor, 4, l_date);
   htp.p('<item>');
   htp.p('<title><![CDATA['||l_title||']]> </title>');
   htp.p('<author><![CDATA['||l_author||']]> </author>');
   htp.p('<pubDate><![CDATA['||formatDate(l_date)||']]> </pubDate>');

--
-- If p_item_link was set for the item print it and tack on the id to the end
-- Otherwise print colum 5 as the link
-- 
   if ( p_item_link is not null ) then
      htp.p('<link>'||p_item_link||l_id||'</link>');
   else
      dbms_sql.column_value(l_cursor, 5, l_link);
      htp.p('<link>'||l_link||'</link>');
      l_start := l_start +1;
   end if;

--
-- Now loop the rest of the columns in the select list and dump them into the description
--
   htp.p('<description><![CDATA[');
   for i in l_start.. l_colcnt loop
      -- Dump the cols into the description
      dbms_sql.column_value(l_cursor, i, l_col_val);
      htp.p(lower(l_desctbl(i).col_name) ||':'||l_col_val||'<br />'); 
   end loop; -- col loop


   htp.p(']]> </description>');
   htp.p('<guid>');
   htp.p(l_id);
   htp.p('</guid>');
   htp.p('</item>');
   end loop; -- cursor loop

   htp.p('</channel>');
   htp.p('</rss>');
   exception when others then
      htp.p(SQLERRM);
end rss;