Posts Tagged ‘APEX’


Oracle APEX and BI Publisher

August 9th, 2010 by Robert McMillen • 2 Comments »

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

Tags: · · · · ·

2 Comments »

 

Applications Exchange (APEX) Grows in Popularity

September 21st, 2008 by Robert McMillen • 1 Comment »

If you haven’t noticed, there’s a growing interest in using the former HTML DB product now known as APEX (Applications Exchange).  APEX provides a declarative way to develop web-based applications without requiring significant knowledge about Javascript or HTML.  It does require that users are comfortable with SQL, and, to take advantage of advanced features, some knowledge about PL/SQL.

APEX utilizes either the Oracle 10g or 11i database to store all of the configuration. Depending on your setup it may or may not use the Apache Server.  Under Oracle 11g, one can use the XMLDB Gateway and forgo the use of the external Web Server (Apache).

Many companies are using APEX to create departmental apps that access information in the E-Business Suite and a new book, Pro Oracle Application Express, has just been released by John Scott and Scott Spendolini that documents advanced features.

Oracle has a full-featured website that provides lots of information about APEX.  It includes the download links, tutorials, and lots of examples.  To simplify it even further, they provide a hosted website where you can jump directly into exploring the features of APEX.  Check it out at http://apex.oracle.com

I’ve experimented with APEX and find it very interesting.  At OOW there are a number of sessions about APEX that have been well-attended.  I attended one on how easy it is to add globablization features to APEX.  The session showed a simple APEX application that had been developed in English which the presenter updated to work in German and French.

Last month while I was at the NCOAUG Training Days, I dropped in on a session called “Flex Your ApEx: Implementing Oracle E-Business Suite Flexfields in Application Express”, by Shane Bentz, InterVarsity Christian Fellowship/USA. His organization had set up their APEX application to use the FLEX Field logic within the E-Business Suite.  That was very impressive and speaks to the capabilities of APEX.

Lastly, there is a process for converting older Oracle Forms applications to web forms using APEX.  I haven’t used it so I can’t vouch for its capabilities, but it is supported by Oracle.

More later on APEX…

Tags: · · ·

1 Comment »