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.

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.

Display any XML as clickable tree using PL/SQL (and Apex)

I don't have too many good things to say about IE, but I do like how it displays XML files. Chrome's handling of XML files (or total lack of it) sucks. But IE (and Firefox) gives you a nice tree structure which allows you to click on the various nodes to expand or collapse them.

This is actually implemented as an XSL stylesheet that is "hardcoded" into IE, and it is possible to extract it.

Using the default IE stylesheet for XML in your own applications

Here's how we can use this XSL stylesheet in our own PL/SQL and/or Apex applications. Let's pretend we receive product information in XML format from a business partner. We parse the XML file and put the information into our product database. When something goes wrong it's helpful to look at the raw XML, so we store that in the database as well (typically in a CLOB or XMLTYPE column).

Using Apex, we build a web page to look at the logs, including the XML content. We'd like a nice clickable treeview instead of the raw XML.

Here's where our XSL stylesheet (extracted from IE) comes in. That stylesheet can be found in the PL/SQL package called XML_STYLESHEET_PKG which is part of Alexandria, the PL/SQL Utility Library. Download the library and install it, then continue with these instructions.

In Apex, create a blank page (I'll be using page 6) and put an HTML Region on it. Then put a Textarea item (called P6_XML_INPUT) in this region. This will simulate our XML product file, although in reality you would of course pull this from the database or a file on disk. Also add a button to submit the page (back to itself).

Next, add a PL/SQL Region to the page, and put the following as the region source:

Run the page, paste some (valid) XML into the text area, submit the page, and voila! The PL/SQL region should show the data nicely formatted as a clickable tree.

Another example

This "clickable tree" can also be useful to show data from regular tables or queries.

You can easily create an XML representation of a query by using a REF CURSOR and passing it to the XMLTYPE type.

For example, create a PL/SQL Region and use this as the source:

Run the page, and the output should look something like this:

Conclusion

Using some handy utility packages from the PL/SQL Utility Library and a few lines of PL/SQL code, you can display any XML document as a clickable tree.

PL/SQL Utility Library now has a proper name

A good library needs a good name, and what could be a better name for a library than (drum roll, please): Alexandria.

Alexandria library, Thoth gateway, anybody see a pattern here? :-)

PL/SQL Utility Library update

I've added several packages to the PL/SQL Utility Library archive, including string, date, SQL and XML libraries. There are also some new examples in the "demos" folder.

All right, now it's Friday and time for a beer :-)

Get the latest version of the PL/SQL Utility Library from this page.

Back to the Future

Come with me on a short trip through space and time, taking a look at the history of building (web sites).


The Stone Age, circa 1995

Although it involved a lot of manual labor, mighty Stonehenge and the pyramids were built with very simple tools (or by aliens, but let's deal with that topic another day).

Web development was pretty basic back then, but conceptually simple enough.

The Dark Ages, circa 1998

Some pretty amazing castles were built during this age, and they still stand today.

The basic tools had improved a lot, but they were still recognizable. Even an Neanderthal from the previous age could probably be effective with these tools.

The Age of Enlightenment, circa 2002

The gothic cathedrals from around this time look awesome, but were (and still are) inhabited by unpleasant religious fanatics.

At this point, great thinkers with lots of free time combined art and science to build magnificent works of beauty and elegance.

The Age of the Astronauts and the Race for Space (via the Clouds), circa 2008

A lot of very expensive spaceships were built during this age.

The great empires fought a cold, mostly ideological, war to see who could be the first to reach the moon.

Of course this all ended with a ka-boom and the eventual retirement of the space shuttle program.

A New Hope, 2011

Realizing that space tourism and new houses on Mars might be out of reach for most people in the foreseeable future, there is a renewed interest in more lightweight approaches to building and transportation. You might even call it eco-friendly.

Even the otherwise lethargic Microsoft jumps on the bandwagon, to the acclaim of professional developers.

"WebMatrix is full 180 from the highly abstracted cathedral that is ASP.NET. (...) WebMatrix is focused on simplicity and the "Get It Done" developer. Which, to me, is a massive undersell as we're all "Get it Done" developers."

"... you have to use raw SQL to query the database. This is going to turn off the Ivory Tower crowd who prefer to wade through XML Soup and tedious designers - but that's to their detriment. As I've always said - the best DSL I've ever seen for working with data is SQL."

That's cool. This is what web development looks like with WebMatrix:

Looks strangely familiar, doesn't it? A mix of HTML markup and code, and direct data access without any of that ORM stuff? Yes, that's right. For comparison, let's bring up that screenshot from the Dark Ages (1998) again:

Conclusions & Take-Aways

(Because there always needs to be a take-away.)

I am (definitely) not saying that "Frameworks are evil", or that "Progress is uncool". I'm just as lazy as any programmer, but I prefer the simplest thing that could possibly work.

I prefer frameworks that don't try to obscure that what we (most of us) are trying to do (most of the time) is to generate some HTML based on data from an SQL database.

Do we really need something like this

If something like this can do the job?

The golden path is probably somewhere in-between those two extremes. But keep in mind: Stonehenge has lasted for 5,000 years, while the space shuttle is being retired after less than 50 years.

PL/SQL Utility Library

Most programmers have a collection of utilities and code libraries that they re-use for several projects.

I've created the PL/SQL Utility Library page on Google Code to host some generic utilities that I've written myself, some that I've collected from elsewhere (credits and links can be found in the relevant source code), as well as links to useful PL/SQL libraries that are actively maintained elsewhere.

Check out the links and download the source code, there's a lot of different stuff here; from parsing CSV files, integrating with Google Maps and generating JSON, to zipping/unzipping files with PL/SQL.

I plan to add several more packages as soon as I get the code cleaned up.

Drop a comment below if you know of any other PL/SQL libraries or utilities that should be added to the list!