ra2 studio - Fotolia


Oracle APEX application tools ease development of database apps

Oracle Application Express lets users build web applications largely through wizards. Consultant Robert Sheldon explains the process for using the Oracle APEX toolbox.

Oracle Application Express -- or Oracle APEX for short -- is a set of tools for developing and deploying web applications without having to do heavy-duty programming. The tools are freely available to users of Oracle Database and the Oracle Database Cloud Service through a web-based interface that relies heavily on wizards, forms and drag-and-drop functionality.

The APEX software is installed by default with all editions of Oracle Database 11g and 12c, both on premises and in the Oracle cloud. Developers need only their web browsers to use the tools to build a custom Oracle APEX application that can target both desktop PCs and mobile devices; end users with the proper login credentials can then access and work with the application via their browsers.

Developers build APEX applications within dedicated areas of databases called workspaces. Each workspace provides a logical development environment for building applications against one or more identified schemas. For example, an organization might set up a workspace structure based on its departments so that each one can develop its own applications independently of the others.

Inside the APEX architecture

Each APEX application is made up of a set of pages. The metadata that defines the components of pages within an application are stored in Oracle Database; when a user calls an application, APEX renders the pages dynamically, without the need for code generation or file-based compilations.

APEX can use one of three different broker technologies as a proxy for handling requests for data between an end user's browser and application objects in the database. These are:

  • Oracle REST (representational state transfer) Data Services, a Java-based interface formerly known as APEX Listener that runs inside a J2EE web server.
  • An embedded PL/SQL gateway that runs in the Oracle XML DB web server built into Oracle Database.
  • The Oracle HTTP Server with mod_plsql, an extension that manages communications between applications and Oracle Database.

When end users connect to an APEX application, they have access to multiple layers of data, as defined during the development process. Oracle provides a sample database application to demonstrate how APEX works. The application's first page displays aggregated customer, product and sales data, each of which can be accessed at various levels (see figure one).

Sample database application
Figure one: General customer data displayed on Oracle's sample APEX application.

From this page, users can click on links to see related data and modify or add information. For example, if a user clicks on a customer's name in the Best Customers component, the application opens a small window that displays details about that customer (see figure two).

Sample database application customer details
Figure two: Details about an individual customer in the sample application.

From this window, the user can view and modify the information or delete the customer record entirely. Any changes made are applied immediately to the data in the rest of the database.

A user can also access a full list of customers from the Best Customers component by clicking the arrow in the upper right corner of the box. Doing so launches the Customers page (see figure three). 

Sample database application customers page
Figure three: The Customers page in the sample application for Oracle APEX.

From this page, users can perform tasks such as searching for customers or creating new customer records. In this way, each component provides options for viewing and working with data in multiple ways. It should be noted, however, that you can also use APEX to build websheet applications, which are similar to wiki pages, that support community contributions. Websheet applications are generally easier to build because they don't require SQL development experience.

APEX application development in action

APEX uses a declarative framework for application development. What this means, according to Oracle, is that developers specify what to do, not how to do it. They interact with built-in wizards and property sheets without having to generate or compile code.

The primary skill that APEX developers usually need is the ability to work with PL/SQL, Oracle's SQL extension for Oracle Database. They can create and edit PL/SQL directly through the APEX interface when building their applications, making it possible to retrieve exactly the data they need for each application component.

When you first connect to the APEX environment through your browser, you're presented with four main tabs:

  • Application Builder
  • SQL Workshop
  • Team Development
  • Packaged Apps

Application Builder is where you do most of your development work. At the heart of Application Builder is Page Designer, an environment for building and managing the pages that make up an APEX application. With it you can add or delete pages, as well as create, modify or delete the components -- or regions -- that go on each page. For example, figure four shows part of the first page of Oracle's sample application as it appears in Page Designer.

Sample application in Page Designer
Figure four: The first page of the sample APEX application as seen in Page Designer.

Page Designer provides a wide range of features for configuring the components on a page. Notice the Best Customers region selected in the main pane of the screen shown in figure four. The pane at the right of the screen provides details about the selected region, including the SQL query used to retrieve the data accessible through the Best Customers component. The right pane also includes various options for configuring the component

The next tab in the APEX interface is SQL Workshop, which provides tools for viewing and managing the database objects and data that drive an application. When you first open the tab, you're presented with a set of five tools (see figure five).

SQL Workshop in Oracle APEX
Figure five: The five tools you see when you open SQL Workshop in Oracle APEX.

With the tools, you can do things such as view and create database objects, enter ad-hoc SQL commands, store and run SQL scripts, create SQL queries via drag-and-drop operations, load and unload data from tables, monitor database operations, and configure RESTful services that other applications can use to access the data.

The Team Development tab is designed to help facilitate the APEX application development process. You can use the tools on that tab to define project milestones, track features and bugs, maintain to-do lists, and capture user feedback (see figure six).

Team Development tab
Figure six: The tools available in the Oracle APEX interface's Team Development tab.

The final tab in the APEX interface is Packaged Apps. It provides access to Oracle's suite of business productivity applications that can be deployed on APEX with a few mouse clicks. The Packaged Apps tab also includes example applications like the sample database one.

To view a list of all the available applications, go to the Packaged Apps Gallery (see figure seven). 

Apex Packaged Apps Gallery
Figure seven: Some of the applications available in the APEX Packaged Apps Gallery.

More to Oracle APEX

There are plenty more features available in APEX. For example, a Universal Theme user interface that became available in APEX 5, which was released in April 2015, lets you build responsive, sophisticated applications without having expert knowledge in HTML, CSS or JavaScript. APEX also includes jQuery Mobile, a lightweight framework for building compact webpages. In addition, you can package and export an application for deployment in other APEX instances.

The best way to learn about APEX is to try it out, which you can do for free, without having Oracle Database installed or subscribing to the Oracle Database Cloud Service. Like any development platform, APEX isn't suited to every type of application -- but for certain web applications that you want to get out quickly, APEX could be the answer. At the very least, it's worth taking for a test drive, especially if you're already running Oracle Database.

Next Steps

Oracle API Manager and Oracle SOA Cloud don't require any coding

Oracle Applications Users Group president talks cloud applications

A hands-on guide to Oracle Application Express security

Dig Deeper on Oracle applications implementation and upgrades