Integrating Yahoo Pipes into APEX

Maybe you have already read about Yahoo Pipes which you can use to build your own mashups.

Today, I did a quick test. It has a nice Web/JavaScript interface and is really easy to use. After I finished my first "Pipe" I thought about how to integrate the output into Oracle APEX.

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).

4. Run your APEX report