Product Description

Oracle Application Express (Oracle APEX), formerly called HTML DB, is a rapid web application development tool for the Oracle database. Using only a web browser and limited programming experience, you can develop and deploy professional applications that are both fast and secure. Oracle Application Express combines the qualities of a personal database, productivity, ease of use, and flexibility with the qualities of an enterprise database, security, integrity, scalability, availability and built for the web. Application Express is a tool to build web-based applications and the application development environment is also conveniently web-based.

Known Versions

• 2.1
• 2.1.2
• 3.0
• 3.1
• 3.1.2
• 3.2
• 4.0
• 4.1
• 4.2
• 5.0

Software Pattern Summary

Platforms Supported by the Pattern

The pattern identifies the product on Windows and Unix platforms.

Identification

Software Instance Triggers

Versioning

Two methods of versioning for the Oracle Application Express Detail are tried.

Standard Query

The following query is run on the Oracle Database, version information is extracted from the results

• select version, status from dba_registry where comp_name like 'Oracle Application Express'

Schema Query

This method is only tried is the Standard Query fails

The following query is run on the Oracle Database

• select username from dba_users where username like 'FLOWS_%'

The output of this query is parsed using the following regular expression

• 
  FLOWS_\d(\d)\d(\d)\d(\d)

The version is taken to be $1.$2.$3

Software Instance Versioning

The Oracle Application Express Software Instance obtains its version information from the Oracle Application Express Detail it triggers from

Connection to the Oracle Database

Configuring the Oracle Database to enable Foundation access

The following steps should be performed on the Oracle Database in order to create a user with read privileges:

• Access your Oracle SID via SSH: sqlplus SYS/<SYS_user_password> as SYSDBA
• Create a user: create user <user> profile default identified by <password>;
• Grant Oracle SID connection to the user: grant connect to <user>;
• Grant read permissions to this user for specific tables: grant select on dba_registry to <user>; grant select on dba_users to <user>;
• exit SQLPlus: disconnect;

Configuring Foundation to access the Oracle Database

It is necessary to insert Oracle connection details into the Foundation appliance to enable it to run queries on the host during discovery process. For information on how to configure Foundation to access the Oracle database please refer to these documents
The Software Credential Group where the credentials should be entered is 'Oracle Applications'.

Port used to access database

By default the pattern obtains the port to access the Oracle Database via the Oracle Database Software Instance.

It is possible for the user to configure the pattern to override this default. In the pattern configuration section, set use_default_port to be True, then specify the default_port to be used

Application Model Produced by Software Pattern

Product Architecture

Oracle Application Express has its own scheme installed within Oracle SID. In order to run, Oracle APEX must have access to either the embedded PL/SQL gateway or Oracle HTTP Server and mod_plsql.

Additional Attributes

Status

The Detail pattern attempts to obtain the status by running the following query

• select version, status from dba_registry where comp_name like 'Oracle Application Express'

The Software Instance pattern obtains the status information from the Detail it triggers on

Enabled

The Detail pattern sets Enabled to be true if the following query can be run successfully

• select version, status from dba_registry where comp_name like 'Oracle Application Express'

If this fails, the Detail pattern sets Enabled to be true if the following two conditions are satisfied:

• The query "select username from dba_users where username like 'FLOWS_%'" runs successfully and gives a username that matches regular expression 'FLOWS_\d+$'
• The query "select DBMS_XDB.GETHTTPPORT from DUAL" runs successfully and gives a port number greater than zero

The Detail pattern sets Enabled to be false if the following two conditions are satisfied

• The query "select username from dba_users where username like 'FLOWS_%'" runs successfully and gives a username that matches regular expression 'FLOWS_\d+$'
• The query "select DBMS_XDB.GETHTTPPORT from DUAL" does not run or gives a zero value for a port number

In all other cases the Detail is aged or destroyed

Port

The Detail pattern attempts to obtain port information by running the following query:

• select username from dba_users where username like 'FLOWS_%'

The Software Instance pattern obtains port information from the Detail it triggers on

Software Pattern Model

This pattern triggers on a Detail node, which has "Oracle Application Express" type. Since only one installation of this software may be running per Oracle SID, a simple SI key containing the 'APEX Oracle SID', 'type' and 'host key' attributes is used on creation of the Software Instance.

Ageing the Detail and Software Instance

The Detail and Software Instance will be aged if both of the following queries fail:

• select version, status from dba_registry where comp_name like 'Oracle Application Express'
• select username from dba_users where username like 'FLOWS_%'

By default a Software Instance or Detail that is aged will be destroyed after seven unsuccessful attempts at database access. This value can be changed using the pattern configuration.

Destroying the Detail and Software Instance

The Detail and Software Instance will be immediately destroyed if:

• The following query fails: select version, status from dba_registry where comp_name like 'Oracle Application Express'
• The following query gives a username that does not satisfy the regular expression FLOWS_\d+$ : select username from dba_users where username like 'FLOWS_%'

Relationship Creation

Pattern module creates dependency relation between the Oracle Application Express Software Instance and an Oracle Database Software Instance that contains the Oracle Application Express Detail node.

Subject Matter Expertise

Subject Matter Expert input will be welcome on any other potential approaches not discussed to improving product versioning coverage and depth of Oracle Application Express.

Testing

Current patterns had been tested on 'Windows 2003 server' and on Linux platforms to ensure that all methods of obtaining the version are working and and the Detail and SI are being created with defined attributes.

Information Sources

http://download.oracle.com/docs/cd/E10513_01/doc/install.310/e10496/trouble.htm http://www.oracle.com/technology/products/database/application_express/html/what_is_apex.html

Open Issues

There are no known open issues with this pattern.

Created By: Olexander Kashkevich (20 Feb 2009)
Reviewed By: Chris Blake (5 Mar 2009)
Updated By: Chris Blake (9 Nov 2010)