How to integrate JasperReport with Oracle Apex 5.

1 using
TomCat Apache server
M Kamal Hossain
www.apexexplorer.com
[email protected]
skype: apexexplorer
Date : 24 March 2017

 We need to perform the below steps for the final output

 The order steps mentioned in the Step table below which must be maintained sequentially otherwise Apex
will not launch at the end of the installation.
 If you have already installed and configured Oracle Apex and deployed to Apache
TomCat server then you should skip the step from 1 to 6

Step# Step Name Remarks

1 Installation & configuration of Oracle database 11g In this tutorial I am going to skip the steps of
R2 Enterprise Edition installation & Configuration of Oracle database
server. I assume you have a ready DB instance
2 Installation & Configuration of Oracle Apex Oracle Apex 5.1
3 Installation & Configuration of Apache Tomcat Apache Tom Cat 9
4 Installation & Configuration of Oracle Rest Data ords.3.0.9.348.07.16
Service (ORDS)
5 Deploying Oracle Apex to Apache Tomcat
6 Run the Apex instance through Apache Tomcat http://192.168.2.116:8181/devserver
7 Configure Apache Tomcat to access Manager & host- By default without localhost
manager service from remote machine (other than (http://localhost:8181/manager) manager & host-
localhost) manager service is not accessible.
8 Stop the Apache TomCat 9.0 Service
9 Install and Configure JasperReport Integration JasperReportsIntegration-1.3.0.2
10 Test JaperReport Integration

Step -1 Installation and configuration of Oracle database 11g R2

I am not going to show this step in this tutorial. I assume you have already installed and configured Oracle
Database 11g R2
Step-2 Installation & Configuration of Oracle Apex

Process summary:

 Download Oracle Apex 5.1 from Oracle website

http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html
 Unzipped downloaded installation file in the computer drive
 Create Tablespace in the database to store apex installation files
 Run script to invoke installation
 After installation Run script to configure Rest data service
 Execute command to make sure http port is set to 0
Process Details

 First download Oracle Apex 5.1 from the above URL. I have downloaded the Apex - All languages version as
shown below.

 After downloading Apex installer unzip the file in your computer drive. I have unzipped in my machine
directory as C:\apex_5.1 as shown in the below:

 Now open a command prompt window

 Go into the apex unzipped directory in the command prompt window by typing
CD C:\apex_5.1\apex
 Now create a Tablespace to store Apex installation files. I have given the Tablespace name APEX_TS

 Connect database using SYS as SYSDBA privilege as shown below

SQLPLUS SYS AS SYSDBA

 Execute the below command to create Tablespace. I have assigned Tablespace size 4GB, it will take 2 to 3
minutes to complete the Tablespace creation.

CREATE TABLESPACE APEX_TS DATAFILE 'C:\Orahome11g\oradata\RND\apex_ts.dbf'

size 4000M
extent management local
segment space management auto;
 Now time to install Oracle Apex 5.1, Make sure You are still connected in the database and in the command
prompt you didn’t quit from the directory C:\apex_5.1\apex in any case if you quit from the directory then
you have to get into the directory by typing CD C:\apex_5.1\apex and login Using SYS as SYSDBA in the
database.

 Type the below command in the command prompt and press enter to execute to start Apex installation.

@apexins.sql APEX_TS APEX_TS TEMP /i/

 The apex installation will be started and it will take approximately 10 to 15 minutes. You must see the final
confirmation message and total completion time.

 Below screen showing the success message of Apex installation & the total installation time as well. In my
case it took almost 15 minutes to complete. Your required installation time might be different.
 After the end of successful Apex installation you will be disconnected from the database, as you can see in
the above screen shot.
 Now connect in the database again using user SYS as SYSDBA privilege
 Run the script apex_rest_config.sql
@ apex_rest_config.sql
 After executing the script you will be asked to input the below information. For any prompted password
throughout this tutorial I have used the common password apex in your case you can put other password.

Enter a password for the APEX_LISTENER user [] apex

Enter a password for the APEX_REST_PUBLIC_USER user [] apex

 After successfully ending the execution you will see the confirmation message as shown below:

 Now execute the below command to stop http port:

 We need to stop http port because we don’t want to run apex standalone mode, As we are using Apache
Tomcat as Application server, Tomcat will define the application launch port.

EXEC DBMS_XDB.SETHTTPPORT(0);
Step-3 Installation & Configuration of Apache Tomcat

Process Details

 Download Apache Tomcat 9 from the tomcat website https://tomcat.apache.org/

 Click Tomcat 9 from Left navigation menu and click 32-bit/64-bit windows……link as shown below.

 After downloading you will see the Tomcat 9 installer as shown below

 Now click the installer exe to start installation

 Proceed by pressing Next button
 In the screen of choose components choose Full as shown in the below
 Click Next>
 The Configuration screen appeared as shown below.
 I have changed the port 8080 to 8181
 This port will be used to launch apex instance.
 Input the user name admin and password admin, you can put your own username/password.
 Now click Next> button
 The Java virtual machine selection screen appeared as shown below
 If the auto selected java path is not correct, then browse and find the exact path of JDK or JRE folder.
 Java must have installed. You can download it from the below link
https://java.com/en/download/
 Click Next> and the installation destination folder selection screen appeared as shown below
 Select the destination for Apache Tomcat Installation.

 Now click Install button

 After end of the installation you will see the below window
 Keep the Run Apache Tomcat checkbox checked
 Click Finish Installation
 You will see the Apache Tomcat service is starting….
 Don’t close or Cancel until the service is started.

 After successfully starting the service you will see the service runtime status icon in the taskbar tray as
shown below
  Leave the Apache service Runtime as it is please do not stop this service.
 Now open a browser window and type the url http://localhost:8181

 You can see the Apache Tomcat 9.0 home page is showing. It means the Apache Tomcat installation is
successful.

Step-4 Installation & Configuration of Oracle Rest Data Service (ORDS)

Process Details

 Open a command prompt window and login database using SYS with SYSDBA privilege
 Execute the below command

ALTER USER APEX_PUBLIC_USER IDENTIFIED BY apex ACCOUNT UNLOCK;

ALTER USER APEX_REST_PUBLIC_USER IDENTIFIED BY apex ACCOUNT UNLOCK;
  Download Oracle Rest data service from the link below
http://www.oracle.com/technetwork/developer-tools/index.html

 After downloading Oracle Rest Data service 3.0.9 unzip into your computer drive and rename the file
ords.3.0.9.348.07.16 To ords_install as shown in the below figure
 Open the folder ords_install
 You will see there is a file named ords.war

 This ords keyword will define the url link name. if you keep the file name ords.war then your apex launching
URL will be http://192.168.2.116:8181/ords

 I would like to change the URL to as my way something like based on my application name or server name or
company name ……………etc.

 If my machine name is devserver and I would like to see the apex link as
http://192.168.2.116:8181/devserver then ords.war must be changed to devserver.war

 In this tutorial I am going to change the URL as http://192.168.2.116:8181/devserver

 In the below screen it is showing the default ords.war which you can keep it as it is if you don’t want to
change the default URL http://192.168.2.116:8181/ords

 To change or customize the apex launching URL, you have to change the ords.war name to devserver.war
(or any other name that you like) before installing Rest data service.

 Also, you have to change the file name ords_params.properties to devserver _params.properties under
directory C:\ords_install\params

 Below screen showing the ords_params.properties has been changed to devserver _params.properties
 Now edit the file devserver_params.properties by open with notepad/WordPad /notepad++ ( if you didn’t
change the file name then you have to edit ords_params.properties)

 After open with any desired editor you will see the file properties of devserver _params.properties as
shown below:

 Fill up the properties that matches with you

Remember the port during Tomcat installation we put 8181 in my case.

Table space APEX_TS we created earlier steps
Db.servicename …….. if you can’t remember then you can execute the command below under sys and find
out the database service name
 SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
rnd

 Now create a Manual folder named config-ords under the directory C:\ords_install as shown below

 Now open a command prompt window and go inside the directory by typing cd C:\ords_install
 Now type the below command to set the configuration directory to config-ords for devserver.war
installation

java -jar devserver.war configdir C:\ords_install\config-ords

 if Java path variable is not defined in your computer and command not executed due to java path issue then
you can replace the ‘Java’ to its full source path like

“ C:\Program Files\Java\jre1.8.0_101\bin\java.exe” or “ C:\Program Files\Java\jdk1.8.0_101\bin\java.exe”

So, for this case your above command will be as below

“ C:\Program Files\Java\jre1.8.0_101\bin\java.exe” -jar devserver.war configdir C:\ords_install\config-ords

 after executing the command java -jar devserver.war configdir C:\ords_install\config-ords you will see the
confirmation message as shown below
 Now execute the Oracle Rest data service configuration script by running the below command
 Make sure in the command prompt window you are still inside the directory C:\ords_install
 Now type the below command and press enter to execute the installation of Rest Data Service

java -jar devserver.war

 You will be asked for the input value

 Enter the name of the database server [localhost]: <Press

Enter>

 Enter the database listen port [1521]: <Press enter if your

port is 1521, if others then put the value and press enter>

 Enter 1 to specify the database service name, or 2 to specify

the database SID [1]: <Press Enter>

 Enter the database service name:RND (I have put RND my service

name, please put your db service name)

 Enter 1 if you want to verify/install Oracle REST Data Services

schema or 2 to skip this step [1]: <Press Enter>

 Enter the database password for ORDS_PUBLIC_USER:apex

 Enter the username with SYSDBA privileges to verify the

installation [SYS] <Press Enter>

 Enter the database password for sys: sys

 If using Oracle Application Express or migrating from mod_plsql

then you must enter 1 [1]: <Press Enter>

 Enter the PL/SQL Gateway database user name [APEX_PUBLIC_USER]:

<Press Enter>
 Enter the database password for APEX_PUBLIC_USER:apex
 Enter 1 to specify passwords for Application Express RESTful
Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER)
or 2 to skip this step [1]: <Press Enter>

 Enter the database password for APEX_LISTENER: apex

 Enter the database password for APEX_REST_PUBLIC_USER: apex

 Enter 1 if you wish to start in standalone mode or 2 to exit

