Jasper Reports – Javascript & PL/SQL Integration

In the last couple of months there has been a lot of information on how to migrate Oracle Reports to other modern reporting tools. For me, as I am dealing mostly with Jasper Reports, the question came up on how we actually can integrate Jasper Reports in Oracle Forms or other tools. So after a workshop together with TIBCO I started to look into the implementation options that the Community and Pro editions have. I had a look at two options: Visualize.js (part of the Jasper Reports Pro Edition) and the REST API (part of both editions).

Visualize.js

Visualize.js is a Javascript API framework that is part of the Pro edition of Jasper Reports. It gives you the possibility to embed Jasper Reports into web applications. Besides the possibility of integration of „simple reporting“, it is possible to „embed interactive HTML5 visualizations, dashboards or multi-element reports using standard JavaScript“.  Oracle Forms in the latest version (currently 12.2.1.3) is able to integrate JavaScript also in environments that use the Webstart and Standalone Launcher. With that information it should be possible to call visualize.js from Oracle Forms or any other web application supporting JavaScript, and integrate Jasper Reports from that. In my little test scenario I was able to integrate a Jasper report from the server really quick. My next try is going to be to integrate a report or even reporting dashboard from Oracle Forms via Jetty.

REST API

The Jasper Reports REST API is a very powerful way to access Jasper Reports. It is very well documented and avaiable for both, Jasper Reports Pro and Community Edition. In my first tests, I used SoapUI to test the API and get a better understanding on how it works. As you can see in the video, I used XML rather than JSON for the data transfer.

After I knew which API I needed, I asked myself, what would be a good language for the integration. I played a little bit with a Java Client but realized, that PL/SQL already delivers a nice toolset for the integration of web services:

• APEX Web Service
• UTL_HTTP

I chose UTL_HTTP for the first try and created a package for the integration:

l_param_list := ‚<?xml version=“1.0″ encoding=“UTF-8″ standalone=“yes“?><reportExecutionRequest><reportUnitUri>’||REPORTUNITURI||'</reportUnitUri><async>’||ASYNC||'</async><freshData>’||FRESHDATA||'</freshData><saveDataSnapshot>false</saveDataSnapshot><outputFormat>’||OUTPUT_FORMAT||'</outputFormat><interactive>’||INTERACTIVE||'</interactive><ignorePagination>false</ignorePagination><parameters></parameters></reportExecutionRequest>‘;

— preparing Request…
l_http_request := UTL_HTTP.begin_request (‚http://’||JASPER_SERVER||‘:’||JASPER_PORT||’/jasperserver-pro/rest_v2/reportExecutions‘, ‚POST‘ , ‚HTTP/1.1‘);

UTL_HTTP.write_text(l_http_request, l_param_list);

The idea is to establish the http-connection to the webservice (reportExecutionRequest), transfer the neccessary data, like report name and mode (synchronous vs. asynchronous). If successfully executed, the API will return a XML with an export-ID and a request-ID. I’ve used the XMLTYPE feature of the Oracle database to store the XML since it is not that big. If you have a 12c database there are also a lot of features to handle JSON responses.

With for instance extractvalue the IDs can be extracted and you can actually grab the finished report. In my scenario I used the synchronous call. If you would like to call the Report asynchronous, you first have to get the status information before you try to get the result. Anyway if you call the report synchronously, the first request will wait for the report to be finished and you will just have to get the final document with:

 l_http_request := UTL_HTTP.begin_request( ‚http://’||JASPER_SERVER||‘:’||JASPER_PORT||’/jasperserver-pro/rest_v2/reportExecutions/’||v_request_id||’/exports/’||v_export_id||’/outputResource‘ , ‚GET‘ , ‚HTTP/1.1‘);

I used UTL_HTTP.READ_RAW to read the result from the response-handler and store the report as a BLOB in the database. You can see the whole process in the video below.