This article provides instructions for installing and configuring the Oracle Application Express (formerly HTML DB) software release 2.2. Please note that these instructions cover the standalone version only.
Installing Oracle Application Express is a two step process:
- Configure an Oracle HTTP Server (Release 9.0.3 or higher) with mod_plsql which is used to connect to the Oracle database where the Oracle Application Express objects will be installed. It is also possible to use Oracle 9i Application Server release 1 (184.108.40.206) or higher.
- Install the database objects that make up Oracle Application Express to a pre-existing Oracle database (Oracle9i Release 2 (220.127.116.11) or later).
Important Note: When installing Oracle Application Express, you will be required to install the Oracle HTTP Server which can be found on the Oracle10g Release 2 Companion CD. Although not a requirement, I will be installing the Oracle HTTP Server to the same machine which already includes an installation of the Oracle Database software.
The Oracle HTTP Server cannot be installed into an existing Oracle Home. It must be installed into a new Oracle Home!
Prerequisites for the Installation:
This section describes the requirements for installing Oracle Application Express, Release 2.2.
To view or develop Oracle Application Express applications, Web browsers must support Java Script and the HTML 4.0 and CSS 1.0 standards. The following browsers meet this requirement:
- Microsoft Internet Explorer 6.0 or higher (Windows only)
- Netscape Communicator 7.2 or higher
- Mozilla 1.2 or higher
- Firefox 1.0 or higher
Operating System Requirements
From what I have read and tested, Oracle Application Express can be installed on the following Operating System platforms:
Linux – Red Hat Enterprise version AS/ES 2.1 or higher; or SUSE Enterprise Server version SLES-8 or higher.
- Solaris 9 or higher.
- Windows 2000 Professions or higher (with service pack 3 or higher)
- Windows XP Professional
- Windows 2003 (32-bit systems)
Disk Space Requirements
Verify that the file system that contains the Oracle home directory contains at least 460MB of free disk space for the installation. Also during the installation process, about 110MB of temporary disk space will be required.
For installing Application Express we need to have a database created before. Same database will be used for creating the objects required for Oracle Application Express.
The installation of Oracle Application Express requires certain objects to be created in an Oracle database. With Oracle Application Express 2.2, the database is required to be Oracle9i Release 2 (18.104.22.168) or later. All of the Oracle Application Express database objects should be created in a separate tablespace which we will create later on in this article.
Shared Pool Size Requirements
Oracle Application Express requires the shared_pool_size of the target database to be at least 100 MB.
Determine the current value of the shared_pool_size parameter:
SQL> show parameter shared_pool_size
The initialization parameter JOB_QUEUE_PROCESSES for the Oracle database determines the maximum number of concurrently running jobs. Starting with Oracle Application Express Release 2.0, transactional support and SQL scripts require jobs. If JOB_QUEUE_PROCESSES is not enabled and working properly, you cannot successfully execute a script.
You can view the number of JOB_QUEUE_PROCESSES from SQL*Plus by running the following SQL statement:
SQL> SELECT VALUE FROM v$parameter WHERE NAME = ‘job_queue_processes’;
If you need to modify the JOB_QUEUE_PROCESSES initialization parameter, log into the database as SYSDBA using SQL*Plus and run the ALTER SYSTEM … statement. For example, to set the number of JOB_QUEUE_PROCESSES to 20, use:
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 20;
Installing Oracle HTTP Server
When installing Oracle Application Express, you will be required to install the Oracle HTTP Server which can be found on the Oracle10g Release 2 Companion CD. Although not a requirement, I will be installing the Oracle HTTP Server to the same machine which already includes an installation of the Oracle Database.
Important Note: The Oracle HTTP Server cannot be installed into an existing Oracle Home. If another Oracle product is installed on the same server, you will need to create a new Oracle home name and location for the Oracle HTTP Server
This section assumes you have access to and will be installing the Oracle HTTP Server from the Oracle10g Release 2 Companion CD. Note that with a few minor modifications, you can also install an earlier version of the Oracle HTTP Server from the Oracle10g Release 1 Companion CD.
You can perform the install from the CD or download it from Oracle Technology Network (OTN)
After downloading and unzipping the Oracle10g Release 2 Companion CD software to a temporary directory, follow the instructions below to install the Oracle HTTP Server:
Step 1) Start the Oracle Universal Installer from the Oracle10g Release 2 Companion CD
./runInstaller -invPtrLoc /u01/app/orsbox/OHS/oraInst.Loc
On Select a product to Install Select Oracle Database 10g Companion Products 10.2.0.1.0
On Specify Home Details Select Different Oracle Home path and name (other then ORACLE_HOME for database)
On Available Product Components screen select ‘Apache Standalone’ product only
Pre-Requisite check will run. Check if you get any error here. If there is some error then correct the same and repeat the above steps again.
Check the summary screen and press in ‘Install‘ button
Installer will show you the progress for installation
Configuration Assistant will configure Apache HTTP server and OC4J component.
End of installation. Press Exit and come out of the wizard.
Test the Oracle HTTP Server Installation
After the installation of the Oracle HTTP Server is complete, you should test it. Point your browser to the URL that was displayed on the “End of Installation” screen by the Oracle Universal Installer. For me, the URL was http://ocvmrh2124.us.oracle.com:7777/
Starting / Stopping / Restarting Oracle HTTP Server:
Start All: $ORACLE_HOME/opmn/bin/opmnctl startall
Stop All : $ORACLE_HOME/opmn/bin/opmnctl stopall
Start HTTP Process: $ORACLE_HOME/opmn/bin/opmnctl startproc ias-component=HTTP_Server
Stop HTTP Process : $ORACLE_HOME/opmn/bin/opmnctl stopproc ias-component=HTTP_Server
Restart HTTP Process: $ORACLE_HOME/opmn/bin/opmnctl restartproc ias-component=HTTP_Server
Installing Oracle Application Express:
Download Oracle Application Express 2.2
APEX 2.2 can be downloaded from official OTN site
Oracle Application Express was previously known as HTML DB. With the release of 2.0, Oracle officially changed the name from HTML DB to Oracle Application Express (APEX) on January 30, 2006.
Important Note: Note that the version of HTML DB shipped on the Oracle10g Release 2 Companion CD is version 1.6. With Oracle10g Release 1, the version of HTML DB is 1.5.
After downloading the file from OTN, unzip the same.
You need to create a new tablespace for APEX application.
Create Tablespace for Oracle Application Express Database Objects:
SQL> CREATE TABLESPACE htmldb DATAFILE ‘/u01/app/orsbox/oradata/htmldb_01.dbf’ SIZE 1000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT auto;
Create Oracle Application Express Database Objects:
After the new tablespace has been created, the next step is to create all required database objects for Oracle Application Express. These database objects are all created by running a single SQL script that can be found in the Oracle Application Express software distribution downloaded earlier in this section. The SQL script to run is named apexins.sql and can be found in the apex directory created when unzipping the software distribution. For the purpose of this example, I ran the script as follows (note that this install script can take quite awhile to complete):
Change the directory to APEX directory you have unziped
[orsbox@ocvmrh2124 conf]$ cd apex
Connect to database as sysdba and run following command
@apexins welcome htmldb htmldb temp /i/ orcl
Below is the brief explanation for this command.
|Parameters to APEX Database Object Creation SQL Script
||The password for the Oracle APEX administrator account, the APEX schema owner (FLOWS_020200), the APEX files schema owner (FLOWS_FILES), and the APEX public user schema (APEX_PUBLIC_USER). The APEX schema owner is the user or schema into which Oracle Application Express database objects will be installed. The APEX files schema owner is the user or schema where uploaded files are maintained in Oracle Application Express.
||Name of the default tablespace for the APEX schema owner – (FLOWS_020200).
||Name of the default tablespace for the APEX files schema owner – (FLOWS_FILES). Note that Oracle Application Express creates a table namedFLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ in this tablespace used to store any uploaded files. Although this could have been a separate tablespace, it is just as easy having the all objects in a single tablespace.
||Name of the temporary tablespace to be used for all schemas created by the Oracle Application Express install process.
||Virtual directory that is used for images rendered by Oracle Application Express. To support future Oracle HTML DB upgrades, define the virtual image directory as/i/.
||Name of the Oracle Net connect string to the database where Oracle Application Express database objects are to be installed. If this is a local install, you can usenone or NONE.
During the Oracle Application Express install process, three schemas will be created. A description of each of these schemas is provided in the following table:
|Oracle Application Express Database Schema Accounts
||This is basically the schema owner of all objects (tables, views, packages, functions, etc.) used by Oracle Application Express. The only exception is the table installed in the FLOWS_FILES schema (FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$) for storing uploaded files. Note that this account is locked at the end of the installation and cannot be used unless it is first unlocked.
||This schema is used to store uploaded files to Oracle Application Express. These can include scripts, documents, cascading style sheets, etc. Note that this account is locked at the end of the installation and cannot be used unless it is first unlocked.
||This schema is used by Oracle Application Express to login to the database and has access to database objects in the above two schemas for all application functionality.
Recompiling Invalid PL/SQL Packages:
After installing the Oracle Application Express database objects, it is recommended (however not required) to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time.
Run the utlrp.sql script from the Oracle Database home:
Install Oracle Application Express Database Images:
After successfully creating all of the required Oracle Application Express database objects, the next step is to copy the necessary images, templates, cascading style sheets, themes, java scripts, (and several other file types) into the directory tree of the Oracle HTTP Server. The Oracle HTTP Server was installed earlier in this article and is located at:
$ORACLE_HOME = /u01/app/orsbox/OHS
The images and other file types to copy can be found in the Oracle Application Express software distribution downloaded earlier under the …/apex/images directory.
The files will need to be copied to the $ORACLE_HOME/Apache/Apache/images directory as illustrated in the following examples:
[orsbox@ocvmrh2124 orsbox]$ cd apex
[orsbox@ocvmrh2124 orsbox]$ cp -R images $ORACLE_HOME/Apache/Apache
Configure Database Access Descriptor:
Now that the Oracle Application Express images are in place, the next step is to configure the Oracle Database Access Descriptor (DAD) file. The configuration file is named dads.conf and should be located in the $ORACLE_HOME/Apache/modplsql/conf directory. The DAD is used by the Oracle HTTP Server and mod_plsql extension module to connect to the Oracle database.
Change the below text according to your setup and copy the below text to dads.conf
Parameters to be changed in below text are
Alias -> This is the alias for images directory under Apache Home
PlsqlDatabasePassword -> This is the password for APEX user, you can set any password here, which will be used further
PlsqlDatabaseConnectString -> This is the connect string for your database. This is of the form HOST.DOMAIN:PORT:DB_NAME
=============================================================== Alias /i/ “/u01/app/orsbox/OHS/Apache/Apache/images/”
AddType text/xml xbl
AddType text/x-component htc
Allow from all
For a complete description of each of the parameters used in the dads.conf configuration file, read through the file dads.README located in the same directory. The following table provides a brief description of the key parameters:
|Oracle DAD Configuration Parameters and Description
||This is set to the virtual directory you specified when running theapexins.sql file to create the Oracle Application Express database objects. This value must match the value you specified which is recommended to be /i/. The virtual directory should refer to the location where you copied the Oracle Application Express images to in the Oracle HTTP Server directory tree.
||This is the name of the virtual path that will be used to access application in Oracle Application Express. For example,http://linux3:7777/pls/apex/f?p=100.
||This is the database username that will be used by the mod_plsqlextension module to connect to Oracle database. The database username should be APEX_PUBLIC_USER – the schema created by theapexins.sql script. All connections coming from an Oracle Application Express application, regardless of their login id and password, will be connected to the database with this username. This even includes applications that use a different parsing schema! Consider an application that uses a parsing schema of DEV_WS. The session user will be APEX_PUBLIC_USER but the Current User will be set to DEV_WS:
sys_context('USERENV','SESSION_USER') "Session User"
, sys_context('USERENV','CURRENT_USER') "Current User"
Session User Current User
||This must match the password you provided during the creation of the Oracle Application Express database objects (@apexins.sql). This is the password used by the PlsqlDatabaseUsername (above) to connect to the Oracle database.
||The connection URL string used to connect to the Oracle database in the format server.domain:port:sid. If the database is located on the same server, you can use localhost.
Obfuscate the PlsqlDatabasePassword Parameter:
Notice that when we configured the dads.conf file, we put in the plain text password for the Oracle database user. Exposing plain text passwords is never good security practice, especially for this sensitive database account. Fortunately, Oracle provides an obfuscation utility that targets and encrypts the PlsqlDatabasePassword entry in the dads.conf file. This utility is named dadTool.pl and can be found in the $ORACLE_HOME/Apache/modplsql/conf directory.
Run the dadTool.pl utility as follows:
[orsbox@ocvmrh2124 orsbox]$ export ORACLE_HOME=/u01/app/orsbox/OHS
[orsbox@ocvmrh2124 orsbox]$ echo $LD_LIBRARY_PATH
[orsbox@ocvmrh2124 orsbox]$ PATH=$ORACLE_HOME/Apache/modplsql/conf:$PATH;export PATH
[orsbox@ocvmrh2124 orsbox]$ PATH=$ORACLE_HOME/perl/bin:$PATH:.;export PATH
[orsbox@ocvmrh2124 orsbox]$ LD_LIBRARY_PATH=$ORACLE_HOME/lib;export LD_LIBRARY_PATH
[orsbox@ocvmrh2124 orsbox]$ PERL5LIB=$ORACLE_HOME/perl/lib:.;export PERL5LIB
[orsbox@ocvmrh2124 orsbox]$ cd $ORACLE_HOME/Apache/modplsql/conf
[orsbox@ocvmrh2124 conf]$ perl dadTool.pl -o
Backed up older dads.conf as /u01/app/orsbox/OHS/Apache/modplsql/conf/dads.conf.orig.2007-07-13_09-04
All passwords successfully obfuscated. New obfuscations : 1
Restart the Oracle HTTP Server:
[orsbox@ocvmrh2124 conf]$ $ORACLE_HOME/opmn/bin/opmnctl restartproc ias-component=HTTP_Server
opmnctl: restarting opmn managed processes…
Now you can access your APEX URL: http://ocvmrh2124.us.oracle.com:7777/pls/apex