Installing Apex (3.2.1) along with OHS (11g iAS_10.1.3.3_Apache2_Modplsql2)


introduction

below you’ll find an installation procedure for apex along with just one variant of the available multitude of web connectors, here ohs (oracle http server aka apache) shipped with 11g. the post heavily sources from this article (in german), which is very much appreciated, but does regard oracle xe only and will not function for a 10g non-xe. futhermore, this article saved my day in pointing out what a non-xe 10gR2 dislikes in regard to dads.conf, the mod_plsql dad configuration file.

installation versions and prerequisites

ok, let’s go straight. the latest version of the 3.x release of apex, which is 3.2.1, can be downloaded here. indeed, apex version 4.0 has already been brought online but i want to eat that banana now. ohs is available as a standalone labelled 10.1.3.3 with the 11g distribution here or as a part of the companion media of ias 10gR3 versioned 10.1.3.5 here. i preferred to employ the first packaging, solely triggered by download time resources. the apex code will live inside an oracle 10gR2 10.2.0.3.

apex 3.2.x does define some installation requirements as set forth in this document. i already have oracle xdb, oracle text and things such that i only needed to update owa (pl/sql web toolkit).

installing apex

firstly do extract the apex source stack (apex_3.2.1.zip) into some persistent location. me, i used %ORACLE_HOME%\apex. from there on do update owa to at least version 10.1.2.0.6.


shell> cd %ORACLE_HOME%\apex\owa
shell> sqlplus "/ as sysdba"
SQL> select owa_util.get_version from dual;
SQL> 10.1.2.0.5
SQL> @owainst.sql
SQL> ...
SQL> select owa_util.get_version from dual;
SQL> 10.1.2.0.6
SQL> exit

then change back one dir level up and start the actual apex installation. the parameters given to the script are default values that i did’nt need to change so far anyhow. the installation process takes considerable time, around 30 minutes on an office box, so please be patient. there will be a summary of http-addresses against apex near the end of the script’s output log.


shell> cd %ORACLE_HOME%\apex
shell> sqlplus "/ as sysdba"
SQL> @apexins SYSAUX SYSAUX TEMP /i/
SQL> 10.1.2.0.5
SQL> @owainst.sql
...
SQL> Thank you for installing Oracle Application Express.
SQL> Oracle Application Express is installed in the APEX_030200 schema.
SQL> The structure of the link to the Application Express administration services is as follows:
SQL> http://host:port/pls/apex/apex_admin (Oracle HTTP Server with mod_plsql)
SQL> http://host:port/apex/apex_admin (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
SQL> The structure of the link to the Application Express development interface is as follows:
SQL> http://host:port/pls/apex (Oracle HTTP Server with mod_plsql)
SQL> http://host:port/apex (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
SQL> JOB_QUEUE_PROCESSES: 15
...
SQL> exit

you’re finally, concerning apex, asked to interactively provide a password for the apex-admin-user, which is admin to the gui and APEX_030200 in the database. the apex-web-front-user, which is APEX_PUBLIC_USER in the database, needs to be unlocked and resigned as well (you see, i’m not that much concerned about password crack stability because this is an inhouse development system). do remember both passwords, they will be needed later.


shell> sqlplus "/ as sysdba"
SQL> @apxchpwd
SQL> ...
SQL> ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;
SQL> ALTER USER APEX_PUBLIC_USER IDENTIFIED BY APEX;
SQL> exit

installing ohs

the ohs source stack (iAS_101330_Apache2_Modplsql2_win32.zip) can be extracted into some temporary location as it can be deleted after the installation. do simply start the setup.exe below the Disk1-directory which will launch oracle universal installer as usual. be carefull when setting the values for the oracle home and the oracle home directory in that you do not install into an existing oracle home. please also, if you like, regard the different versions of the oracle db and the ias product line. to cast it out visually, i already had OraDb10g_home1 pointing to F:\oracle\product\10.2.0\db_1 and in this step added OraAs10_home1 pointing to F:\oracle\product\10.1.3\as_1. when finished, oracle universal installer provides another summary of docu- and http-links. you can now exit oracle universal installer.


Folgende Informationen sind verfügbar in:
f:\oracle\product\10.1.3\as_1\install\setupinfo.txt
Verwenden Sie den folgenden URL, um auf den Oracle-HTTP-Server und die Willkommenseite zuzugreifen:
http://myhost:7777
Verwenden Sie den folgenden URL für den Zugriff auf Oracle Enterprise Manager Application Server Control:
http://myhost:1810

ohs furthermore needs some configuration concerning the setup of the apache-mod_plsql-module for apex application. this is done by editing the dads.conf-file within the %IAS_HOME%\ohs\modplsql\conf directory (note that dad actually reads database access descriptor using the web-servers root-directory /pls as an indicator to use the mod_plsql cartridge and being named apex, that is /pls/apex is the service root described below). it is initially empty and after population, the following worked for me.

working means, it worked at the end of the day, because only the web advised me to comment the line that is outlined _do_not_use_me_on_10.2.0.3_ below. i do not know why this is nessecary …

