Oracle Apex Installation and Configuration in EBS

Introduction & Software

Apex 3.1 works with all versions above 9203, (including Oracle 11g). In this article the steps are defined for Installing apex_3.1 on 10gR2 (10.2.0.3)
Download All the Software required for Installation:

· Download 10gR2 Companion CD (for HTTP Server) (http://www.oracle.com/technology/software/products/database/index.html)

· Download apex_3.1 from http://apex.oracle.com or http://www.oracle.com/technology/products/database/application_express/download.html

Installation Overview

Oracle Application Express (Apex) installs into the Oracle data base and uses mod plsql to handle requested from the Oracle Application server through to the Oracle database. The installation process involves installing the software then configuring the HTTP server to recognise the application.

Prerequisite for Apex Installation

· Install 10gR2 software and create a general database as part of installation

· Install 10gR2 companion CD – you will have two options (HTMLDB + HTTP Server or only HTTP Server) (I selected only HTTP Server)

The Oracle HTTP server is a simple Web HTTPD server (Web listener). It is based on the Apache Web Server provided by the Apache Group (www.apache.org). Both the Oracle database (8.1.7 and above) and Oracle iAS (Oracle Internet Application Server) ships with the Oracle HTTP Server.

The following virtual servers are predefined when one install the Oracle HTTP Server:

· HTTP Server Home Page - http://machine-name:7777/ for the Apache, mod_plsql, mod_ose, mod_JServ, and JSP documentation.

· Oracle Enterprise Manager (OEM) Web_stage - http://machine-name:3339/ for Oracle Enterprise Manager Console and applications.

Please note that these ports might be different on your system. Check the httpd.conf file to obtain the correct port numbers.

· Stop both oracle DB and Apache server just installed.

· Start the DB and let apache be down.

Apex Installation:

• DB Preparation

An standard Oracle 10g database is configured to support Oracle Apex, the only thing that needs to be created is a tablespace for the Apex application:

CREATE TABLESPACE apex01

DATAFILE 'E:\oracle\product\10.2.0\oradata\d02\apex01.dbf'

SIZE 100M

AUTOEXTEND ON NEXT 100M

MAXSIZE 1000M;

• Installing Oracle Apex

Navigate to the apex directory and launch SQLPLUS, login as SYS:

C:\> CD e:\apec

E:\apex> sqlplus sys/ as sysdba

Run apexins.sql passing the following six arguments in the order shown:

@apexins tablespace_apex tablespace_files tablespace_temp images

Where:

apex_tablespace: The tablespace to hold APEX schema (apex01)
apex_file_tablespace: Tablespace to hold APEX files (apex01)
temp_tablespace: Temporary tablespace (temp)
images: Virtual directory for Images (/i/)[1]

The following examples demonstrate running apexins.sql and passing these

arguments:

@apexin apex01 apex01 TEMP /i/

This script will install Oracle Apex and create the user APEX_PUBLIC_USER as part of the process, this user will be used in section 3.2.2.

· Change Password for ADMIN Account

- Change directory to apex
- Connect to DB as sys user and execute
- @apxchpwd

(ADMIN user is the super user account simillar to SYS for DB)

Configuring HTTP Server

The Apex application will be accessed via an Oracle HTTP server using modplsql, to enable this connection two set up processes need to be completed:

1. Copy the images directory

2. Configure the.conf files

· Unlock APEX_PUBLIC_USER Account & change password

C:\> sqlplus sys/ as sysdba
sql>alter user apex_public_user account unlock;
sql>alter user apex_public_user identified by NEW_PASSWORD;

• Copy the Images Directory

After installation the Apex software, copy the directory apex/images directory to the HTTP sever

xcopy /E /I E:\apex\images E:\oracle\product\10.2.0\ohs\Apache

• Stop HTTP server:

Before configuring the HTTP server all processes should be stopped:

C:\oracle\product\10.1.3.1\OAS\opmn\bin\opmnctl stopall

• Update DADS.conf

The following entry will need to be added to the DADS.conf to link Oracle Apex to the HTTP server:

E:\oracle\product\10.2.0\ohs\Apache\modplsql\conf

Alias /i/ "E:\oracle\product\10.2.0\ohs\Apache\images"

AddType text/xml xbl

AddType text/x-component htc

Order deny,allow

PlsqlDocumentPath docs

AllowOverride None

PlsqlDocumentProcedure wwv_flow_file_mgr.process_download

PlsqlDatabaseConnectString :1521:oracleserviceFTSPROD ServiceNameFormat

PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8

PlsqlAuthenticationMode Basic

SetHandler pls_handler

PlsqlDocumentTablename wwv_flow_file_objects$

PlsqlDatabaseUsername APEX_PUBLIC_USER

PlsqlDefaultPage apex

PlsqlDatabasePassword

PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize

Allow from all

• Update httpd.conf

Add the following line to httpd.conf:

E:\oracle\product\10.1.3.1\Ohs\Apache\Apache\conf\httpd.conf

# Include the Oracle configuration file for Apex

include "E:\oracle\product\10.1.3.1\Ohs\Apache\modplsql\conf\dads.conf"

• Start HTTP server:

E:\oracle\product\10.1.3.1\OAS\opmn\bin\opmnctl start

Oracle Application Express should now be available from the following URL:

http://:7777/pls/apex

Issue Resolution

If the Apex login screen fails to load the opmn log files and Apche log files should be reviewed to confirm the configuration has run as expected. These log files can be found at the following locations:

Opmn logs: E\oracle\product\10.2.0\ohs\opmn\log

Apche logs: E\oracle\product\10.2.0\ohs\Apache\Apache\log

CODING :-

SQL>@apexins.sql apex apex temp /i/


1. Unlock the APEX_PUBLIC_USER account
alter user APEX_PUBLIC_USER account unlock
alter user APEX_PUBLIC_USER identified by (new password)


Adding DAD entry in dads.conf file (in $IAS_ORACLE_HOME/Apache/modplsql/conf/)

Alias /i/ “[ Put the IMAGE_LOC Value] “ as $IAS_ORACLE_HOME/Apache/Apache/images
AddType text/xml xbl
AddType text/x-component htc

<Location /pls/apex>
Order deny,allow
PlsqlDocumentPath docs
AllowOverride None
PlsqlDocumentProcedure wwv_flow_file_mgr.process_download
PlsqlDatabaseConnectString hostname:port:SID (here my entry is muthu:1521:apex (MY SID Name is apex)
PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
PlsqlAuthenticationMode Basic
SetHandler pls_handler
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDatabaseUsername APEX_PUBLIC_USER
PlsqlDefaultPage apex
PlsqlDatabasePassword APEX_PASSWORD (here my entry is welcome)
PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize
Allow from all