Apex Listener Resource Templates

An early adopter version 1.1 of the Apex Listener was released a while back.

Along with the software download there is a developer's guide that describes a new feature of the listener, called Resource Templates. From the guide:

Oracle Application Express Listener enables data stored in an Oracle Database to be exposed via RESTful Application Programming Interfaces (APIs).

The guide continues:

RESTful APIs are created by configuring 'Resource Templates'. A Resource Template is a configuration file that binds a set of URIs to a SQL query or anonymous PL/SQL block. The set of URIs is identified by a 'URI Template', a simple syntax for describing URIs e.g. people/{userid}. The URI Template may contain zero or more parameters ( e.g. {userid}) which along with the HTTP Headers sent with a HTTP request can be bound to parameters in the SQL query or anonymous PL/SQL block. 

This is a very interesting feature that can be used outside of the Apex framework (ie in a barebones PL/SQL web application). Personally, I would prefer to be able to configure the resource templates somewhere in the database (in a special table and/or a predefined PL/SQL API), rather than messing around with the listener configuration, but the concept itself is a good one.

Anyway, it's an interesting read, so do take a look if you haven't already done so.

Working with Office 2007 (OOXML) files using PL/SQL

Starting with Office 2007, Microsoft switched to an XML-based format called Office Open XML (OOXML).

There has been some debate as to how "open" this format really is, given that the specs run to around 7,000 pages (!).

Be that as it may, it's a fact of life that a lot of people use Microsoft's Office suite, and that means we have to deal with this new format in a lot of situations.

The OOXML format is, as it turns out, not so difficult to deal with. The main concept is that an Office document, whether it is a Word document (.docx), Excel spreadsheet (.xlsx) or Powerpoint presentation (.pptx), is actually a compressed (.zip) file that contains a number of XML documents (as well as any image files the user has included in the document).

So to work with OOXML files, we need to be able to zip and unzip files, and to parse and generate XML. Oracle (and PL/SQL) has had good support for XML for a number of years, but (even though there is a UTL_COMPRESS package in the database) there is no built-in zip/unzip support. Of course you could load some Java classes into the database to do it, but dealing with the Java stuff is always a bit of a hassle. But some time ago the good gentleman Anton Scheffer published a PL/SQL implementation based on UTL_COMPRESS that supports zipping and unzipping.

Based on this I have written a package for working with OOXML documents. It's called OOXML_UTIL_PKG and you can download it as part of (you guessed it) the Alexandria utility library for PL/SQL.

Let's see what this package allows us to do.


Get document properties from a Word (docx) file

First we fire up Word and create a test document:

By the way, you can read and write the new OOXML formats using an older version of Office (as I do in the screenshot above), by downloading the Microsoft Office Compatibility Pack from Microsoft.

After saving the document, we can then extract the document properties using the GET_DOCX_PROPERTIES function, which returns a custom record type called T_DOCX_PROPERTIES.

Extract plain text from a Word (docx) file

Using our test document again, we can extract the plain text of the document using the GET_DOCX_PLAINTEXT function, which returns a CLOB.

This is of course very useful if you want to search and/or index (just) the text of a document, or otherwise work with the content.

Get document properties from an Excel (xlsx) file

Let's first create an Excel test file (again, using Excel 2003 but saving in Excel 2007 format):

(This has to be one of the lamest spreadsheets of all time, but it will do fine as an example. It has some text, some numbers, and a formula.)

Similar to the Word document, we can now use the GET_XLSX_PROPERTIES function, which returns a custom record type called T_XLSX_PROPERTIES.

You'll notice that Word documents and Excel spreadsheets have slightly different properties.

Extract a cell value from an Excel (xlsx) file

The GET_XLSX_CELL_VALUE function allows us to retrieve a single value from a named cell in a specific worksheet, like this:

Technical Detail: In the XLSX format, strings (as opposed to numbers) are not stored in the actual cell where they are entered, but rather in a "shared strings" section. The cell just contains a reference back to this "shared strings" section. The GET_XLSX_CELL_VALUE function handles this for you, so you don't have to worry about that.


Extract multiple cell values from an Excel (xlsx) file

Since the function that extracts a single value from a spreadsheet must open the file, unzip it, and parse the XML content every time you call that function, there is another function (GET_XLSX_CELL_VALUES, notice the plural) that allows you to retrieve multiple values in one call. In other words, the file is unzipped and the contents parsed as XML just once, which is obviously more efficient.

Simply specify the names of multiple cells using an array of strings:

Write contents into OOXML file using PL/SQL

Since the contents of OOXML files are XML files, you can manipulate the existing content, or generate new content, and then save it back to the zip file that contains your document.

The following demonstrates one approach; it uses a Powerpoint file, but this technique will also work with Word and Excel files.

We create a Powerpoint 2007 file (.pptx) and put in some tags that we want to replace via code. In other words, this becomes a template that we can fill with dynamic values from the database.

The GET_FILE_FROM_TEMPLATE function takes a template file as input, and two string arrays: The tag names and actual values to replace the tags with. It unzips the file, performs the substitutions, writes back the file to the zip archive, and returns the file, which you can then save back to disk (or, more likely, store in the database or send to a web browser).

The code is trivial:

Here is the result when opening the output file:

So the next time you do a presentation, you could actually update your Powerpoint slides with the latest sales figures (or whatever) from within SQL*Plus...


Conclusion

Working with Office 2007 (OOXML) files from PL/SQL is easy and opens up many possibilities, both for extracting information from documents and storing them in the database, as well as generating or modifying OOXML files in the database server.

Generating test data using PL/SQL

Here's a fun little PL/SQL package, part of the Alexandria library for PL/SQL, that you can use to generate semi-random test data.

The package is called RANDOM_UTIL_PKG. At the time of writing it contains the following functions:

• GET_AMOUNT
• GET_BUSINESS_CONCEPT
• GET_BUZZWORD
• GET_DATE
• GET_EMAIL_ADDRESS
• GET_ERROR_MESSAGE
• GET_FILE_NAME
• GET_FILE_TYPE
• GET_INTEGER
• GET_MIME_TYPE
• GET_PASSWORD
• GET_PERSON_NAME
• GET_TEXT
• GET_VALUE
• GET_WAIT_MESSAGE

These should all be fairly self-explanatory, but let's look at some examples of how you could use these to fill your database with "realistic" test data.


A list of users

An email archive

A list of orders (and order items)

A list of uploaded files (or files in a folder)

A list of errors (an error log)

A list of business concepts or strategies

This is for those times when a struggling company needs to re-focus its strategy and define a new vision. No need to hire expensive management consultants, just fire up SQL*Plus and execute the following query:

Here are some of my favorites from the concepts I generated just now:

• Quickly innovate sticky products with resource-maximizing portals.
• Credibly plagiarize robust opportunities using timely architecture.
• Dynamically reinvent dynamic leadership for end-to-end web services.
• Appropriately empower front-end human capital via error-free web services.
• Globally plagiarize stand-alone ideas after next-generation alignment.

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? :-)