Oracle APEX Listener - support for Apache Tomcat

The Oracle APEX listener is one possible gateway to run Oracle Application Express besides the Oracle http server (OHS), the embedded pl/sql gateway (which is the http server included in the Oracle database as part of the XML DB) and other open source alternatives.

Being implemented as a J2EE application it needs a servlet container to be deployed in. Finally, due to popular customer demands, the Apache Tomcat servlet container is (again) supported. Many customers are using it already for quite a while and I personally had hardly any issues with that.

When you look at the APEX listener 2.0 installation guide you will find a section on deploying to tomcat. There they talk about Apache Tomcat 7 but it seems like version 6 is also supported, I have found the information on Kris Rices blog, he is the development manager of the APEX listener.

This is not really NEW news ;), but I personally had not seen this information before.

Cheers and enjoy,
~Dietmar.

Upgrading the JasperReports libraries to 5.2.0

Would you like to upgrade your existing JasperReportsIntegration with the 5.2.0 libraries of JasperReports?

As of now this already seems to be not the most current version, JasperReports has already moved on to 5.5.0. But since I have already created the 5.2.0 package a while ago, I might just post the instructions here. The 5.5.0 version will follow.

Here you go ...

This is an updated version of the original instructions found here: http://daust.blogspot.de/2013/01/upgrading-jasperreports-libraries-to-501.html

Step 1: Download the libary files for 5.2.0

You can download the files here:

 

Step 2: Shutdown the Apache Tomcat J2EE server

 

Step 3:  Remove the existing JasperReportsLibraries from your existing installation

Typically, after you have installed your previous version of the JasperReportsIntegration toolkit on your Apache Tomcat J2EE server, the files will be located in the directory $CATALINA_HOME/webapps/JasperReportsIntegration/WEB-INF/lib, for example version 4.7.0 of JasperReports, where $CATALINA_HOME represents the path to your installation of Tomcat.

Then you would have to remove these libraries first. In this directory you should find two files for removal: _jasper-reports-delete-libs-4.7.0.sh and _jasper-reports-delete-libs-4.7.0.cmd, for *nix or Windows respectively. For *nix systems you would have to make it executable, though, e.g.: chmod u+x _jasper-reports-delete-libs-4.7.0.sh . Then you can call it and it will remove all files for version 4.7.0. But it will NOT remove the file for the JasperReportsIntegration and all other libraries which YOU might have placed there deliberately.

You can always find the required removal scripts here: http://www.opal-consulting.de/downloads/free_tools/JasperReportsLibraries/ .

Whenever I release another package, the removal scripts for that package will be shipped as well.

Step 4: Install the new 5.2.0 libraries

Now you can just copy the new libraries from JasperReportsLibraries-5.2.0.zip into $CATALINA_HOME/webapps/JasperReportsIntegration/WEB-INF/lib.

 

Step 5: Start the Apache Tomcat J2EE server again

Now you system should be upgraded to the most current JasperReports 5.2.0 !

Just drop me a note when you need updated libraries for a later JasperReports version, etc. I have scripts in place to create a new package of the libraries.

Here you can find the notes from my upgrade (4.5.0 => 4.8.0) on Linux to illustrate the process, I hope it makes sense:

**
** download the libraries from:
**  http://www.opal-consulting.de/downloads/free_tools/JasperReportsLibraries/4.8.0/JasperReportsLibraries-4.8.0.zip
** to /home/jasper/JasperReportsLibraries

cd /home/jasper
mkdir JasperReportsLibraries

** unzip them
cd JasperReportsLibraries
unzip JasperReportsLibraries-4.8.0.zip -d JasperReportsLibraries-4.8.0

** stop tomcat server
/home/jasper/tomcat/bin/shutdown.sh

** remove libraries of current jasper reports release
cd /home/jasper/tomcat/webapps/JasperReportsIntegration/WEB-INF/lib
chmod +x _jasper-reports-delete-libs-4.5.0.sh
dos2unix _jasper-reports-delete-libs-4.5.0.sh

./_jasper-reports-delete-libs-4.5.0.sh

