Oracle APEX: Creating a simple RSS feed

A few weeks ago I attended the Mid Atlantic Oracle User Group (MAOP) meeting in Reston Virginia and although I sat in traffic for about 5 hours that day I was intrigued by some of the things I saw people doing with Oracle APEX.

One potential use case I could think of right away was creating RSS feeds from simple database queries. These RSS feeds could link out to existing applications which have easy to understand URL's. An example of an "easy to understand url" would be something like the following Google URL: http://www.google.com/search?q=Oracle+APEX as the parameter "q" has a value of a string.

So...
I dug around and couldn't find an example of someone doing this so I posted a "tweet" using the Oracle and Oracle APEX hashtags (#Oracle & #APEX) and sure enough a persone from Germany (Carsten Czarski) was nice enough to point me to this page which is written in German. I don't know German but this posting was enough for me to get dangerous and create an RSS feed using Oracle APEX.

So, without further discussion, here is the RSS feed I built and how I built it

1) coded the following Oracle PL/SQL Stored Procedure

CREATE OR REPLACE PROCEDURE sp_rssfeed(
  p1 IN NUMBER DEFAULT NULL
) AS
  v_xml blob;
  v_user        VARCHAR2(100);
BEGIN
    SELECT user
    INTO    v_user
    FROM    dual;

  WITH a AS(
    SELECT *
    FROM (
      SELECT owner, count(1) num_tabs
      FROM   all_tables
      GROUP BY owner
      order by count(1) desc
    )
    WHERE rownum <= 5
  )
  SELECT 
    XMLElement("rss",
     XMLAttributes('2.0' as "version"),
     XMLElement("channel",
      XMLElement("title",
              'Title: APEX test RSS Feed, User:= '||v_user),
      XMLElement("link",
               'http://www.oracle.com/global/de/community'),
      XMLElement("description", 
               'Description: APEX test RSS Feed, p1:='||p1),
      XMLElement("language", 'en-us'),
      XMLElement("copywrite", 'Copyright '||
           to_char(sysdate, 'YYYY')||' Rich Murnane'),
      (
       XMLAgg(
        XMLElement("item",
        XMLElement("pubDate",
            to_char(sysdate - rownum, 
                 'DAY, DD MON YYYY HH24:MI:SS')),
        XMLElement(
          "title", 
          'The DB user named: '||a.owner||' has '||
                       a.num_tabs||' table(s)'
          ),
        XMLElement(
          "link",
          'http://richmurnane.blogspot.com'
         )
       )
      ) 
     )
    )
  ).getblobval(nls_charset_id('AL32UTF8')) into v_xml
  from a;
  owa_util.mime_header('text/xml');
  wpg_docload.download_file(v_xml);
  dbms_lob.freetemporary(v_xml);
END;
/
show errors

2) deployed the Stored Procedure in my personal schema and granted execute to public

3) went to the following URL on my laptop (which had APEX running on it, as well as Oracle XE)
http://localhost:8888/apex/RICH.SP_RSSFEED


The content of the XML document:

<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0">
  <channel>
    <title>Title: APEX test RSS Feed, User:= APEX_PUBLIC_USER</title>
    <link>http://www.oracle.com/global/de/community</link>
    <description>Description: APEX test RSS Feed, p1:=</description>
    <language>en-us</language>
    <copywrite>Copyright 2010 Rich Murnane</copywrite>

    <item>
      <pubDate>THURSDAY , 11 MAR 2010 07:24:19</pubDate>
      <title>The DB user named: MDSYS has 44 table(s)</title>
      <link>http://richmurnane.blogspot.com</link>
    </item>
    <item>
      <pubDate>WEDNESDAY, 10 MAR 2010 07:24:19</pubDate>

      <title>The DB user named: SYS has 27 table(s)</title>
      <link>http://richmurnane.blogspot.com</link>
    </item>
    <item>
      <pubDate>TUESDAY  , 09 MAR 2010 07:24:19</pubDate>
      <title>The DB user named: WKSYS has 10 table(s)</title>
      <link>http://richmurnane.blogspot.com</link>

    </item>
    <item>
      <pubDate>MONDAY   , 08 MAR 2010 07:24:19</pubDate>
      <title>The DB user named: TEST has 9 table(s)</title>
      <link>http://richmurnane.blogspot.com</link>
    </item>
    <item>

      <pubDate>SUNDAY   , 07 MAR 2010 07:24:19</pubDate>
      <title>The DB user named: SYSTEM has 8 table(s)</title>
      <link>http://richmurnane.blogspot.com</link>
    </item>
  </channel>
</rss>

Overall, very simple and easy to do - the primary issues I see with Oracle APEX:
1) Not all data geeks speak Oracle, too bad because this tool could really make all our lives much easier.
2) Not all shops are going to want to move to APEX. Issues folks might have with include
- Opening up ports to DB servers from outside firewalls
- It's a paradigm shift

In summary, no longer do the user interfaces have to be built by software engineers as now data geeks (architects, database administrators, analysts, etc.) can really become empowered with this. It still might take some time for this to really take off but I've been keeping an eye on Oracle APEX for the last few years and I can tell you that it really does seem to have come a long way w/r/t the technology and adoption. Empowering your high-end knowledge "technologists" (aka data geeks) to build UI's that make their workload easier should in fact be a very powerful argument to have your organization take APEX for a test ride.

Until next time...Rich