25 August 2017

Oracle Application Express – Introduction to APEX


1.1. What is Oracle Application Express (APEX)?

Oracle APEX is a web application development framework that is centralized in a database. It allows you to quickly and conveniently design, develop and execute applications supported by Oracle database, using only a web browser.

APEX is available by default in all Oracle database installations, including the Express version.

1.2. Use Cases

One of Oracle APEX's goals is to simplify user-tool interaction so that it can be used by all employees in a company, not just back-office IT teams.

It is widely used to quickly build applications that require urgent changes due to business changes.

It enables to migrate existing solutions in file-based or old-business applications, making the new solution available on the web for all users centrally, securely and robustly.

1.3. Historic evolution


2.1. 3 Layer Architecture

Oracle APEX uses a simple architecture divided into 3 layers: Web Browser, Web Server and Database with APEX included.

Requests triggered by the web browser are sent through the intermediate layer (Web Server) to run on the APEX engine and database. After the processed information is returned a response to the web browser in HTML format.

There is no manipulation of data in the intermediate layer, this task is performed exclusively by the APEX engine next to the database through interaction with the schemas.

In the middle tier (Web Server) Oracle prefers to use Oracle REST Data Services (ORDS) running on a Web Logic Server (other possible solutions: Glassfish or Tomcat). Oracle HTTP Server (OHS) is typically installed ahead of ORDS to act as a proxy.

2.2. Links to Data Sources

APEX performs best when it shares the same Database where the source data to be used in the developed applications is.

However, you can interact with remote data sources through the use of Web Services (REST and SOAP) or through Database Links to other databases.

By using ORDS you can make Web Services available from the Oracle database.


3.1. User Authentication

Oracle APEX is prepared to use various methods of authentication of its users, the main ones being:

  • LDAP – Authentication performed based on the company’s directory server;
  • Oracle APEX – Authentication is performed based on users created in the APEX tool itself;
  • Database – Authentication is performed with users configured in the database.

User Profile

  • Workspace Administrator (inherits the privileges of Developer and End User)

 User management

 Workspaces Management

 Management of the instance

  • Developer (inherits End User privileges)

 Creating and Editing Applications

 Access to schema reports

 Activity monitoring

  • End User

 Only has access to applications made available by Developers


3.2. Workspace

A Workspace is a private virtual space that allows multiple APEX users to work within that same space, thereby maintaining the privacy of their objects, data and applications within the group of users with access to that workspace.

It allows to define at the application level a connection with the source data that will be available for use within the workspace, through the identification of which database schemas that this workspace will have access to. The relationship between workspaces and schemas is N-N.

Users (developer or end user) can, at any time, request changes to existing accesses or creation of new accesses to other schemas. This management is performed exclusively by users with a workspace administrator profile.

3.3. Metadata

The APEX metadata is stored in its own schemas for this purpose in the database. These schemas are created automatically with the APEX installation.

The metadata schema nomenclature is based on the installed APEX version, for example: APEX_040200 or APEX_050000.

3.4. Development Lifecycle

In situations where there are several APEX environments (for example: Development, Testing and Production) it is possible to quickly export and import APEX applications between them.

To export an application, the developer uses the Export component in the Application Builder module. This action generates a file with an SQL script.

The developer then informs the person responsible for the environment that will receive the application of the availability of the previously generated SQL file.

The new environment manager imports the file through the Import component, or alternatively executes the SQL script directly in the database.


4.1. Application Builder

Integrated Development Environment that allows you to configure web applications, due to a wide range of components already in the solution: navigation control, forms, reports, etc.

It is in this module that the development team spends most of the time creating, maintaining and improving their applications.


4.2. SQL Workshop

It allows to view objects of the schemas to which the workspace has access, seeing the universe of tables or the detail of the columns of certain table for example.

You can change and perform maintenance tasks on database objects by executing SQL statements (select, insert, update, delete, etc.).

The features of this module are a subset of the Oracle SQL Developer product.


4.3. Team Development

It is a collaborative project management tool integrated in APEX, which allows a better monitoring of the state of the project, giving visibility to the details associated with the applications being developed.


4.4. Packaged Applications

It is a set of pre-built applications, available for use quickly and productively.

It allows the user to practice some of the many potential features of APEX, and can later apply this knowledge during the development of their applications.

This module is constantly being updated and enriched with new content in every update of the APEX version.



5.1. Oracle Virtual Store

5.2. Oracle Human Resources Directory

Oracle's internal application, where all the information about its employees is centralized.

Workers can update their information through the application, add or update photo, add links, etc.

It is used to search and contact workers, and displays an average of 1.5 million pageviews per day, with the average response time being 0.04 seconds.

5.3. Oracle Team USA

Navigators have access to an APEX based Mobile Application that allows you to automate the validation of a list of 250 items in the preparation of the vessel for navigation.

     Ricardo Simões


    Carlos Cardoso