Have you read the news today?

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:

 CREATE VIEW RSS_READER
AS
   SELECT EXTRACTVALUE (VALUE (p), ‘/item/title’) title,
          EXTRACTVALUE (VALUE (p), ‘/item/link’) link_url,
          EXTRACTVALUE (VALUE (p), ‘/item/description’) description
     FROM TABLE (
             XMLSEQUENCE (EXTRACT (xmltype (HTTPURITYPE.createuri (‘http://www.economist.com/rss/leaders_rss.xml’).getclob (),
                                            NULL,
                                            1,
                                            1),
                                   ‘/rss/channel/item’,
                                   ‘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

Leave a Reply