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

XSL Transformation in Oracle Apex

I recently explored some of the XML capabilities of the Oracle database, and I must say it has matured quite a bit since I last looked at it (Oracle 8i).

Using XMLTRANSFORM-function
It is a powerful function which acts as a full XSLT processor. The syntax is simple:

xmltransform([xml_document],[xsl_document])

Both input parameters must be of type xmltype. By having XML/XSL stored as xmltype, or casting it to xmltype with the corresponding function, you in effect ensure the structure of the xml-documents are intact.

Read the documentation for more information. I know it can be boring, but also quite giving (from time to time, at least...). While you are at it, why not click "Previous" or "Next" on the documentation pages to view the other XML-functions?

Sample application
Well, "application" is just a bit overstated, what I do have is an Oracle Apex application page. I have created a quick and easy front end to the XSL transformation. Just paste your XML and XSL into the corresponding text areas, and click button Transform to view the result.

If the transformation fails, the error message will be returned in the display area.

Try out the transformation, and get a detailed description of the code involved in my sample application.