also, the file contains the password of the apex-web-front-user, which is APEX_PUBLIC_USER, in clear text. you may help in obfuscating this information by employing the dadTool.pl-perl-script, residing in the same directory as dads.conf.

# ============================================================================
#                     mod_plsql DAD Configuration File
# ============================================================================
# 1. Please refer to dads.README for a description of this file
# ============================================================================

# Note: This file should typically be included in your plsql.conf file with
# the "include" directive.

# Hint: You can look at some sample DADs in the dads.README file

# ============================================================================

Alias /i/ "F:/oracle/product/10.1.3/as_1/ohs/images/"
AddType text/xml xbl
AddType text/x-component htc
<Location /pls/apex>
  SetHandler pls_handler
  Order deny,allow
  Allow from all
  AllowOverride None
  PlsqlDatabaseUsername APEX_PUBLIC_USER
  PlsqlDatabasePassword APEX
  PlsqlDatabaseConnectString myhost:1521:mysid SIDFormat
  PlsqlDefaultPage apex
  PlsqlDocumentTablename wwv_flow_file_objects$
  PlsqlDocumentPath docs
  PlsqlDocumentProcedure wwv_flow_file_mgr.process_download
  PlsqlAuthenticationMode Basic
#  _do_not_use_me_on_10.2.0.3_ PlsqlRequestValidationFunction www_flow_epg_include_modules.authorize
  PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
  PlsqlErrorStyle DebugStyle
</Location>

you’ll need to bounce ohs for the changes to take effekt, i.e. reloading / rebinding the apache module with the new configuration. there are three control scripts available supporting this, in detail {start, stop, status}.


shell> %IAS_HOME%\opmn\bin\opmnctl startproc ias-component=HTTP_Server
shell> %IAS_HOME%\opmn\bin\opmnctl stopproc ias-component=HTTP_Server
shell> %IAS_HOME%\opmn\bin\opmnctl status

finally, for ohs, provide all the images stuff, external to the apex-db-installation at a location visible the web server. this location is defined by the alias /i/, twicely employed when running the @apexins-script for apex and configuring dads.conf for ohs (see the first active line). that is, actually, do copy %ORACLE_HOME%\apex\images to %IAS_HOME%\ohs\images manually.

you may generally test for a successful ohs installation by pointing your browser to the ohs default and welcome page http://myhost:7777.

first apex steps

your first step with apex is probably checking out the given http-links for the dev- and the admin-area. since this approach uses ohs with mod_plsql this is http://myhost:7777/pls/apex and http://myhost:7777/pls/apex/apex_admin, respectively.

the next step is usually the creation of an apex workspace. the apex workspace concept has been described with the oracle documentation as follows:

A workspace is a virtual private database allowing multiple users to work within the same Oracle Application Express installation while keeping their objects, data and applications private. Each workspace has a unique ID and name.

after logging on to http://myhost:7777/pls/apex/apex_admin, using the apexadmin credentials, there is a workspace creation wizzard with the administration interface that is well self-explanatory. note however, that a workspace creation implies a binding to a database schema which may in turn create a new db schema and tablespace (iff you just start from scratch). also, a workspace may be accessed by someone in two distinct roles, as an administrator and as a developer. such a way, it may be advisable to create at least two apex users for a single workspace in advance, some admin and some dev guy.

after workspace creation have a try at logging into that new workspace as the dedicated workspace-admin. you may notice that a sample application has already been installed with the new workspace. get acquainted with all the high and low level objects like items, pages, themes, regions etc.

conclusion

installing went on relatively easy. care must be taken to select the appropriate modules and versions from the oracle product stack. however, this is little effort in regard to what is available now with apex: an overall (up to logging and versioning) web-based and db-backed rad-tool for browser-app-development. blog you next time.

have fun

updates

  1. regard this post iff you ever got problems with obviously missing breadcrumbs rob wrote: “I found out when I select Tabular View, page 8 comes up in the breadcrumb REPORTS, but when in Hierarchial View, it does not show.” … “There seem to be a bug in APEX then. It should have been displayed in the hierarchical view as well as the tabular view.”. i say: there is a bug in apex, be attentive.
  2. i also liked the article from david peake about the interactive report template, originally published with oracle magazine.
  3. morten braten has a great article in his adventures with apex series, part 2, about running the installed apache (oracle process manager) as a windows service. do read it down to the end, including comments, there is some case-sensitivity detail that you should not miss.
Advertisements

2 comments

  1. Hi,

    do you know the reason why PlsqlRequestValidationFunction doesn’t work on 10.2.0.3 ?

    For security reasons i would recommend to upgrade your database to a more current patchset and use PlsqlRequestValidationFunction, because if you don’t use this option in dads.conf EVERY public Procedure in your database can be called from a browser.

    Peter

    Like

    1. hi peter,

      no, i do not yet know the root cause of the depicted problem. so far, apex is only used inhouse here so i do not care a lot about security and stuff. but however, you are right at last. regards peter

      Like

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s