** copy libraries of the new release to the WEB-INF/lib directory
cp /home/jasper/JasperReportsLibraries/JasperReportsLibraries-4.8.0/* /home/jasper/tomcat/webapps/JasperReportsIntegration/WEB-INF/lib

** restart tomcat
/home/jasper/tomcat/bin/startup.sh

Cheers,
~Dietmar.

Oracle APEX 4.2 New Features und Tipps aus der Praxis - Neuer Kurs am 11.11.2013

In diesem November werden Denes Kubicek und ich wieder einen Kurs zu Oracle APEX in Bensheim abhalten:

Oracle APEX 4.2 New Features und Tipps aus der Praxis

Wir werden diesmal viel über den APEX Listener, Einsatz von jQuery und über Restful Web Services reden und natürlich, praktisch üben.

Darüber hinaus gehen wir auch das Thema Mobile Development an und geben einen Überlick über die 100+ neuen Features, die sich auch in diesem Release wieder geändert haben. Mir gefällt besonders, dass durch diese vielen kleinen Verbesserungen die Transparenz in der Applikationsentwicklung nochmals erhöht wurde.

Gerade die Packaged Applications liefern viele gute Beispiele, wie häufig verwendete Design Patterns in der Praxis moderner umgesetzt werden können.

Dieser Link informiert Sie über alle weiteren Details zum Kurs.

Viele Grüße,
~Dietmar.

DOAG 2013 Development in Bonn am 19. Juni

Am 19.Juni 2013 findet die nächste DOAG Development Konferenz in Bonn statt, diesmal mit dem Thema:

„Agile and Beyond – Projektmanagement in der Oracle-Softwareentwicklung“

Das Motto der zweiten Auflage der Community Konferenz steht fest: Die effektive Durchführung von Softwareprojekten steht thematisch im Zentrum der Konferenz für Entwickler, Softwarearchitekten und Projektleiter.

Gerade Oracle APEX eignet sich hervorragend für die agile Durchführung von Softwareprojekten, ich habe damit seit Jahren hervorragende Ergebnisse erzielt. Mit Oracle APEX kann man in extrem kurzen Iterationszyklen von 1-4 Wochen sehr beachtliche Funktionalitäten implementieren. Gerade die Lieferung von Funktionalitäten in kurzen Abständen hilft, das Vertrauen der Fachseiten zu gewinnen. Dennoch gibt es einige Fallstricke zu beachten, damit auch alles rund läuft ;).

Weitere Informationen zur Konferenz finden Sie hier.

Bitte beachten: der Frühbucher-Rabatt endet heute!

Viele Grüße,
~Dietmar. 

How to locate all interactive reports in my application?

Today my client decided to remove the email functionality from all interactive reports in their APEX application.

I could step through all the pages of my application and check whether there are any interactive reports used. This very manual approach can take some time and I can make mistakes by overlooking some of the interactive reports to modify.

A different approach would be to use the (officially supported) data dictionary views in APEX. Using this simple sql query I can find all interactive reports in all pages of my application (having the id 20120618):

select   workspace
       , application_id
       , application_name
       , page_id
       , region_name
       , download_formats
  from apex_application_page_ir
 where application_id=20120618
 order by page_id;

This will show all interactive reports which are used in my application:

We can even look for all interactive reports that need to be fixed by specifically looking for all reports where the download format email is enabled:

select   workspace
       , application_id
       , application_name
       , page_id
       , region_name
       , download_formats
  from apex_application_page_ir
 where application_id=20120618
   and instr(download_formats, 'EMAIL') > 0
 order by page_id;

These data dictionary views contain a wealth of information about your application. This is a real strength of Oracle Application Express, due to its nature being driven by a metadata repository.

Thus from a quality management perspective you could use the following query to make sure, all interactive reports are configured the same way, i.e. having flashback disabled, using the same display position for pagination (Top and Bottom-Left) and showing the dropdown list for selecting the rows per page:

select   workspace
       , application_id
       , application_name
       , page_id
       , region_name
       , pagination_display_position
       , show_rows_per_page
       , show_flashback
  from apex_application_page_ir
 where application_id=20120618
   and (   pagination_display_position <> 'Top and Bottom - Left'
        or show_rows_per_page = 'No'
        or show_flashback = 'Yes' )
 order by page_id;

Here is the result:

But how do we find the appropriate views? The APEX data dictionary contains information about all available data dictionary views and all of their columns.

Typicall I use the following query to find all information about interactive reports, all related views contain _IR_ in the view name:

select *
  from apex_dictionary
 where apex_view_name like '%IR%'

This will show us all view including their respective columns:

If we want to display the view only without their columns we can add the filter for the column_id 0, in there the APEX team added the overall description of the view itself:

select *
  from apex_dictionary
 where apex_view_name like '%IR%'
   and column_id=0; 

Now, we get only the view names including their description:

Regards,
~Dietmar.

Oracle Application Express 4.2.2 Patch released

Just yesterday, the most recent APEX patch set has been released. It is Release 4.2.2.00.11. The full version just as well as the patch set to apply to a 4.2.0 or 4.2.1 instance can be downloaded from here.

The full details can be found on Joel's Blog. 

Nevertheless I will have a good look at the release notes, because they contain quite some important notes, which you should be aware of before upgrading. Especially, there were some changes made to the templates. So it is wise to upgrade your existing 4.2.0 / 4.2.1 instance with the patch set directly. If you export/import a 4.2.1 APEX application into a 4.2.2 instance, you cannot leverage the changes immediately, you would have to install the new theme and switch it then. 

I will try to look at these issues in more detail ...

Cheers, 
~Dietmar.

Join me at ODTUG's Expert Panel - Oracle Application Express

In May, I will take part in one of ODTUG's webinars:

Tuesday, May 14, 2013 12:00 PM - 1:00 PM EDT

ODTUG's Expert Panel - Oracle Application Express
 
Panelists: Dietmar Aust (Opal Consulting), Roel Hartman (APEX Evangelists ), Dan McGhan (Enkitec)
Moderator: Patrick Cimolini (Patrick International APEX Consulting) 

This is a live interactive panel similar to the ACE Lunch and Learn panels held at Kscope. This innovative webinar is modeled after a call-in radio talk show. You will be able to chat a question to our organizer and if it is accepted you will have the option to verbally ask the question to our panel. Please be sure to have the proper equipment (headset and a quiet office) so that our panelists can clearly understand your question. You’ll also be able to participate in the discussion with the panel while they answer your question. 

The following are some potential topics, but audience participation will shape the scope of the panel: 

• What skill sets does an APEX team need? 
• What options are available for producing printed reports from APEX?
• Moving away from Forms/Reports to APEX 
• Interactive Reports - THE killer feature of APEX 
• Developer Productivity - negotiate and tweak the requirements with the customers in order to use standard APEX components to implement them = unparalleled productivity
• Real-world showcases (APEX can handle application complexity just as well as a high load serving many users)

Personally, I like ODTUG a lot, it is a friendly and very competent place to be, I always enjoy the conference.

The panel will be held for 90minutes, so there will be a lot of time asking questions :).

You can register for the webinar here. 

Cheers, 
~Dietmar. 

How to make your browser save your password

Typically and for good security reasons the password manager from either Firefox or any other browser will not prompt you to save the password for the login screen of the APEX workspace or any of your own APEX applications.

Why? The APEX team implemented the login page with the HTML attribute autocomplete=off:

For a development environment I find this behaviour kind of annoying, especially when the APEX builder session times out frequently and you have to login to your workspace multiple times during the day.

Thus I have looked around and found a javascript routine which removes the autocomplete=off attribute from the form.

Just right-click on this link and save it as a bookmark: Force browser to save the password
I have found it here, but it is explained on many other websites as well.

Once you click on your bookmark you will see the following message:

If you continue to log into APEX you will get the typical message where you can save the password now:

It is good practice to use a password for your password manager in Firefox, too. Here are the steps to enable it.

Cheers and enjoy,
~Dietmar.


 

APEX Training 15.04. - 17.04.2013

Wie jedes Jahr in den letzten sechs Jahren, veranstalten wir (Denes Kubicek und ich) unser

Oracle APEX: Knowhow aus der Praxis

Training in Bensheim an der Bergstrasse. Wir werden unsere bisherigen Themen überarbeiten und einige neue Themen hinzufügen. So werde ich auch folgende neue Themen in das Programm der Schulung aufnehmen:

- jQuery (Beispiele und Übungen)
- APEX Collections
- Erstellung von komplexen Forms
- APEX und Mehrsprachigkeit

Wir haben dieses Mal einen ganz speziellen Gast zu der Schulung eingeladen - Christian Rokitta aus den Niederlanden. Er ist ein Experte in Sachen Layoutgestalltung und Mobile Applikationen. Die Teilnehmer der Schulung werden sich gemeinsam für eins dieser Themen entscheiden und Christian wird es vortragen.

Unser Highlight sind auf jeden Fall die abendlichen  Q & A Session, in denen die Teilnehmer die Gelegenheit bekommen ihre eigenen Projekte vorzustellen und ihre konkrete Probleme mit uns zu diskutieren.

Die Anmeldung zur Schulung finden Sie hier.


Viele Grüße,
~Dietmar.

Upgrading the JasperReports libraries to 5.0.1

Would you like to upgrade your existing JasperReportsIntegration with the latest 5.0.1 libraries of JasperReports?

Here you go ...

Step 1: Download the files for 5.0.1

You can download the files here:

 

Step 2: Shutdown the Apache Tomcat J2EE server

 

Step 3:  Remove the existing JasperReportsLibraries from your existing installation

Typically, after you have installed your previous version of the JasperReportsIntegration toolkit on your Apache Tomcat J2EE server, the files will be located in the directory $CATALINA_HOME/webapps/JasperReportsIntegration/WEB-INF/lib, for example version 4.7.0 of JasperReports, where $CATALINA_HOME represents the path to your installation of Tomcat.

Then you would have to remove these libraries first. In this directory you should find two files for removal: _jasper-reports-delete-libs-4.7.0.sh and _jasper-reports-delete-libs-4.7.0.cmd, for *nix or Windows respectively. For *nix systems you would have to make it executable, though, e.g.: chmod u+x _jasper-reports-delete-libs-4.7.0.sh . Then you can call it and it will remove all files for version 4.7.0. But it will NOT remove the file for the JasperReportsIntegration and all other libraries which YOU might have placed there deliberately.

You can always find the required removal scripts here: http://www.opal-consulting.de/downloads/free_tools/JasperReportsLibraries/ . 

 

Step 4: Install the new 5.0.1 libraries

Now you can just copy the new libraries from JasperReportsLibraries-5.0.1.zip into $CATALINA_HOME/webapps/JasperReportsIntegration/WEB-INF/lib.

 

Step 5: Start the Apache Tomcat J2EE server again

Now you system should be upgraded to the most current JasperReports 5.0.1 !

Just drop me a note when you need updated libraries for 5.0.2, 5.0.3, ... 6.0.0, etc. I have scripts in place to create a new package of the libraries. 

Here you can find the notes from my last upgrade (4.5.0 => 4.8.0), I hope it makes sense:

**
** download the libraries from:
**  http://www.opal-consulting.de/downloads/free_tools/JasperReportsLibraries/4.8.0/JasperReportsLibraries-4.8.0.zip
** to /home/jasper/JasperReportsLibraries

cd /home/jasper
mkdir JasperReportsLibraries

** unzip them
cd JasperReportsLibraries
unzip JasperReportsLibraries-4.8.0.zip -d JasperReportsLibraries-4.8.0

** stop tomcat server
/home/jasper/tomcat/bin/shutdown.sh

** remove libraries of current jasper reports release
cd /home/jasper/tomcat/webapps/JasperReportsIntegration/WEB-INF/lib
chmod +x _jasper-reports-delete-libs-4.5.0.sh
dos2unix _jasper-reports-delete-libs-4.5.0.sh

./_jasper-reports-delete-libs-4.5.0.sh

** copy libraries of the new release to the WEB-INF/lib directory
cp /home/jasper/JasperReportsLibraries/JasperReportsLibraries-4.8.0/* /home/jasper/tomcat/webapps/JasperReportsIntegration/WEB-INF/lib

** restart tomcat
/home/jasper/tomcat/bin/startup.sh

Cheers,
~Dietmar.

Presentation available for download

In November I have been presenting at the last DOAG conference in Germany about documentation in APEX projects ( http://daust.blogspot.de/2012/11/presenting-at-doag-next-week.html ). The presentation (only in German) is now available for download.

Cheers,
~Dietmar.

APEX 4.2.1 Patchset released

Two days ago on Friday, the APEX 4.2.1 patch set has been released.

Although the quality of APEX is really high compared with other products, I tend to ALWAYS wait for the first patch set.

Ok, no more excuses for not upgrading to APEX 4.2 :).

Cheers and enjoy,
~Dietmar.

Speaking at ODTUG's KScope 13 in New Orleans

My abstracts for Oracle Application Express have just been accepted for presentation at ODTUG Kscope13, June 23-27, at the New Orleans Sheraton in New Orleans, LA:

• APEX + XE: a cookbook for a secure setup 
• A spotlight on the smaller improvements of APEX 4.2 

This is THE best conference for Oracle developers worldwide. 900 abstracts have been submitted this year and it was a real challenge to decide what will make it and what not.

Really looking forward to New Orleans. In 2008 I was speaking at ODTUG for the first time in New Orleans.

It was so cool and we had such a good time with my friends Patrick Wolf, Joel Kallman and Carl Backstrom.

Carl, we miss you!!!

See you at New Orleans,
~Dietmar.

Oracle ACE Award received!

Dear all, I have just received the Oracle ACE award for my contributions to the community and my knowledge about Oracle technologies, in particular with regards to Oracle Application Express and Oracle XE.

Thank you Oracle!

This is quite an honor since there are only 15 active Oracle ACEs in Germany:

 and in my area of expertise there is only my friend Denes and me :)

You can find my ACE profile here.

In order to celebrate the award I have received another very special (and tasty :) "award" from my girlfriend Valerie ... mmmmmhhhhh!

See you around at the next ODTUG KScope 2013 in New Orleans or in the forums,
~Dietmar.

P.S.: Thanks to all of my friends who have already congratulated me on Twitter, I appreciate it!

DOAG2012

Yesterday was the second of three days of the DOAG conference in Nürnberg. Once again this was a huge event for Oracle APEX.

We had lots of good APEX presentations from Patrick Wolf, Marc Sewtz, Christian Neumüller Carsten Czarski, Niels de Bruijn, Christian Rokitta, Peter Raganitsch and many others including myself.

We even took the time to have a little APEX Stammtisch

at the booth of my newly founded company JDD Software UG together with Jens Gauger: 

We had an awesome feedback for our first product, the "JDD Spreadsheet Publisher", which is a really flexible MS Excel export tool.

Thanks to all who visited our booth!

Cheers and all the best,
~Dietmar. 

Presenting at DOAG next week

Whow, time is passing by so fast ;).

The DOAG conference 2012 is only a few days away. And there will be 26 presentations around APEX alone!

So come by and experience the APEX enthusiam! Make sure to attend the sessions presented by Marc Sewtz, Patrick Wolf, Christian Neumüller, Carsten Czarski, Peter Raganitsch, Niels de Bruijn and other member of the APEX community. 

I will be talking about documentation in APEX projects "Der Widerspenstigen Zähmung - Dokumentation in APEX Projekten". 

In particular you will see how to extract documentation from your APEX application directly into your MS Word document. 

So that you can embed a simple tag (e.g. (:apex.application:) into your MS Word document (using your standard company template):

 With a simple macro this will be resolved to: 

and

 Also, you can document tables and views:

 to

Do you want to see the magic live? Come and join me at my presentation at DOAG in Nürnberg. 

Also, you can come to meet me at our booth ( JDD Software: http://jdd-software.com ) . Our first product, the JDD Spreadsheet Publisher is available. You can see the intro video in German or English on Youtube. 

Cheers, 
~Dietmar. 

APEX Listener 1.1.4 on Windows ... ILLEGAL_CHARACTER problems

I am currently setting up the APEX Listener at a new customer. Unfortunately they are running Windows ;).

To be more precise, they are running Windows 2008 Server R2 (64bit). I have installed Apache http server 2.2.22 and Apache Tomcat 7.0.30.

Everything went fine so far. Then I went on installing the APEX Listener 1.1.4. But I couldn't get it to work, I always received the following errors:


18.09.2012 09:59:23 org.apache.catalina.core.StandardContext listenerStart
SCHWERWIEGEND: Exception sending context initialized event to listener instance of class oracle.dbtools.rt.web.SCListener
java.lang.IllegalArgumentException: oracle.dbtools.rt.util.FilePathSyntaxException: ILLEGAL_CHARACTER
at oracle.dbtools.rt.util.Files.checkLegal(Files.java:133)
at oracle.dbtools.rt.util.Files.file(Files.java:98)
at oracle.dbtools.apex.config.ApexConfigFile.chooseExistingFile(ApexConfigFile.java:80)
at oracle.dbtools.apex.config.ApexConfigFile.choose(ApexConfigFile.java:57)
at oracle.dbtools.apex.config.ApexConfig.init(ApexConfig.java:149)

Upon researching the issue, I have found the following threads on the APEX Listener forum: 

- https://forums.oracle.com/forums/thread.jspa?messageID=10470712&#10470712

- https://forums.oracle.com/forums/thread.jspa?messageID=10493101&#10493101

The problem seems to be the within the APEX listener. In order to find the proper location for the configuration files, it will look in different locations on the server. It also checks the environment, the user home variable and the temp directory. Somehow the validation algorithm didn't account for Windows ;).

There is a workaround to set these specific environment variables for Java manually, but I didn't want to change the environment there to fix the problem. 

Anyway, I just reverted to 1.1.3 and everything worked fine! 

Cheers, 

~Dietmar. 

Clarification on Oracle XE licensing - really free for any usage?

There is an updated version on my new blog: http://dietmaraust.com/repost-clarification-on-oracle-xe-licensing-really-free-for-any-usage

Since Oracle XE was initially released in January 2006 I believed there was no usage restriction whatsoever with regards to the type of application you could build with Oracle XE and especially in combination with Oracle Application Express (Oracle APEX).

Just recently a customer of mine was told by an Oracle sales rep in Germany that certain usage scenarios are not covered by the Oracle XE license and he therefore would have to purchase a regular Oracle (at least Standard One) license for that purpose.

This sales rep argued that once you build an application as a hosted solution storing customer data in this database, this is no longer covered by the XE license.

I was stunned and couldn't believe that. Then I had a close look at the official license agreement: http://www.oracle.com/technetwork/licenses/database-11g-express-license-459621.html

There I noticed that this usage scenario wasn't covered explicitly and I wasn't 100% sure any more. I have checked the forum again and there I saw, that these questions do come up once in a while, here are just a few examples:
https://forums.oracle.com/forums/thread.jspa?messageID=1454706&#1454706
https://forums.oracle.com/forums/thread.jspa?messageID=2775638&#2775638

Thus I decided to get some official statement from Oracle.

Oracle authorities from Database Product Management and Oracle Pricing responded to my request and confirmed that any application-specific usage of Oracle XE is covered by the license agreement and there is no usage related restriction imposed on Oracle XE.

I have explicitly asked for the following use cases:

1) A web application for the internal staff only: A calendaring application where one could record his times of absence. The application is reachable via the internet but protected by a login. Only the employees of the company running Oracle XE are allowed to access the application.

2) A web application used by everybody, no customer content stored in the database: This could be a company website which is built on Oracle XE / APEX. This application is reachable over the internet by everybody. Content is only provided by the company running Oracle XE.

3) A web application used by customers on their own data (no manipulation through website): This could be an application similar to the UPS or DHL tracking of parcels. Information is processed in other backend systems and published to Oracle XE to allow a customer to query for his/her own data.

4) Supporting a business process via a web application among several of my customers: This could be a portal allowing a customer to invite other parties to bid on a public project (like building a railroad track). This portal (built using Oracle APEX and Oracle XE) supports the communication between all parties and allows the upload / download of files with regard to specific parts of the bid.

5) A web application used by customers to provide services to others: This could be multi-tenant shop system where different customers open their own shop and upload their product catalogs in order to resell them to their own customers.

Aside from that you would still have to comply to all other elements of the license agreement: http://www.oracle.com/technetwork/licenses/database-11g-express-license-459621.html ,
i.e. you have to comply to the export restriction, your end customers would have to agree to the XE license agreement and so forth.

Cheers and enjoy Oracle XE and Oracle APEX!
~Dietmar.

JasperReportsIntegration: Passing multiple parameters to the report

With the JasperReportsIntegration kit I provide a mechanism to call JasperReports reports from an APEX application.

The reports are stored locally in the filesystem accessible to the j2ee container running the JasperReportsIntegration j2ee application. This application will connect to the desired Oracle schema using connection information deployed in the J2EE container.

This j2ee application provides an url interface to calling the desired report against a desired data source.

So how can we pass parameters to our report, e.g. for producing a report for a specific customer or order number?

Let's consider my test report for that purpose (test.jrxml). The test report will list the user objects installed in the Oracle schema we connect to as well as produce some header information about the selected report locale. You can even pass three parameters to the report called parameter1, parameter2 and parameter3.

So, how can we pass the parameters to the report?

First of all we need to specifiy the parameters in the report itself. I have added the three parameters to the parameter list:

Currently all parameters (which we want to call from the JasperReportsIntegration kit) have to be specified as java.lang.String, no other data types are allowed. I might add that as a new feature in a later release. But for now only strings are allowed:

We assume that we have an APEX page with two page items, P1_FILTER_OBJECT_NAME and P1_FILTER_OBJECT_TYPE.

In order to pass these two parameters to the test report using the pl/sql interface XLIB_JASPERREPORTS, you would do the following:

declare 
  l_additional_parameters varchar2(32767);
begin
  -- set the url for the j2ee application
  -- better retrieve that from a configuration table
  xlib_jasperreports.set_report_url('http://localhost:8090/JasperReportsIntegration/report');

  -- construct addional parameter list
  l_additional_parameters := 'parameter1=' || apex_util.url_encode(:p1_filter_object_name);
  l_additional_parameters := l_additional_parameters || '&parameter2=' || apex_util.url_encode(:p1_filter_object_type);

  -- call the report and pass parameters
  xlib_jasperreports.show_report (p_rep_name => 'test',
                                  p_rep_format => xlib_jasperreports.c_rep_format_pdf,
                                  p_data_source => 'default',
                                  p_additional_params => l_additional_parameters);

  -- stop rendering of the current APEX page
  apex_application.g_unrecoverable_error := true;
end;

Why do we use apex_util.url_encode? This is required since we essentially pass the parameters via an url to the j2ee application. 

When the report is executed, we get the following result (assuming P1_FILTER_OBJECT_NAME=APEX$ and P1_FILTER_OBJECT_TYPE=TRIGGER):

 

Using the test report this will just display the passed parameters. If you want to filter the user objects using the parameters you could modify your query to:

This will filter the result either bei object_name or object_type (in the example I filtered by the object type TRIGGER):

Hope that helps,
~Dietmar.

JasperReportsIntegration 2.0.0 - Beta Test

Hi guys,

I am pleased to announce the beginning of the beta test for the upcoming release 2.0.0 of the JasperReportsIntegration kit.

I welcome you all to participate in the beta test, you might even have the chance to get some extra features in or at put your wish at least on the roadmap.

This release focuses a lot on ease of use, stability and support of different application servers. But there are interesting new features as well:

Support for JasperReports 4.7.0

I have included the latest libraries from JasperReports to stay up to date with the current JasperReports release.

Included Oracle JDBC connection pool

In addition to the JNDI configuration of the data sources you can now use a plain configuration file to use the Oracle JDBC connection pool directly. It is included in the J2EE application. Thus the configuration will be identical on all J2EE servers ... and it is really easy to use :). 

Support for the major application servers

I have already tested on Jetty, Tomcat and Weblogic. Here I will need your support to test against other J2EE servers as well!

Fine grained logging and better error messages

Iam using log4j as the logging framework now with nicely written logfiles and support for different log levels. Also, everything is checked, no more null pointer exceptions, you should always get a really precise error message. 

Reports can be deployed anywhere on the server

You can manipulate the search path for the report files using an environment variable on the operating system or a setting in the application server configuration (e.g. web.xml). This way you can easily upgrade the integration kit, your reports and the configuration files will not be removed by an undeploy/redeploy of the application. 

Storing files on the application server directly

Storing files on the application server directly (just as Oracle Reports did, destype=FILE). Developers from an Oracle Reports background will like this one. You can enable this feature and provide a filename on the server on which to save a copy of the generated report. 

Directly sending output to a network printer

Also, this is a feature well known from Oracle Reports. Just specify a printer (which is locally known to the application server) and send the output directly to it. You can specify the media (size or tray), the number of copies and whether the output shall be sorted (collate).

You can call the PrinterDiagnostics page to discover the locally installed printer and their settings: 

Major upgrade of the test application

The new version will check everything that is required and give specific suggestions on how to fix it. 

If everything is setup correctly, all tests will pass. 

If anything goes wrong, you will get detailed error messages and precise instructions on how to fix it: 

Quick Start

Personally, I love this one. Especially to get the beginners started I have included the Jetty server in the download. You can start the J2EE application just by clicking on a shell script, can it get any easier?

Even for myself it is nice to have the test report up and running in two minutes :) 

The documentation can be found here. To go to the download directly, click here. 

I have created another forum for the beta test, so please post all questions, issues, findings here: http://www.opal-consulting.de/forums/viewforum.php?f=10 .

Hope you enjoy it :). 

Cheers, 

~Dietmar.