Integrating Yahoo Pipes into an Oracle Application Express Application

Date: 20-Feb-2007
Author: Patrick Wolf

After completing this How-To, you will:

• Understand how to use a Yahoo Pipe output in your Oracle Application Express (Oracle APEX) Application.
• Know more about the powerful features of the Oracle Database.

Table of Contents

• Introduction
• Software Requirements
• Creating your own Yahoo Pipe
• Run your Yahoo Pipe
• Create a report in Oracle APEX
• Run the report
• Conclusion

Maybe you have already read about Yahoo Pipes which you can use to build your own mashups. It has a really nice Web/JavaScript interface and is easy to use. A new mashup called Pipe can be created within minutes.

But how to integrate the output of such a Yahoo Pipe into your Oracle APEX Application?

This document describes how to leverage the power of the built-in features of the Oracle Database to include the Pipe output as a report into an Oracle APEX Application.

• Oracle APEX 2.x
• Oracle Database 10g or Oracle XE
• Web browser like Firefox or Internet Explorer

Logon to Yahoo Pipes and create your own pipe. For this How-To I have created a Pipe which:

1. Reads the RSS feed of three different blogs,
2. translates them with the BabelFish service from English to German
3. and finally filters all the postings which contain APEX in their description.

Figure 1: Yahoo Pipe to translate an RSS feed

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, e.g. it looks like

http://pipes.yahoo.com/pipes/0HpUBCq52xGdaeRPfOgC8A/run?_render=rss

Create a SQL based report (Create Region\Report\SQL 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. The output of the URL is an RSS feed with a defined XML format. You can see the format when you paste the URL into your browser and use the "View Page Source" to view the XML output.

The Oracle built-in package 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 Oracle XML built-ins to extract the data from the XML stream.

For more information about HTTPURITYPE or the XML built-ins, see

• PL/SQL Packages and Types Reference: Summary of HTTPURITYPE Subtype Subprograms
• SQL Reference: EXTRACT
• SQL Reference: XMLSEQUENCE
• SQL Reference: EXTRACTVALUE
• Carsten Czarski's blog (in German)

Run your Oracle APEX page and you should get a similar output.

Figure 2: Oracle APEX Report showing Yahoo Pipe output

As you can see from the above description, integrating Web-/XML content into your Oracle APEX Application can be quite simple if you combine the power of Oracle APEX and the underlying Oracle Database.

Feel free to discuss this How-To in the Oracle APEX OTN Discussion Forum.

Patrick Wolf is working at Sphinx IT Consulting as a Senior Solution Architect and is an Oracle specialist (PL/SQL, Oracle APEX, Oracle Developer) who likes to share his Oracle APEX expertise. He is running an Oracle APEX blog and is the creator of the Open Source Oracle APEX development framework ApexLib and the Oracle APEX Builder Plugin. He can be contacted at patrick.wolf@sphinx.at