Dynamic RTF Documents Revisited

Yes, yes, this will be my last post on RTF for a while, but it's been a fun ride so far.

The (hi)story
In my posting RTF Documents in an Oracle Database I showed how to go about

• Creating RTF templates with substitution variables
• Generating RTF documents with substitution variables filled in run-time
• Data model and code to support the functionality in Oracle Apex
  

In the posting Converting RTF to PDF in an Oracle Database I,... Well the title is pretty self explanatory I guess :-)

Too simple!
Well, the first version of the code I showcased was a bit on the simple side, but it was just meant to give a hint of the possibilities. Anyway, I challenged myself to make a more complex RTF-component, so I set about wasting my time on coding a PL/SQL package that can return a RTF-table based on a query. With heavy use of dbms_sql the code can:

• Describe and store query columns
  This is used when defining the substitution variable. The columns are stored as rows in a table and have attributes such as title, text alignment, width.
• Running the query and return a RTF-table
  This is just a more advanced version of the simple substitution variables, but in stead of returning one column, one row as plain text, it now returns a RTF-table (but still as text).

In order for it to work, the query must have column aliases that can be resolved by dbms_sql.describe_columns into unique column names, and the columns must be of type varchar2 (yes, it is quite possible to return other data types, but in most cases text will suffice and I am lazy).

Sample Application
I have updated the sample application to show the function. I have included some images here to explain some of the elements. First a screen shot of the listing of all substitution variables in the uploaded template:


And a screen shot of the substitution variable attributes:
You can test it in my sample application, you can also download all necessary sample code there.

Now I soon leave for my summer holiday, five (!) weeks of leisure with my family :-)

Converting RTF to PDF in an Oracle Database

There are a number of commercial products out there which converts RTF to PDF, but there is also a free alternative called Ted. Actually, calling this a converter is a gross understatement, it is a fully fledged RTF editor which also can convert RTF to PDF.

I will describe how to install Ted, and how to use it with RTF-documents stored in the database. This combined with generating dynamic RTF-documents can give a rich document solution for your Oracle Apex applications.

This is a Linux/Unix specific solution. So if you are stuck with some other operating system, go do something about it ;-)

Downloading and Installing Ted
Ted runs on Unix/Linux, and you can download the software here. Ted is released under GNU Public License, and restrictions may apply.

The easiest way of installing Ted is to use RPM package file.

• Download RPM package file to /tmp
• Install RPM-package as user root (or system user with sufficient privileges)
  rpm -ivh .rpm

If you get an error stating that packages are missing, do not despair, simply download and install the package indicated (or get it from your installation CD). The installation is pretty much the same as described in the bullets above.

Preparing os for RTF conversion
Download Ted the rtf2pdf.sh script which converts rtf-files to pdf-files.

• Create an os-directory:
  mkdir /u01/app/ted
• Copy the Ted conversion script to the directory
  cp /u01/stage/ted/rtf2pdf.sh /u01/app/ted/.
• Grant execute privileges on the script
  chmod +x rtf2pdf.sh

I have done the above operations with user oracle of group oinstall, and in this demonstration user oracle will be used to execute os-operations. This is probably not the best production solution, but will serve for my demonstration purposes.

Creating directory in the Oracle database
Defining a directory in Oracle is quite easy:

• Connect as user SYS
• Create directory:
  create directory RTF2PDF as '/u01/app/ted';
• Grant privileges to RTF2PFDF
  grant all on directory RTF2PDF to <schema_user>

How to execute os-commands from the Oracle database
There are basicly three alternatives; dbms_scheduler, Java and C. The three alternatives and use of dbms_scheduler is described by Steven Feuerstein here. He also has a good description of the Java and C approaches.

dbms_scheduler is the superior alternative to me, it's easy to use, and as opposed to Java, is compatible with Oracle XE (even though there are some issues with this release). The only beef with dbms_scheduler are the implicit commits, I have not investigated when or why any further as I have barely scratched the surface of dbms_scheduler. I'll get around to it some rainy day.

