Installing and Configuring Oracle Application Express and BI Publisher in Oracle Database 11g Release 2

Purpose

This tutorial describes how to configure Oracle Application Express and BI Publisher in Oracle Database 11g Release 2.

Time to Complete

Approximately 30 minutes

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Configuring the Embedded PL/SQL Gateway
 Enabling Network Services
 Installing BI Publisher
 Configuring BI Publisher in Application Express Administration
 Creating a New Workspace and Workspace Administrator
 Testing BI Publisher Report Creation
 Installing and Testing Translation Versions of Application Express
 Summary

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

Installation of Oracle Application Express in Oracle Database 11g has become much easier. In Oracle Database 11g, Oracle Application Express is installed when the database is installed. All you need to do is configure the PL/SQL gateway. In prior releases of the Oracle Database, you needed to install Apache, which is no longer needed although you can still use that option in Oracle Database 11g.

In order to take advantage of PDF reporting in Application Express, you can use BI Publisher. In this tutorial, you will install BI Publisher, configure Application Express to use the BI Publisher as its print server and run a PDF report in Application Express.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Install Oracle Database 11g with the Advanced Installation. Note that if you install the database with the basic installation, you will not get all the necessary files to perform the Installing and Testing Translation Versions of Application Express section of this tutorial.

2.

Download and unzip the apexinst.zip file into your working directory which contains the files needed to perform this tutorial.

 

Back to Topic List

Configuring the Embedded PL/SQL Gateway

In Oracle Database 11g, you configure the embedded PL/SQL gateway by running the configuration script apxconf.sql. Running this script enables you to configure the port for the Oracle XML DB HTTP server and to specify a password for the Oracle Application Express ADMIN account. Then you unlock the ANONYMOUS account. Perform the following steps:

1.

From your terminal window, enter the following command:

                               
sqlplus / as sysdba
@apxconf
                            

Enter oracle when prompted for the ADMIN password.

Press enter (to use the 8080 default) when prompted for the port for the XDB HTTP Listener.

 

2.

You also need to unlock the anonymous user. From your terminal window, enter the following command:

                               
alter user anonymous account unlock; 
                            

 

2.

To can now verify the port number where the Oracle XML DB HTTP Server is running. From your terminal window, enter the following command:

SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

 

Back to Topic List

Enabling Network Services

By default, the ability to interact with network services is disabled in Oracle Database 11g release 2 (11.2). Therefore, if you are running Oracle Application Express with Oracle Database 11g release 2 (11.2), you must use the new DBMS_NETWORK_ACL_ADMIN package to grant connect privileges to any host for the APEX_030200 database user. Failing to grant these privileges results in issues with:

Perform the following steps:

1.

You now need to provide less privileged access to local network resources. This will enable indexing the Oracle Application Express Online Help and will enable email and PDF printing when the server is also on the local host. From your terminal window, enter the following command:

                               
cd < location where you unzipped the files from the prerequisites>
sqlplus / as sysdba
@change_priv
exit
                            

The script change_priv.sql is as follows:

                               
DECLARE
                                