[1]: 2
Step-5 Deploying Oracle Apex to Apache Tomcat

Process Details:

 Copy the devserver.war file from the directory C:\ords_install and paste into the directory C:\Program
Files\Apache Software Foundation\Tomcat 9.0\webapps

 File source to copy showing in the below screen

 After copy file devserver.war will move to the directory shown as below to paste

 Create a Folder manually named ‘i’ in the below directory

C:\Program Files\Apache Software Foundation\Tomcat 9.0\webapps

 Now go to the directory and copy the full content of images folder
 Go the directory of C:\Program Files\Apache Software Foundation\Tomcat 9.0\webapps\i
 And paste the full content of the image folder in the i folder in the tom cat

 Below screen showing how file moving from source folder to destination folder.
 Left side source folder and right side destination folder

Step-6 Run the Apex instance through Apache Tomcat

Process Detals:

 Open a browser window and type the URL

http://localhost:8181/devserver or
http://192.168.2.116:8181/devserver

 After executing the above URL, you see below screen, the Oracle Application Express login page is appeared.
It means you have successfully completed all the installation steps.

 If you see 404 page error instead of Apex login page appearing, then Stop Apache Tomcat Service and Start
again, then refresh the page or re-enter the URL, The Apex login page should appear.
 Now open a command prompt window and get into the directory C:\apex_5.1\apex
 Now login using SYS as SYSDBA privilege in the database
 Run the script apxchpwd.sql to reset the admin password
 Now launch the apex by typing http://192.168.2.116:8181/devserver
 The application express login window appeared
 Type below and login first time in Apex
