RTF Documents in an Oracle Database

In this article I will outline how to use RTF templates to create documents with dynamic content.

Why RTF?
I'm lazy. RTF templates can easily be made of any user with some knowledge of MS Word, I don't have to lift a finger. Most users are comfortable with Word, and like the "freedom" it gives.

It's a proprietary standard, yes, but it's also documented, that helps... It comes in different versions, with subtle differences.

Is all rosy? No, there are pitfalls. In order to create templates with reasonable content and size, a structured approach i necessary. In a public application, I would not recommend RTF as the preferred format, but for an internal application in a controlled environment the approach can be quite effective.

In the past, I have used RTF in various projects, and found that it is very easy to develop and maintain fairly complex reports.

Creating a template
This example assumes working in M$ Word. First of all, create a document layout (with content) that you would like to use. When all is done, copy all content, click "Create new document" and choose "Empty document", paste all the content from the original document in one go and save as RTF.

You now have a clean RTF document, if you want to, you can examine the result in your text-editor of choice. Some elements are more cryptic than others, but you should get the general idea. For a more thorough investigation (depending on your temper), you can create RTFs from scratch. There are a number of sources online, check out the three tutorials located here, or this cookbook. If you are a bit more lazy (but still won't use MS Word), there are a number of tools that can do the job for you, including OpenOffice. Wiki is a good starting point, or use your favourite internet search engine...

Identifying dynamic elements
Some parts of the document are clearly candidates for dynamic substitution, identify these and tag them with descriptive names, I use §¤mySubstitutionVariable¤§ with "§¤" and "¤§" surrounding a descriptive name.

The substitution variable can contain any RTF-element, including tables and graphics. I concentrate on simple text strings in this article.

When you are satisfied with your substitutions, create a RTF template as described above.

Simple test application
Next you want to put the document in the database. I have a simple data model to support both RTF-templates and the substitution variables. It consists of three tables; one containing the RTF-template, one containing substitution variables and the last to bind them together.

I have created a package called rtf_p which pretty much handles all the fun, you can see it in action and download the source database objects here.

Remember that this is for demonstration purposes only, use it as you will, but I take no responsibility for what might happen when you do (your desktop melts, I achieve world domination, etc.).

Ready for production, or...?
So, the algorithm did not satisfy you? Well, it was not supposed to :-) Next you would want to implement some common function basis; which customer am I working with, what order number, etc. Using Apex collections is an easy way of achieving this. Create a collection (perhaps called "report"), and create rows of common use like "CUST_ID" or whatever you need and populate it before creating the finished document. Use the collection in your queries. If there are some substitution variables that are "always" included in a document; fill them with one query to improve performance.

The download part of rtf_p is not protected in my example (quite the contrary in fact), this you should rectify in a production environment; be sure the user is authenticated.

Depending on the need (you never know which way the customer jumps...), you might implement functionality for storing the finished document, or substituting more advanced RTF objects (like tables). You should try hard (well, harder than I did) to assist the users for filling substitution variables and such in the application (or: do you think it's fun to write the advanced "I wan't the last name first!"-type of queries?).

As always; the sky is the limit! (...and time, money, social life, quality of coffee, mood, etc...)

Producing documents with Oracle Apex

While in a start-up of a project, I delved into the whole document administration and generation world from an Apex perspective. I'm not talking about the typical accounting reports, but more like formal letters and such to send to customers.

My requirements list goes like this:

• Produce nice looking documents with some advanced elements (graphics, fonts, tables, header/footer, etc.), preferably in PDF
  
• Easy to develop and maintain, both static and dynamic content
  
• Low or no license cost

First off, Oracle Apex does not produce PDFs on its own. If you want to use the native Apex functionality for producing reports in PDF, you have to connect it to a print server. You can however generate PDFs with tools that does not integrate as well with Apex, and the list of possible solutions increases.

Here are the alternatives I dug out on short notice. Now, this is by no means an exhausting list, so feel free to guide me to other solutions.

Apache FOP: Open source XSL:FO implementation. Pure Java solution, can be used "as-is" with command line interface, or embedded in a servlet running in a Java container (eg. Tomcat). Requires skills of XSL:FO (or a commercial tool that produces this format) to customize output. Apex ships with pre-configured implementation for running in OC4J, so it's very easy to integrate.

Apache Cocoon: Spring-based open source Java framework that can do a lot more than producing PDF. If I understood correctly, it uses Apache fop libraries to generate PDF. Has to run in a Java container (eg. Tomcat). Very easy to integrate with Apex, Carl's (may he rest in peace) example proves the point. Requires skills of XSL:FO to customize output.

PLPDF: A pure PL/SQL alternative, and thus requires PL/SQL skills to define documents. I have tested the api, and the amount of code required to produce a document is not overwhelming. I really miss a GUI for the tool though. Cheap license (per database). Keeping it in the database appeals to me, and it comes recommended from some prominent members of the Apex community. Easy (but not without programming) integration with Apex.

JasperReports: Open source Java framework for report generation. Can be embedded in a servlet running in a Java container (yes, yes, Tomcat again...). Requires adaption of both JR and Apex for integration. There are some examples of integrating the two on forums.oracle.com. As opposed to the Apache fop-based tools which integrate quite easely into Apex, both the queries and layout have to be configured in JR. Customizing and layout of reports are supposedly quite easy with iReport. I have seen som fairly exotic documents generated with this tool, so advanced layout is no show-stopper. JR will require skills in a separate product from Apex. ReportChunker (based on JasperReports) comes pre-packed as a war, this might be an easy way of integrating, but I have no hands-on experience with it.

Oracle BI Publisher: King of document design and Apex integration. If you already have a license for Oracle EBS/BI Publisher, or are going to produce painfully many advanced reports (and have an arm and a leg to spare), this is the way to go. It is really, really not cheap, but depending on how many hours you plan spending on developing documents and customizing them, I imagine there must be a break-even limit somewhere. I imagine... Requires skills in a separate product from Apex.

Any more out there? Of course there are, but as the project headed for RTF, I did not dig any further.

The monkey-side of me is still baffled by the fact that there still are no point-and-click solution for this, coding is so much, well, work... And quite a bit of fun ;-)

Monkey see, monkey do

So, what could possibly possess a "normal" person to start a blog at this time? Everyone knows there are far more blogs than readers out there.

I think the reason is simply that I am a monkey! Well, at least a biped of sorts. You have to hand it to the monkeys, they have no restrictions what so ever about "copying" other peoples (or bipeds, whatever) work. In that sense, I am a monkey (probably more than just that sense, but I won't go there now). I sift through the internet to find an example matching my current task. I am lazy, I am good at searching the net, and I am good at adopting code. At least good enough to make a living of it.

And the blog? It's time for this monkey to give something back. Even if people don't read blogs like they used to (everybody busy writing their own and all), the text becomes public and just a few keystrokes away in your favorite search engine. Even my own work for myself, which is not such a stupid idea for someone like me.

What really prompted me to do this was this thing called Oracle Application Express (Apex). Working with the product (which is great!) proved significantly more easy than I am used to. Why? The community surrounding the product! Let's face it, Apex does not get the support from sales that it deserves, so the community surrounding it has risen to the occasion. This is what we expect to see from the Java community, but seldom see in the more closed world of Oracle (even if that has changed these past few years). I like the "make each other better"-approach!

So, this blog will ramble on about this Apex thingy and technology surrounding it.

Think you could learn something from a monkey? Happy reading!