ACL_PATH VARCHAR2(4000);
ACL_ID RAW(16);
BEGIN
-- Look for the ACL currently assigned to 'localhost' and give APEX_030200
-- the "connect" privilege if APEX_030200 does not have the privilege yet.
SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = 'localhost' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
-- Before checking the privilege, ensure that the ACL is valid
-- (for example, does not contain stale references to dropped users).
-- If it does, the following exception will be raised:
--
-- ORA-44416: Invalid ACL: Unresolved principal 'APEX_030200'
-- ORA-06512: at "XDB.DBMS_XDBZ", line ...
--
SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
FROM XDB.XDB$ACL A, PATH_VIEW P
WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
EQUALS_PATH(P.RES, ACL_PATH) = 1;
DBMS_XDBZ.ValidateACL(ACL_ID);
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_030200',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'APEX_030200', TRUE, 'connect');
END IF;
EXCEPTION
-- When no ACL has been assigned to 'localhost'.
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('local-access-users.xml',
'ACL that lets power users to connect to everywhere',
'APEX_030200', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('local-access-users.xml','localhost');
END;
/
COMMIT;


Back to Topic List

Installing BI Publisher

Oracle Business Intelligence Publisher (BI Publisher, formerly XML Publisher) is an enterprise reporting solution to author, manage, and deliver all types of highly formatted documents. In Application Express 3.0 and later, you can use BI Publisher as your print server to deliver PDF reports within Application Express. In order to utilize the capabilities of BI Publisher, it needs to be installed and configured within Application Express. Perform the following steps to install BI Publisher:

1.

From your browser window, enter the following URL:

                               
http://www.oracle.com/technology/software/products/publishing/index.html
                            

Accept the license and download the file corresponding to the environment you want to install.

 

2.

Unzip the file and depending on what environment you are in, start the installer. In this case, you execute the following command on linux.

                               
./runInstaller
                            

 

3.

When the Welcome window appears, click Next.

                               
                                  
                              
                            

 

4.

Enter BIPHome1 for Name and /u01/app/oracle/product/bipub for Path and click Next.

                               
                                  
                              
                            

 

5.

Accept the default Installation Type of Basic and click Next.

                               
                                  
                              
                            

 

6.

Enter welcome1 for oc4jadmin Password and Confirm Password and click Next.

                               
                                  
                              
                            

 

7.

At the Summary window click Install.

                               
                                  
                              
                            

 

8.

The progress window appears.

                               
                                  
                              
                            

 

9.

The configuration assistant will run.

                               
                                  
                              
                            

 

10.

The installation completed successfully. Click Exit.

                               
                                  
                              
                            

 

11.

Click Yes to confirm.

                               
                                  
                              
                            

Note that after the installation is complete, a window is displayed where BI Publisher OC4J is running. Do not close this window. BI Publisher OC4J must be running to be able to successfully generate a PDF report in Application Express.

 

Back to Topic List

Configuring BI Publisher in Application Express Administration

Before you can create a PDF Report using BI Publisher, you need to configure Application Express to use BI Publisher. Perform the following steps:

1.

Open your browser and enter the following URL:

                               
http://<hostname>:8080/apex/apex_admin
                            

Enter admin as the username and enter your password oracle (or whatever password you specified during the installation). Then click Login.


2.

The first time you login, you need to change your password. Enter your current password oracle and then enter the new password (in the VM Template provided, the password is Oracle_1) and click Apply Changes.

 

3.

Click Return.

 

4.

You need to login again and specify your new password. Then click Login.

 

5.

Select the arrow next to Manage Services, select the arrow next to Manage Envrionment Settings and select Instance Settings.

 

6.

Select the Report Printing tab.

 

7.

Make sure the following are specified and click Apply Changes.

Print Server: Advanced
Print Server Protocol: HTTP
Print Server Host Address: localhost
Print Server Port: 9704
Print Server Script: /xmlpserver/convert

Application Express has been configured to use BI Publisher. In the next section, you create a workspace.

 

Back to Topic List

Creating a New Workspace and Workspace Administrator User

Before you create an application, you need to create a workspace and a workspace administrator user. Perform the following steps:

1.

Select the Manage Workspaces tab.

 

2.

Under Manage Workspaces, select Create Workspace.

 

3.

Enter obe as the Workspace Name and click Next.

 

4.

For "Re-use Existing Schema?" select No. Enter obe in the Schema Name and Password fields, and select 5 for the Space Quota. Then click Next.

 

5.

Enter obe as the Administrator Username, and enter your Administrator Password and your e-mail address. Then click Next. Note: A new administrator user will be created in addition to the workspace.

 

6.

Review your workspace request, and then click Create.

 

7.

Your workspace and user are now created. Click Done .

 

8.

You now want to log in as the obe user for the obe workspace. Click Logout .

 

Back to Topic List

Testing BI Publisher Report Creation

At this point you can test the BI Publisher configuration by creating a PDF report. Perform the following steps:

1.

Click Login.

 

2.

Enter the following details, and click Login.

Workspace: obe
Username: obe
Password: <your password>

 

3.

Click Application Builder.

 

4.

Click Sample Application.

 

5.

Select Details from the View pulldown and click Go.

 

6.

Scroll down to the bottom of the page and select the Print link.

 

6.

Open the file in a viewer (i.e. Adobe Reader) and click OK.

 

7.

Your PDF report is shown successfully.

Note: If your report does not appear, check to make sure that BI Publisher OC4J is started. Here is the command to start it:

                               
                                 
/u01/app/oracle/product/bipub/oc4j_bi/bin/oc4j -start
                              
                            

 

Back to Topic List

Installing Translation Versions of Application Express

If you want to be able to display your Application Express Application in different languages, you need to install the translated version for the language you want to translate to. For demonstration purposes, you will install and test the translation of the German language. Perform the following steps:

1.

From your terminal window, execute the following commands:

                               
cd $ORACLE_HOME/apex/builder/de
sqlplus / as sysdba
alter session set current_schema=apex_030200;
@load_de.sql
                            

 

2.

In your browser you need to add the German Language. Select Edit > Preferences.

 

2.

Select the Advanced tab and under the General tab, click Edit Languages.

 

3.

Select German (de) from the drop down list box and click Add.

 

4.

Make sure German is selected and click Move Up until German appears first in the list.

 

5.

Click OK.

 

6.

Click Close.

 

6.

To test the new language in Application Express, switch to your browser and enter the following URL:

http://localhost:8080/apex

Notice that Application Express is now in German.

 

Back to Topic List

Summary

In this tutorial, you learned how to:

 Install and configure Oracle Application Express

Install BI Publisher and configure Application Express to use as Print Server

Create a Workspace and Workspace Administrator

 Run a PDF Report

Back to Topic List

 Move your mouse over this icon to hide all screenshots.

Left Curve
Popular Downloads
Right Curve
Left Curve Right Curve