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