I recently worked on a project that involved an Oracle APEX application. If you aren’t familiar with APEX, it’s a database-centric rapid application development product that is included with the Oracle 10g and 11g databases. Using APEX you can generate web-based applications, interact with database tables/views, leverage PL/SQL stored procedures and even integrate it with Oracle BI Publisher. BI Publisher is an easy-to-use report generation product.
One of our requirements was to generate reports from the application in a PDF format. I looked at several options and decided for the moment that Oracle BI Publisher was the fastest and simplest solution. Fortunately the client was happy with that as well.
What I found out was that generating a report from APEX and having it printed as a PDF via BI Publisher is a very elegant solution. Beyond installing BI Publisher on a server, the following steps were what I need to do. BTW, we installed BI Publisher in both the windows and Linux environments with no issues.
We had an existing application screen and wanted to generate the PDF using the same underlying SQL queries. First I logged into the APEX application, went to SHARED COMPONENTS and defined multiple SQL select statements using the REPORT QUERIES option. I was able to reference some existing session variables to customize the SELECT statements. In APEX I was able to define a REPORT LAYOUT template under the SHARED COMPONENTS. Then I downloaded the BI Publisher plug-in for Microsoft Word 2007 and created a report template. There’s more detail in that process than I can cover here, but it didn’t take long for me to learn how to create more and more complex templates.
I was then able to upload the template to APEX (and download it later for editing). Since the templates are MS Word documents, it’s pretty straight-forward.
Back in APEX I modified the logic on one of the application web pages to add a button. This button was linked to a BRANCH statement which called BI Publisher and generated the PDF Report. Within a few seconds my browser had a download of the PDF file which I could open and browse!
Within several weeks we had created about a dozen reports, each with 5 or 6 SQL queries. Some executed directly out APEX and others were scheduled to run directly from BI Publisher on an automated schedule. If you want to learn more about the link between APEX and BI Publisher, here are some examples. Happy Report Writing!
Defining a PDF Report in APEX