Monday, February 7, 2011

Fun with RSS and PL/SQL, Part One

If you follow one or more blogs, you have probably heard about RSS.

Several varieties of the RSS format exist, so I will refer to them collectively as "feeds" (and when I say "RSS" I really mean any kind of feed).

RSS can be used to publish not just blogs, but also other kinds of information, such as news updates, audit trails, log entries, and so on.

The Alexandria library for PL/SQL contains RSS_UTIL_PKG, a package for publishing and parsing RSS feeds.

In this blog post I will describe how we can use this package to read (parse) RSS feeds. A follow-up post will describe how the same package can be used to publish any query as a feed.

Reading an RSS feed with SQL

The RSS_UTIL_PKG package contains a pipelined function named RSS_TO_TABLE which can be used in a SQL query to extract feed values.

A single line to read an RSS feed, that's not too shabby, eh?

Reading an RSS feed with PL/SQL

Another function in the RSS_UTIL_PKG is RSS_TO_LIST, which returns a PL/SQL associative array.

After the feed has been parsed to a list, you can loop through it and perform any required processing.

Of course you could also use the pipelined function in a cursor FOR loop, depending on your coding style.

Creating a report in Apex based on an RSS feed

Since you can use RSS_TO_TABLE in SQL statements, you can also use it as the basis for a report region in Apex.

Create a page and add a text item to input the URL of an RSS feed.

Then add a report region (classic or interactive) and use the following query as the report source:

Running the page produces the following result:

That's it for reading (parsing) RSS feeds. In my next blog post I will describe how to publish RSS feeds from the database using PL/SQL and SQL.


Anton said...

Love the RSS package you made. I build myself a peronal rss-reader for myself in apex quite easy.
But here is my problem: The first, oh, 15 feed and ran throught the "select * from table(rss_util_pkg.rss_to_table(httpuritype('http://feedaddress').getclob())) worked fine. but one gave me problem. Namnly this feed:

The errors it gives me is:
ORA-01706: user function result value was too large
ORA-06512: at "SC2FOOD.RSS_UTIL_PKG", line 284
ORA-06512: at "SC2FOOD.RSS_UTIL_PKG", line 361
ORA-06512: at line 1
01706. 00000 - "user function result value was too large"

Thanks for any help or it might just help your progress of the package in the future.

Morten Braten said...

@Anton: The ORA-01706 error is related to a length limitation in the XML parser. This may (or may not) have been fixed in Oracle 11g, see this thread:

As noted in that thread, a workaround could perhaps be implemented using the DBMS_XMLDOM package.

- Morten

Ganim said...

This is really what I needed, great!
But I the Date field is null. How can I fix this?