Sunday, February 3, 2013

Recently I assisted a colleague (with much help from Marc Sewtz) that wanted to send an
Excel document attached to an email showing new customers on a scheduled basis.
Seemed like a reasonable request since APEX_MAIL has had attachment support
since 4.1 (thanks to Joel) and APEX_UTIL.GET_PRINT_DOCUMENT
can get a BiPublisher report as a BLOB. In assisting my co-worker I learned at
least three nuggets about accomplishing this feat.

You
cannot use GET_PRINT_DOCUMENT Signature 2 or 3 which accept the name of the
report defined already in an application in a scheduler job. You must use
either Signature 1 or 4 that allows you to pass in both the report data and the
report layout.

DBMS_XMLGEN
which allows you to generate XML based on query escapes special characters using
& notation. The BiPublisher convert servlet will choke if it receives
data that contains an &. You must do your own escaping in the query itself
to make sure DBMS_XMLGEN won’t produce a document with an ampersand in it.

In
order to get an Excel document using GET_PRINT_DOCUMENT Signature 4, you must
use ‘Excel’ for the p_document_format parameter, not ‘xls’ as the documentation
suggests.

Armed with these three nuggets, let’s use the Sample
Database Application to schedule an Excel document based on the orders table to
be emailed to us.

Since I have BiPublisher available to me I am going to use
the BiPublisher
Word plugin to create a rtf template for my report layout. I find it much
easier to use than trying to create an xsl-fo template. To use this plugin, I get
the XML data for my query by creating a Report Query in shared components and
then click the Download button.

Now I load the data into the BiPublisher Word plugin and run
the table wizard to create a simple table. The result looks like the following
picture.

I save the document in rtf format, and then open the
document using a text editor. I build a local string up in my procedure
SEND_ORDERS described and listed next.

SEND_ORDERS uses DBMS_XMLGEN to create the XML data that is
passed into GET_PRINT_DOCUMENT. The report layout that is passed in is built up
in the procedure using the text that makes up the rtf document. Once I get the
blob back from GET_PRINT_DOCUMENT, I simply attach it to an email using
APEX_MAIL.