Workspace: Internal
Username : admin
Password: <the password that you have reset in the above step>
Note: if you forgot password then reset again following the same step in the above.
 After filling up credential information press “Sign In” and you will be see the apex home screen
Step: 7 Configuring Apache Tomcat to access manager & host-manager service from remote machine

Process Details
 By default manager & host-manager page can’t be accessed from remote machine, only local machine is
allowed, we would like to configure the option to launch those pages from remote machine.
 Go to your Tomcat Server installation folder as you see in the below screen
 Create 2 XML blank file manager.xml and host-manager.xml

 Copy the below XML code text and paste both in the manger.xml & host-manager.xml file and save it

<Context privileged="true" antiResourceLocking="false"

docBase="${catalina.home}/webapps/manager">
<Valve className="org.apache.catalina.valves.RemoteAddrValve"
allow="^.*$" />
</Context>

 Make sure you saved both files as extension .xml

 See the final status after editing the above text both in the manager.xml & host-manager.xml file in the
below 2 screens.
Manager.xml file content:
 host-manager.xml file content:

 To test, from a remote machine type the URL http://192.168.2.116:8181/manager you will see the manager
page is showing. You can Click the /host-manager link from this page, you can view host-manager page as
well.

Step : 8 Stopping Apache TomCat Service

Process Details:
 During JasperReport configuration & installation we will not keep the Apache Tomcat Service open
 As shown in the below figure, you will find the Apache Tomcat service icon at the right corner of the
