Have you read the news today?

You may already know that Oracle has the capability to get data directly from the Web. Like in:

SELECT HTTPURITYPE.createuri (‘http://www.economist.com/rss/leaders_rss.xml’).getclob () RSS FROM DUAL;

This offers a lot of possibilities when you need to query external data and, combined with some XML magic, can make a cute RSS view out of a well-formed xml feed:

   SELECT EXTRACTVALUE (VALUE (p), ‘/item/title’) title,
          EXTRACTVALUE (VALUE (p), ‘/item/link’) link_url,
          EXTRACTVALUE (VALUE (p), ‘/item/description’) description
             XMLSEQUENCE (EXTRACT (xmltype (HTTPURITYPE.createuri (‘http://www.economist.com/rss/leaders_rss.xml’).getclob (),
                                   ‘xmlns:media=”http://search.yahoo.com/mrss/”‘))) p;

Nice and cheap, no need to store data in the RDBMS. Every time you access the view the data is read from the Web. Just change the rss url according to your feed (you will probably need to rework the EXTRACT functions also).

Note: if you’re on Oracle 11g you may have to grant connecttion to the rss feed domain thru’ an ACL (ask your DBA for details…)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.