Thursday, July 9, 2009

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


  1. Hi! the sample code in your sample application doesn't include the RTF-Table enhancements you made, does it?

  2. @Paul:
    Err... Yes, it does! After I corrected the link, of course ;-) A bit of vacation mode there...

    Thank you for the heads up!

  3. This comment has been removed by the author.

  4. Hello, I have successfully tested your application for RTF Templates. However, when I click on the link "Download the simple data model and package here" I get "Access denied." Could you please tell me why is that? I've logged in as welcome.


  5. @Jelena

    Thank you, fixed the link. I hope you find it useful :-)

    Note to self: Test your code when not logged in as a developer!

  6. Hi and thank you, when I want to edit substitution variable, it doesnt work.
    ErroColumn SHOW_TABLE_HEADER not found in table RTF_SUBST_SQL

    Thank you in advance.

  7. Can you explain how button gettemplate exactly works? You wrote "GetTemplate is url-redirect to: #OWNER#.rtf_p.get_rtf?p_rtf_template_id=&P3_RTF_TEMPLATE_ID.&p_only_template=N" but it dosnt work... Can you help ?

  8. Thanks for the inspiration.... I have used your code as the basis for my requirement, but slightly enhanced it. I've written a blog about this (given you credit) see, also the updated code has been put up on here
    Once again, thanks for the inspiration and the sharing. Hopefully someone will find the additions of interest and share back.

  9. @Richard

    Great work! I have notified the owner of the Alexandria PL/SQL Utility Library of your posts/code :-)

  10. Excellent package. Very useful. Thanks a lot.