Fun with RSS and PL/SQL, Part Two

In my previous post, I talked about how to read (parse) RSS feeds using PL/SQL.

This post will cover how to publish your own RSS feeds, based on a SQL query.

You need the RSS_UTIL_PKG from the Alexandria library for PL/SQL.


Publishing an RSS feed using PL/SQL

The RSS_UTIL_PKG package contains the function REF_CURSOR_TO_FEED, which takes a REF CURSOR parameter and returns a CLOB with the RSS feed (the actual format can be specified and includes RSS, Atom and RDF).

Note that any query (REF CURSOR) can be used, and the column names in your query are irrelevant. What you must do is make sure that the number and order of columns (as well as the data types) match that of the T_FEED_ITEM record type defined in the RSS_UTIL_PKG package specification.

In other words, just make sure your query includes the following information in this order: ID, title, description, link and date.

Once you have the RSS feed as a CLOB, you would use the PL/SQL Web Toolkit (OWA) to print out the contents on a web page. You should also set the MIME type to XML, as in the following example:

Example: Creating a live feed of database errors

Let's put the RSS_UTIL_PKG to good use with an example. Let's say you are part of a development team, all working on various pieces of PL/SQL code in a shared development or test database.

In this situation it would be useful to see the current status of any invalid objects or PL/SQL compilation errors in the database.

Using an RSS feed, we can create a "live bookmark" in Firefox that shows us the status information in a convenient manner, without leaving the web browser.

We will do the following:

• Create a query that lists PL/SQL compilation errors, based on the USER_ERRORS view, and return the results as a REF CURSOR.
• Convert the REF CURSOR to an RSS feed, and output it using the PL/SQL Web Toolkit.
• Set up a live bookmark in Firefox based on the feed procedure.
• Create a details page to see details of each error (RSS feed item) when it is clicked.

Starting with the query, we wrap it in a function that returns a REF CURSOR:

We then create a procedure to format the REF CURSOR as RSS, and output it:

Then start Firefox and navigate to the procedure. This brings up Firefox's built-in RSS handler. Select "Live Bookmark" and subcribe to the feed.

Whenever you want to check for errors, just click the appropriate Live Bookmark folder in the Firefox toolbar, and the feed items will be displayed in a sub-menu:

If you click on one of the items, it will take you to the details page, which we have coded to retrieve the details of the error:

This is what the details page looks like in the browser:

Conclusion

We have seen that it is really easy to create your own RSS feed (all you have to do is write the query), and perhaps the example has given you some ideas and shown that RSS feeds can be used for more than news articles from online newspapers.

Note that if you have downloaded the Alexandria library for PL/SQL, you will find the code for the PLSQL_STATUS_WEB_PKG in the "demos" folder.