Sunday, May 24, 2009

Producing documents with Oracle Apex

While in a start-up of a project, I delved into the whole document administration and generation world from an Apex perspective. I'm not talking about the typical accounting reports, but more like formal letters and such to send to customers.

My requirements list goes like this:
  • Produce nice looking documents with some advanced elements (graphics, fonts, tables, header/footer, etc.), preferably in PDF
  • Easy to develop and maintain, both static and dynamic content
  • Low or no license cost
First off, Oracle Apex does not produce PDFs on its own. If you want to use the native Apex functionality for producing reports in PDF, you have to connect it to a print server. You can however generate PDFs with tools that does not integrate as well with Apex, and the list of possible solutions increases.

Here are the alternatives I dug out on short notice. Now, this is by no means an exhausting list, so feel free to guide me to other solutions.

Apache FOP: Open source XSL:FO implementation. Pure Java solution, can be used "as-is" with command line interface, or embedded in a servlet running in a Java container (eg. Tomcat). Requires skills of XSL:FO (or a commercial tool that produces this format) to customize output. Apex ships with pre-configured implementation for running in OC4J, so it's very easy to integrate.

Apache Cocoon: Spring-based open source Java framework that can do a lot more than producing PDF. If I understood correctly, it uses Apache fop libraries to generate PDF. Has to run in a Java container (eg. Tomcat). Very easy to integrate with Apex, Carl's (may he rest in peace) example proves the point. Requires skills of XSL:FO to customize output.

PLPDF: A pure PL/SQL alternative, and thus requires PL/SQL skills to define documents. I have tested the api, and the amount of code required to produce a document is not overwhelming. I really miss a GUI for the tool though. Cheap license (per database). Keeping it in the database appeals to me, and it comes recommended from some prominent members of the Apex community. Easy (but not without programming) integration with Apex.

JasperReports: Open source Java framework for report generation. Can be embedded in a servlet running in a Java container (yes, yes, Tomcat again...). Requires adaption of both JR and Apex for integration. There are some examples of integrating the two on forums.oracle.com. As opposed to the Apache fop-based tools which integrate quite easely into Apex, both the queries and layout have to be configured in JR. Customizing and layout of reports are supposedly quite easy with iReport. I have seen som fairly exotic documents generated with this tool, so advanced layout is no show-stopper. JR will require skills in a separate product from Apex. ReportChunker (based on JasperReports) comes pre-packed as a war, this might be an easy way of integrating, but I have no hands-on experience with it.

Oracle BI Publisher: King of document design and Apex integration. If you already have a license for Oracle EBS/BI Publisher, or are going to produce painfully many advanced reports (and have an arm and a leg to spare), this is the way to go. It is really, really not cheap, but depending on how many hours you plan spending on developing documents and customizing them, I imagine there must be a break-even limit somewhere. I imagine... Requires skills in a separate product from Apex.

Any more out there? Of course there are, but as the project headed for RTF, I did not dig any further.

The monkey-side of me is still baffled by the fact that there still are no point-and-click solution for this, coding is so much, well, work... And quite a bit of fun ;-)

4 comments:

  1. How can we use Spring Framework with Oracle Apex? Lets say I want to develop some reporting capability in APEX, can i use spring framework in any piece of the development cycle?

    ReplyDelete
  2. @himanshu

    The short: No! ;-)

    The slightly longer: I cannot say, based on the details you have provided!

    As long as it gets at this stage: Apex resides in the database, a typical Spring application resides in the middleware layer. If you want to develop some part of an apex application "outside", you have a number of challenges ahead.

    Before I elaborate the answer, riddle me this: Why would you want to do that?

    And some details of exactly what you wish to achieve would be nice...

    ReplyDelete
  3. I was exploring options if we can integrate spring framework with APEX. I agree with you that there will number of challenges but on the other side, there will be a solution as well since APEX alone is not enough to provide all kind of solution. The reason we're using APEX in our one of the projects is its' widget based simple developing tool provided with Oracle suite/products at our organization but i don't think it's fully capable of producing what we want in the future when we'll some requirements.

    ReplyDelete
  4. I integrated Oracle Reports a few years ago. I did it the same way as the integration of Jasper Reports was done (utl_http).

    Learco

    ReplyDelete