What actually made me revisit dbms_scheduler was this recent blog post by Tobias Arnold, following feeds like orna.info and apexblogs.info has proven invaluable for a monkey like me :-)

Dr. Tim Hall has an excellent site called oracle-base.com with loads of examples and articles on the Oracle database. My dbms_scheduler code and setup is based on his article Scheduler Enhancements in Oracle Database 11g Release 1.

Preparing for use of dbms_scheduler
Before your schema user can take advantage of dbms_scheduler and execute host commands, there are a few steps that must be done:

• Connect as user sys
• Grant create job to schema user:
  grant create job to <schema_user>;
• Grant the ability to execute host commands/external jobs to schema user:
  grant create external job to <schema_user>
• Create credential (this is new in 11g, and saves a lot of work):

begin
-- basic credential.
dbms_scheduler.create_credential(
credential_name => 'LOCALORACLE',
username        => '',
password        => '');
end;

• Grant credential to schema user:
  grant execute on LOCALORACLE to <schema_user>

The os-user specified must have execution privileges on rtf2pdf.sh, and r+w on the directory where the script resides for this example.

PL/SQL code to invoke conversion
I have written a sample package for converting a rtf-document to a pdf-document. Sadly, I can't use Oracle's hosted environment on apex.oracle.com to showcase the functionality. I do not have the privileges to perform the required steps described above, and rightly so. Feel free to download and test it yourself. I have uploaded the package to my sample application at apex.oracle.com, you can download the sample code here.

The code is for demonstration purposes only, so my usual disclaimer apply: I take no responsibility what so ever for what might happen when you use it and there is no warranty of any kind expressed or implied. Phew... I still hope for world domination when you compile the package though ;-)

The package contains procedures/functions for file handling and invoking rtf2pdf.sh, but can easily be adapted for a more generic use.

Converting RTF to PDF
Lets say you have a table called RTF_DOCUMENTS that looks like this:

create table rtf_document
(
rtf_document_id   number not null,
file_name         varchar2 (255) not null,
rtf_file          clob,
pdf_file          blob,
constraint rtf_document_pk primary key (rtf_document_id)
);

And you want to create a procedure to convert the contents of column RTF_FILE to a pdf-document in column PDF_DOCUMENT, you can use the following procedure:

create or replace procedure convert_rtf_doc (
p_rtf_document_id    in       rtf_document.rtf_document_id%type
,  p_dir_name           in       varchar2 default 'RTF2PDF'
) is
cursor c_doc
is
select rtdo.rtf_document_id
    , rtdo.file_name
    , rtdo.rtf_file
    , rtdo.pdf_file
from rtf_document rtdo
where rtdo.rtf_document_id = p_rtf_document_id;
l_pdf_file        blob;
begin
for r_doc in c_doc
loop
l_pdf_file := rtf2pdf.rtf2pdf(p_rtf_file_name => r_doc.file_name
                            ,p_rtf_file => r_doc.rtf_file
                            ,p_dir_name => p_dir_name);
update rtf_document rtdo
  set rtdo.pdf_file = l_pdf_file
where rtdo.rtf_document_id = p_rtf_document_id;
end loop;
end;
/

Beware of the implicit commit by dbms_scheduler! A select for update in c_doc cursor would result in "fetch out of sequence"-error.

Alternatives to Ted
There are always alternatives, this is not different. There are some Open Source projects out there, and this guy (that's sexist, but I still assume cfSearching is a he) has done some research on the topic. His angle was different from mine though, and he went the ooxml-way, which is not a stupid idea.

Open Office could be an alternative, and has a more active community surrounding it. The CLI surrounding the OO-libraries in server mode will probably do the job.

In conclusion
In all honesty, I have not yet investigated to which extent the rendering of pdf-files is correct based on the more complex elements of rtf. This, of course, I should have done first. For me the journey is the most fun when (as in this case) there are no expectations attached to the end solution.

Tightening the PL/SQl-code referenced here for production goes without saying!

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