PITSS Tech BlogNews zu Java, PL/SQL, Entwicklung & Technologiewechsel
by Daniel Kampf
Senior Consultant, PITSS GmbH
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).
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
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‘);
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.