Looking online there doesn't seem to be much in the way of options for being able to use a single tool to produce a report that includes rows of data from an Oracle table along with CLOB or BLOB data in a single place.
However one idea came to mind when I was talking through options with colleagues. We already have the facility within the Apex system for a user to email themselves a copy of a specific record with any linked data. If it was possible to add the Clob attachments to this email then it would give a way to be able to print them.
Within Microsoft Outlook there is the ability to print an email and one slightly hidden option is to also print all attachments. The main print screen is below.
|Printing Clob Attachments from Oracle Database|
|How to print clobs in report with Oracle data|
You will see a message asking if you want to open the program that processes each attachment type - answer Ok and the print is generated. You don't even see the program opening, the print is just generated. The print jobs will be sent directly to the printer and be held for printing by the user or print immediately depending on your print server setup.
To generate the email from the Oracle database we're using the Apex_Mail PROCEDURE which is part of the standard Oracle UTL_SMTP toolkit that has lots of documentation online.
You can use the
APEX_MAILpackage to send an email from an Oracle Application Express application. This package is built on top of the Oracle supplied
UTL_SMTPpackage. Because of this dependence, the
UTL_SMTPpackage must be installed and functioning to use APEX_MAIL.
APEX_MAILcontains three procedures. Use
APEX_MAIL.SENDto send an outbound email message from your application. Use
APEX_MAIL.PUSH_QUEUEto deliver mail messages stored in
APEX_MAIL.ADD_ATTACHMENTto send an outbound email message from your application as an attachment.
So the process to print a report from Oracle that includes table data as well as PDF, XLS, DOC or other attachments stored as Clobs is below:
- Generate email from Oracle Apex
- Add attachments to email
- Send email
- Use Outlook to print email and all attachments
An improvement/variation on this would be to include the Oracle table data in a PDF as an attachment to the email rather than being in the email body. This would allow you to control the layout and format of the table data more closely and avoid the email headers being shown.
If you have any questions, comment or feedback please post below