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