Sunday, August 26, 2012

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.

Monday, August 13, 2012

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.

Monday, August 06, 2012

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. 

Wednesday, August 01, 2012

JasperReports 4.7.0 released

JasperReports 4.7.0 was released a few weeks ago. The new release of the iReport designer is not yet announced on the homepage, but you can download the new files already: http://sourceforge.net/projects/ireport/files/iReport/

I will update the integration kit in a few days. This time it will be released with a major update and lots of new features:

  • 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. 
  • I will test on the major J2EE servers and make sure it runs everywhere. Might need support from the community to test everything. 
  • Fine grained logging and better error messages: I am using log4j as the logging framework now with nicely written logfiles and different log levels. 
  • 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 configuration files will not be removed by an undeploy/redeploy of the application. 
  • Storing files on the application server directly (just as Oracle Reports did, destype=FILE).
  • Sending reports directly to a network printer
  • Adding security features, especially a token-based validation function for each data source. Using this technique, you can create a token in the database, pass it to the Integration kit and have it validated back in the database. This way you can share a single Tomcat instance among different applications. 
The main focus for this upcoming release was ease of deployment and troubleshooting.

Cheers, 
~Dietmar.