windows taskbar tray.
 Right click the Apache Tomcat service and click “Stop Service”
  Upon clicking “Stop Service” you will see the below screen the Service stopping is in progress, please do not
cancel or close until the process is completed

 After completing the process you can make sure the Apache Tomcat service is showing stopped icon as
shown in the below figure

Step : 9 Installation & Configuration of JasperReporstIntegration with Oracle Apex 5.1

Process details

 Download JasperReportsIntegration File from the link below

http://www.opal-consulting.de/downloads/free_tools/JasperReportsIntegration/1.3.0/
 I have downloaded the version JasperReportsIntegration-1.3.0.0.zip as you can see from the
screen below
 After downloading the file, Unzip it and rename to JasperReportsIntegration

 Open a command prompt window and go into the directory of CD C:\JasperReportsIntegration\sql

  Now connect to the database using user SYS with SYSDBA privilege

SQLPLUS SYS AS SYSDBA

 After connecting with the database using SYS, we need to create a local database Schema user
 I have created a local Schema named APEXDEV using the below command, you can put any name as you like
 I have assigned the default table space for this user is APEX_TS, you can assign any available tablespace that
you have
 I could grant DBA, but I didn’t like to assign DBA role to APEXDEV user, but you can do that for your local
schema, there should not be any issue.
 Make sure all the command below has been executed successfully.

CREATE USER APEXDEV identified by apexdev;

grant connect,resource to APEXDEV;

grant create table,create view,create sequence,create procedure,create

trigger,create synonym to APEXDEV;

grant create any table,create any view,create any sequence,create any

procedure,create any trigger,create any synonym to APEXDEV;

alter user APEXDEV default tablespace apex_ts;

alter user APEXDEV quota unlimited on apex_ts;

/
 After creating user APEXDEV, we need to run the script sys_install.sql which you will find in the directory
C:\JasperReportsIntegration\sql
 Now make sure you are connected with database under SYS, run the below script, First type the script name
then give a space and type the local Schema name that we created in the above step.

@sys_install.sql APEXDEV

 Execute the above command by pressing Enter

 You will see the below success execution status in the below screen.

 Make sure you are still connected in the database Under SYS, execute the below command to assign the
Network Access permission (ACL) for the APEXDEV & APEX_050100 user
 Make sure the below command has been executed successfully and commit completed as well.
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'ANY_HOST_ACCESS.xml',
description => 'POWER ACCESS USER',
principal => 'APEXDEV',
is_grant => true,
privilege => 'connect');

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'ANY_HOST_ACCESS.xml',

principal => 'APEXDEV',
is_grant => true,
privilege => 'resolve');

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'ANY_HOST_ACCESS.xml',

principal => 'APEX_050100',
is_grant => true,
privilege => 'connect');

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'ANY_HOST_ACCESS.xml',

principal => 'APEX_050100',
is_grant => true,
privilege => 'resolve');

DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'ANY_HOST_ACCESS.xml',
host => '*',
lower_port => 1,
upper_port => 9999);
end;
/
commit;
/
 Please do not run the script sys_install_acl.sql, we don’t need to run it, we have
covered it in the above statement.
 Now connect to the local schema APEXDEV, you can switch by typing
CONN APEXDEV then put the password and connect
 Now you should make sure you are connected in the database with Local Schema, in my case APEXDEV
 Now we have to execute the script user_install.sql, after connected under APEXDEV schema type the
command to execute the script.
@user_install.sql
 After successfully executing the above command you will see the below status

 Now copy the library file ojdbc6, orai18n from the directory C:\JasperReportsIntegration\lib & paste into
the directory C:\Program Files\Apache Software Foundation\Tomcat 9.0\lib
 Below screen showing files are copying from the source (left) & moving into the destination (right)
 Now copy the JasperReportsIntegration.war file from the directory C:\JasperReportsIntegration\webapp
and paste in the directory C:\Program Files\Apache Software Foundation\Tomcat 9.0\webapps as shown
in the below screen.

 Now start the service of Apache Tomcat9 as shown in the below screen
 After starting the service of Apache Tomcat you will see there is a new folder JasperReportsIntegration has
been created under the directory C:\Program Files\Apache Software Foundation\Tomcat 9.0\webapps as
shown in the below screen

 Now edit the file conext.xml under the directory C:\Program Files\Apache Software Foundation\Tomcat
