Infosys’ blog on industry solutions, trends, business process transformation and global implementation in Oracle.

« Zero Based Budgeting - A Rigorous way of Cost Optimization | Main | Hadoop Overview »

Building Apex applications on Oracle Database

Oracle Application Express ( Oracle APEX )

What is Oracle Application Express

Oracle Application Express widely  referred as Oracle APEX also, is a  feature provided by Oracle on its Database which enables user build web based applications using SQL and PL/SQL.  Any developer with SQL and PL/SQL skills can  design, built and deploy database-driven applications using its browser based user interface. APEX features such as pre-built user interface themes (templates), navigational controls and form handlers among others makes application development fast and easy  and also  eliminates dependency  on UI developers/experts.

Apex Architecture

Apex is installed /configured  on an Oracle database instance and consists of data in the schema and PL/SQL code written for the business logic of application.


Apex application and page are accessed from browser. These requests are then channeled through the middle-tier for execution in the Oracle Database and returned as HTML responses to the browser.

Mid tier plays no role in data manipulation or processing, it is the  Oracle APEX engine which ( a part of Oracle Database) accepts  page, and interacts with the schemas in the database.

The mid-tier can be

1. Oracle REST Data Services (ORDS) running on a Java Server ( Oracle Weblogic Server, Oracle Glassfish, Tomcat )

2. Oracle embedded PL/SQL Gateway (EPG).

3.  Oracle HTTP Server ( with mod_plsql ).


Advantage Apex

It is a good alternative to Oracle forms.

Leverages oracle database capabilities and sql and PL/SQL.

Capability to load data in database tables from .csv and .xlx files.

No separate license required, it comes free with Oracle DB.

Easy to develop application on apex and deploy across environments.


Apex Integration with OBIEE

 Lets first understand apex URL syntax and what each parameter in the syntax does.

Syntax of an APEX page URL follows the format as below:



http://  - It can  be either of  http or https protocol.

host_name  -  domain/host/server, it can also be localhost. IP address and port number can  be part of it as well.

/pls - This indicates use of Oracle HTTP Server.

/apex - This is refers to name of Database Access Descriptor (DAD). DAD describes how the HTTP server connects to DB server to service  HTTP request ( default value being apex ).

/f?p -  f here public  procedure and main entry point to APEX, and  'p' is parameter to it. (the entire string which consists of below is parameter to 'f').

:AppId - This refer to  application Id or alphanumeric alias of apex application being accessed.

:PageId - This is  page number or alphanumeric alias of page which is called in the URL ( Every page is part of an apex application ).

:Session - This is apex  unique Session ID. It can be empty (then APEX creates a new Session) or set to 0 for Public Pages.

:Request - This is a keyword and used to set value of REQUEST and control page behavior. On click  application button sets the value of REQUEST to the name of the button.

:Debug --  This is used to enable or disable debug mode for the page. Valid values are YES or NO.  

:ClearCache --  This is used to clear cache and set values of page items to null.  Provide list of page ids for which cache is to be cleared.

:Parameters -  Multiple Parameters (page items separated by comma) can be passed from URL  to apex age for page items as well as application items

 :ParamValues -  This contains values (comma separated in case of multiple parameters) for the parameter list in the above. Values cannot have comma and colon as part of it.

:PrinterFriendly - To  render an apex  page  in printer friendly  mode, set value as 'Yes' for this parameter.


An apex application page can accessed  from  or integrated with OBIEE in multiple ways:

1.  Calling apex page from report column using interaction feature.


We can define parameter names and respective values. Value can be hardcoded, column value from the report or value of a variable.


2. Apex page embedded in OBIEE dashboard.  


How and where we used Apex

We  can built simple as well complex application using apex.

 We Automated  some of the manual ( dependent ) tasks and integrated to OBIEE.

  1. Apex base application to manage Business reference data : Using this apex page authorized user can manage (modify , create, edit ) , refer to business metadata in the database table and reducing the dependency on DBA/ backend team.


2.Application to retarget orders due for fulfillment.

Apex application was created to retarget current and subsequent stages of an under progress order and capture the audit information of retarget.


3. Created an apex application page to provide user ability to add comments/inputs for delayed orders which can be seen by different stakeholders. This task was performed over emails using microsoft excel.


Good Information

URL syntax is well explained.

Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)

Please key in the two words you see in the box to validate your identity as an authentic user and reduce spam.

Subscribe to this blog's feed

Follow us on

Blogger Profiles