Dec 26, 2009

Apex- Oracle’s Best Kept Secret

What is Oracle Apex?

Oracle Application Express (Oracle Apex, previously named Oracle HTML-DB) is a freeware software development environment based on the Oracle database. It allows a very fast development cycle to be achieved to create web based applications.

APEX is Oracle’s answer to wizard-based Web Development. It certainly is full of wizards! APEX contains metadata (lots of it) on everything in the tool, too. It produces dynamic HTML and it’s fast! APEX uses the PL/SQL module for Apache (mod_plsql). It doesn’t use any Java code. Anything you can do in SQL or PL/SQL can be done using APEX. APEX provides a nice Web-based team development environment for your organization.

If you’re an Oracle developer at heart (which means you know SQL and PL/SQL really well), Apex takes the mystery out of Web development. Write a query and view it in a browser! The data can be viewed as an HTML table (i.e. a report) or an Adobe Flash graph. Data can easily be edited in forms. A page can be made up of any number of components (i.e. charts, reports, forms, etc.). If you would like to execute a PL/SQL procedure or function before or after your page displays - fear not, Apex can easily handle this task with its processes.

Oracle Apex can be installed in an Oracle 9.2 or higher database, and starting from Oracle 11g it will be preinstalled along with the database.

Apex 3.1. includes a new major feature known as Interactive Reporting, which enables end-users to extensively customize a report without programmer intervention, using techniques such as filtering, sorting, group-by, choosing displayed columns, etc. The user can even save multiple versions of their customized reports. The programmer can limit which features are enabled.

APEX’s origin

One popular misconception is that it's a new version of Web DB. Mike Hichwa created Web DB, a successful web front-end for Oracle, but the development of Web DB started to move in a direction that diverged from Mike's vision. When tasked with building an internal web calendar, Mike enlisted the help of Joel Kallman and started "Flows". They co-developed the Web Calendar and Flows for several years, adding features to Flows as they needed them to develop the calendar. In the earliest days of Flows, there was no front-end for it, so all changes to an application were made in SQL*Plus via inserts, updates and deletes. In some ways APEX is an evolution of Web DB, but it was really a fresh start with new code and no upgrade path.

One of the most well known applications developed in Application Express is the AskTom application developed by Thomas Kyte. Oracle's online store also runs on APEX.

Below are the links:

Oracle Apex Architecture

Oracle Application Express consists of a metadata repository that stores the definitions of applications and an engine (called the Application Express engine) that renders and processes pages. It lives completely within your Oracle database. It is comprised of nothing more than data in tables and large amounts of PL/SQL code. The essence of Oracle Application Express is approximately 215 tables and 200 PL/SQL objects containing 300,000+ lines of code.

The Application Express engine performs:

  • Session state management
  • Authentication services
  • Authorization services
  • Page flow control
  • Validations processing
  • Rendering and page processing

The asynchronous session state management architecture ensures the minimal CPU resources are consumed. The browser sends a URL request that is translated into the appropriate Oracle Application Express PL/SQL call. After the database processes the PL/SQL, the results are relayed back to the browser as HTML. This cycle happens each time one either requests or submits a page. The session state is managed in the database and does not use a dedicated database connection. Each page view results in a new database session, thus database resources are only consumed when the Application Express engine processes or renders a page.

The Application Express engine is accessed from a Web browser through the Oracle HTTP Server (Apache) and mod_plsql. Applications are rendered in real time from the metadata repository stored in database tables. Building or extending applications does not cause code to be generated, instead metadata is created or modified.


With Oracle Database 11g you can replace the Oracle HTTP Server (Apache) and mod_plsql from the architecture with the Embedded PL/SQL Gateway (EPG). The EPG provides the Oracle Database with a Web Server and also the necessary infrastructure to create dynamic applications. The EPG runs in the XML Database HTTP Server, part of the Oracle Database, and includes the core features of mod_plsql, but does not require the Oracle HTTP Server powered by Apache. Oracle Database 10g Express Edition (XE) also utilizes the EPG.


APEX components

APEX has a number of components, including:

  • SQL Workshop
    • Interact with your database as with SQL*Plus, but visually
    • Data dictionary and object browsing, query by example
  • Utilities
    • Load and extract data from the database
    • Turn a spreadsheet into a table in a few seconds
    • Generate DDL
    • Object reports
    • Monitor the DB and the applications
  • Application Builder
    • Centerpiece of APEX
    • Loaded with wizards
    • Created reports, forms and charts
    • Connect pages using branches
    • 75 predefined widgets
    • Basic HTML, popup lists, calendars, etc.
    • Full data entry validation

Oracle APEX Pros & Cons


  • Fast development.
  • 100% web based
  • Ready to use components
  • Professional looking
  • Easy to create mock-ups
  • Easy to deploy (end user just needs to open an URL to access an Apex application)
  • Easy to understand
  • Fast (no overhead)
  • Easy to scale
  • All processing, validations are done in server side
  • Strong and supportive user community (especially Oracle Apex forum)
  • Price
  • Basic support for group development
  • Free hosting of demo applications provided by Oracle (see link below)


  • APEX applications are created using Oracle's own tools and only can be hosted in an Oracle database, making an implementer susceptible to Vendor lock-in. However, this problem is applicable to any other alternative technology like .NET etc.
  • While applications are exportable to a script form that can be version controlled, the underlying PL/SQL code is not intended to be human-readable or writable, meaning that it is not easy to compare source code revisions.
  • As an application framework, it can be difficult to customize an application outside of a set of expectations about how an APEX application is supposed to operate.
  • Large installation (V3.1.2 is 532Mb)
  • Very few web hosts offer Apex (Oracle Database) on their hosting service package (most of them offer PHP + MySQL or ASP + SQL Server). As a result, Apex applications are limited in their choice of web hosts.
  • The framework itself adds as little as 0.04 second of overhead to each page request; how well an application scales is primarily based on the efficiency of the SQL queries used by the application developer



Enterprises looking for a quick and easy-to-use application development tool for Oracle will find APEX a good fit. APEX is not a replacement for Java or .NET programming environments, but it can help develop and deploy Web applications quickly. Enterprises that are planning to use APEX should:

  • Initially limit the application development only to tech-savvy users. Although APEX can be used by end users and business users, limit the application development mainly to techsavvy users including developers and DBAs. Having an understanding of the data model and knowing what data to access are key requirements for developing applications using APEX.
  • Look at consolidating spreadsheet and desktop databases. One of the key benefits of APEX is data sharing, and enterprises can leverage this by consolidating spreadsheets and desktop databases.
  • Start small and grow. Initially, deploy APEX on one or two Oracle databases mainly to understand the benefits and issues. Also consider developing internal standards and policies, documenting best practices, and sharing templates and forms to maximum productivity.
  • Nail down any data-security-related issues. Extra security measures should be taken when deploying APEX, especially if the database deals with private data. Perform routine audit of role and user access on databases that run APEX applications.
  • Limit the development of APEX on production databases. Similar to application development policies for other programming environments, APEX should not be used directly on production environments.
  • Train the developers and DBAs. Although APEX is relatively easy to learn and use, to take full advantage of the product, consider training developers and DBAs.



Anonymous said...

An excellent Tutorial!!

Anonymous said...

Proficient overview. Thanks.

Text Widget

Copyright © Vinay's Blog | Powered by Blogger

Design by | Blogger Theme by