9.0\webapps\JasperReportsIntegration\META-INF

 Open the context.xml file with any editor like notepad/notepad++/word pad etc. in the content you will find
a url as shown below.
url="jdbc:oracle:thin:@localhost:1521:DEV"

In the URL text : localhost = the host name, 1521 = Database port, DEV = Oracle DB Service Name
 After editing the context.xml file, do not forget to save the content.
 Now open a browser window and type the link http://192.168.2.116:8181/manager (from remote/local
machine) or http://localhost:8181/manager (from local machine)
 You will see the home page of Apache Tomcat and the /JasperReportsIntegration has been added in the
application list and the running status is showing true.
  Now click the /JasperReportsIntegration link from the above screen and you will be redirected to the sample
report page as shown below

 Now click the show report button (Senden), you will see the PDF report on screen as shown below

 If you see the below screen, it means you have successfully Integrated JasperReport integrated.

Step : 10 Run a Report from Oracle Apex instance and test the JasperReport Integration
Process Details

 Open a browser window type the url http://192.168.2.116:8181/devserver or

http://localhost:8181/devserver (your login url text may be different based on your configuration) to launch
the apex instance, apex login page will be appeared.
 Input the credential. If you forgot the admin password then you can re-generate it by running script
C:\apex_5.1\apex\apxchpwd.sql under user SYS

 After logging into Apex admin, click the Create Workspace> button and the new workspace creation page
will be appeared as shown below:
 I have given Workspace name “ JASPERREPORTS ” you can input your own preferred name. click Next>
button
 Re-use existing schema? = Yes, I have selected the schema = APEXDEV
 Remember you should select schema those are granted & configured for required role of JasperReport
Integraton that we have done in the JasperReport Integration part.
 In this tutorial I had created APEXDEV schema and assigned those required role to that schema that’s why I
have selected APEXDEV schema as shown below

 Click Next> button, in the new screen type your desired user name and password and finally create the
workspace
 Once the confirmation shown the workspace is created successfully then click “Done “button.
 Now sign-out and sign in to the JASPERREPORTS workspace
 Now put the credential as below:

Worksapce: JASPERREPORTS ( not case sensitive)

Username : admin
Password: <the password that you input during WS creation)

 At the first login you will be asked to reset the password, put the old password and reset the password
 Now click Apply Changes and you will be directed to the Apex home page
 Now click Application Builder then Import button

 In the import screen browse to select the example application provided by the JasperSoft to launch report
from Oracle Apex instance & test the integration which you will find in the directory
C:\JasperReportsIntegration\apex

 Select the f860_JasperIntegrationTest_1.3.0.2.sql and click Open button, then click Next> button
 File import confirmation page showing. Now click Next> button
 Now selection summary page is showing, keep all the setting as it is, click Install Application button
 Follow the screen instruction and finally you will get the below screen
 Click the Run Application button, you will see the home page of the example report testing page

 Please do not click any of the Tab/link in the application as shown above until you map the Apex Report
connection configuration with the configuration that we have done during JasperReport integration &
configuration steps.

 Now click the Application 101 button from the application tool bar that you will find at the bottom of the
page as shown in the screen below:
 New page appeared, Now click shared components button at the top-right of the page

 Click application definition link as shown below

 Now application details page appeared, now go at the bottom of the page and locate Substitutions section
As shown below screen

 Change the substitution value of the parameter ‘G_REPORT_URL’ as shown in the figure
 Change Localhost to 192.168.2.116 (put your own IP)
 Change Port 8080 to 8181 (put your port that you configured)
 Click “Apply Changes” and now run the application

 When you run the application, home page is appeared. Click the Tab Report Tester and you will see the repot
page showing.
 You will notice the Report URL link text has been changed automatically based on the configuration in the
shared components > Application definition we have configured in the earlier step.
 Now click “Show Report” button, you will see the pdf report is appeared, you have seen this report earlier,
but this time you are invoking this report from Oracle Apex instance.

 Now go back to the example application and click “Demo” button

 Click Call Report in Popup Window link then click open in popup window ...
 You will see the pop-up pdf report is showing from Apex as shown in the below:
 The above report is showing means, you have 100% configured and Integrated JasperReportsIntegration with
Oracle Apex 5.1

Thank you very much for your patience and

completing all the steps above. Wish you good
luck in Apex development with JaperReports