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...)