Maybe you have already read about Yahoo Pipes which you can use to build your own mashups.
It isn’t really hard to do that, because the Oracle database has already everything build in to accomplish that.
1. Create your own pipe
I created one which gets different RSS feeds, translates them with Babelfish into German and filters all which have the keyword APEX in the text. The German into which it is translating doesn’t really make sens, but that’s a other story… 🙂
2. Run your pipe
Run your pipe (you don’t have to publish it) and look for “Tools: Get as RSS” at the bottom of the page. Get the URL, eg it looks like http://pipes.yahoo.com/pipes/0HpUBCq52xGdaeRPfOgC8A/run?_render=rss
3. Create a report in APEX
Create a SQL based report with the following SQL statement.
SELECT EXTRACTVALUE(VALUE(ITEM), '/item/link') AS LINK , EXTRACTVALUE(VALUE(ITEM), '/item/title') AS TITLE , TO_DATE ( SUBSTR(EXTRACTVALUE(VALUE(ITEM), '/item/pubDate'), 6, 20) , 'DD Mon YYYY HH24:MI:SS' ) AS PUBLISH_DATE FROM TABLE ( XMLSEQUENCE ( EXTRACT ( HTTPURITYPE('http://pipes.yahoo.com/pipes/0HpUBCq52xGdaeRPfOgC8A/run?_render=rss').getXML() , '/rss/channel/item' ) ) ) ITEM ORDER BY 3 DESC NULLS LAST
Replace the URL with your stored URL
About the SQL Statement:
The Oracle build-in HTTPURITYPE is really powerful, it will retrieve the content of an URL and if you specify getXML, automatically convert the XML output into an Oracle XMLTYPE. As soon as it is a XMLTYPE you can use all the XML build-ins to extract the data from the XML stream.
More info about HTTPURITYPE can be found at Carsten Czarski’s blog (in German).