In every business application created in any technology, users need to create some reports (e.g. results of some researches, letters, notes, invoices, etc.), which may be saved on hard drive.
In every business application created in any technology, users need to create some reports (e.g. results of some researches, letters, notes, invoices, etc.), which may be saved on hard drive.
Due to the existing needs, Oracle developer community has created a free-to-use package written in PL/SQL, which can be integrated with any application running on Oracle database. It allows developers printing PDF files directly from their application. I am writing about jrxml2pdf, excellent tool which can be downloaded for free on SourceForge (under MIT and GPL licenses).
The name of the package – jrxml2pdf – is not insignificant. Package supports JRXML files created in other free tool – iReport Designer, distributed under AGPL licence. The program allows to create PDF templates in easy way. Note that, today, last stable version of iReport Designer tested under jrxml2pdf is 4.7.0.
In this article I will describe how to print PDFs directly from your PL/SQL-application. I will use some examples based on Oracle Application Express 5.0 (Oracle APEX) and I will show how to use the Jaspersoft iReport Designer. Furthermore, you can find some simple examples in attachments at the end of this article.
Prepare a report in iReport Designer
Before we start, please note that iReport Designer does not allow to connect Oracle Database by default. You need drivers to do it. For details how to configure the connection I refer to the web-page.
After drivers installation and connection to the HR schema configuration, you can create your first report. For simplicity, I prepared a report that is ready to download here. The report displays a list of all employees from the EMPLOYEES table in HR schema, installed by default in Oracle 11g Express Edition.
If you want to create a report by yourself, please follow the 
icon where you can write your own SQL query. Next click the Preview tab to see the results. Of course you can skip this part and download the shared file, open it and enjoy using iReport Designer.
Below you can find SQL query that you can paste to the iReport Designer.
|
1
2
3
4
5
6
7
8
9
|
select
first_name,
last_name,
email,
phone_number,
job_id,
to_char(salary) salary
from hr.employees
order by salary desc;
|
Installation of jrxml2pdf package
The next step is to install the jrxml2pdf package. After downloading the package, it should be unpacked and installed according to the instruction, which you can find in the doc/ directory. In the end, the installation process comes down to two points:
- Grant permissions to UTL_FILE for user where you installed jrxml2pdf package (I used HR)
|
1
|
GRANT EXECUTE ON UTL_FILE TO <schema where you have installed jrxml2pdf package (I used HR)>
|
- Run sqlplus from install/ directory and execute install.sql file.
If you installed the package successfully, expand the Tables and Packages in SQL Developer (or other database IDE). You should see there new objects like on the picture below:
Import Jasper report to Oracle Database
Now you can upload the Jasper report to one of newly created table:
- Go to iReport Designer and open the file or create your own report
- Check if the report works well by clicking the Preview tab
- If your report works well, you can see list of all employees, like on this screen. Next, click XML tab.
- Copy XML code
Next you need to paste XML code to JRXML_REPORT_DEFINITIONS table.
- Go to your favorite IDE (I use SQL Developer)
- Expand Tables and click on JRXML_REPORT_DEFINITIONS table
- In Data tab, add a new record
- JRD_ID – add next number in order of sequence
- JRD_NAME – very important column, where you put name of the report (without special characters, locals and without blank space); I wrote “blog-report1”
- JRD_DESCRIPTION – you can leave that field empty (null)
- JRD_XML – here you should paste your XML code, copied from iReport Designer
- Save your changes, by clicking Commit button
Connect to APEX application
If XML code has been imported to the table, go to your Oracle Application Express (APEX) application and create new page. If you want you can use my sample application (developed in APEX 5.0, ready to download here).
On new page, you should “Print PDF” button and PL/SQL process like the one below. Please note, that code works well on HR schema, because there jrxml2pdf package has been installed. If your APEX application works on another schema in Oracle Database, grant privileges to JRXML packages and tables.
|
1
2
3
4
5
6
7
8
9
10
|
DECLARE
vcName VARCHAR2(200);
lParams HR.PK_JRXML2PDF_REPGEN.TPARAMLIST;
bl BLOB;
BEGIN
vcName := ‘blog-report1’;
bl := HR.PK_JRXML2PDF_REPGEN.FK_RUN(i_vcName=>vcName);
HR.PK_JRXML2PDF_REPGEN.PR_SHOW_REPORT(bl);
APEX_APPLICATION.STOP_APEX_ENGINE;
END;
|
If the APEX application has been imported or created, run your application by clicking
Next, click Print PDF and download your PDF report.
Summary
Jrxml2pdf – it is a excellent and free to use tool developed by Andreas Weiden.
The jrxml2pdf allows to print PDF files from any PL/SQL application. User can generate any report in database and save it on his computer’s hard drive.
In the attachment you can find some useful examples. Please check them out. You can find there 3 examples how you can use jrxm2pdf package in your APEX application. Just download, unpack and import. For more details, please read README.txt or README.pdf.