Oracle Application Express
Oracle Application Express
My introduction to Oracle Application Express was through the user comments on this blog. Though ii have not come across an implementation of Oracle Application server in any of my projects so far, I still decided to have a look at the product and its integration with Oracle Applications. I was both satisfied and a bit disappointed. I will discuss the reasons for both as i progress with this post.
Oracle Application Express or APEX was also formally know as HTML DB.APEX aims at consolidating and securing data while improving access by delivering data through a web browser.
Based on a thin client architecture APEX does not require any additional software on each desktops of the users, a supported web browser will suffice. APEX makes the communication with the database using the mod_plsql module of the Oracle Application Server.
APEX is tool which is aimed at both users and developers .For users who have limited or no programming experience APEX allows them to easily build reports, charts and upload data from spread sheets and text files into the database. It also allows developers to quickly build up forms, reports and making deployment a secure and less complex for database centric web applications.
Other benefits of APEX include.
- Web Services Integration : Blends well with web services by making use of SOAP and XLS style sheets.
- Workspaces: which allow multiple level of access within the same database for different users
- Template Driven UI: Application development of UI is made simpler by making use of in built templates.
- Email notifications: Using the UTL_SMTP package.
- Flexible Authentication: Using Single Sign On and native database type authentication methods supported.
- SQL Workshop Tools: These provide tools to view and modify the database objects within the web browser.
APEX Architecture
Oracle Applications Release 12 and APEX
Mod_plsql is planned to not included in release 12 of Oracle Applications, so does this mean APOEX would be unusable? well not really you would need to do a separate Application server installation to be able to use APEX in Release 12.
Installing APEX with Oracle Applications Release 11.5.10.2
Pre Requisites
The database should be 9i release 2 9.2 or higher
The version of Oracle Application server should be 1.0.2.2 or higher
Make sure you have an additional 500 MB of free space in your ORACLE_HOME mount point.
Oracle Text Requirement
Oracle Text which is installed by default as a part of the DB installation is required for using the Application express Help files. However you must install the default language for Oracle Text using drdefus.sql
$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.6.0 - Production on Thu Nov 16 15:25:22 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> @$ORACLE_HOME/ctx/admin/defaults/drdefus.sql
Creating lexer preference...
PL/SQL procedure successfully completed.
Creating wordlist preference...
PL/SQL procedure successfully completed.
Creating stoplist...
PL/SQL procedure successfully completed.
Creating default policy...
PL/SQL procedure successfully completed.
Download APEX software
Download the apex_2.2.1.zip from http://www.oracle.com/technology/products/database/application_express/download.html
Copy it to your ORACLE_HOME and unzip it
unzip apex_2.2.1.zip
Create a tablespace to hold your APEX schema and APEX files
Since you do not have a default schema to hold your APEX installation in Oracle Applications you must create a new tablespace with at least 200 MB to hold it.
$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.6.0 - Production on Thu Nov 16 16:59:31 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL>create tablespace APEX datafile '/u04/sam/orasam/samdata/apex01.dbf' size 200M
Installing APEX
To install APEX into the database you must run the apexins.sql script on the database on which you wish to load APEX.The script can be located at $ORACLE_HOME/apex/apexins.sql.The scripts takes six arguments as parameters.
password: Password you wish to give for your APEX schema (in my case XXXX)
apex_tablespace: The tablespace to hold APEX schema (in my case APEX)
apex_file_tablespace: Tablespace to hold APEX files (in my case APEX)
temp_tablespace: Temporary tablespace (in my case TEMP)
images: Virtual directory for Images (in my case /i/)
connect: Connect string.(in my case none as it is a local DB)
$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.6.0 - Production on Thu Nov 16 16:59:31 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> @apexins.sql xxxxx apex apex temp /i/ none
Post Installation Tasks
Copy The Images Directory
Copy the images directory at $ORACLE_HOME/apex/images(DB Tier) to $IAS_ORACLE_HOME/Apache/Apache (Applications Tier)
cp -Rf images $IAS_ORACLE_HOME/Apache/Apache
Edit wdbsrv.app
Edit the $IAS_ORACLE_HOME/Apache/modplsql/cfg/wdbsrv.app file and add the following
[DAD_apex]
connect_string = SAM (your connect string here)
password = XXXXX (password you gave while installing APEX)
username = apex_public_user
default_page = apex
document_table = wwv_flow_file_objects$
document_path = docs
document_proc = wwv_flow_file_mgr.process_download
reuse = Yes
enablesso = No
stateful = STATELESS_RESET
nls_lang = American_America.AL32UTF8
Edit the httpd.conf
Edit the $IAS_ORACLE_HOME/Apache/Apache/conf/httpd.conf file and add the alias
Alias /i/ "/u03/sam/applsam/samora/iAS/Apache/Apache/images/"
Bounce your HTTP Server
Restart your HTTP server to have the changes take effect.
$cd $COMMON_TOP/admin/scripts/SAM/
./adapcctl.sh stop
./adapcctl.sh start
Logging in to APEX
You can use the following URL to login to APEX
http://<hostname>.<domain>:port/pls/apex
Use the username:admin and the password you gave during the install of APEX
First time users may need to navigate to the APEX administrators screen to do setup
There is a possibility that you may get login window with to-be-defined realm login window, the solution though not documented is quite simple edit the wdbsrv.app file and find an entry like
custom_auth = CUSTOM
add a comment in front of this entry
#custom_auth = CUSTOM