Issue 22

Rapid Application Development for the Web with Oracle APEX

George Bara
@ZA Cloud


Ever wanted to build a web application extremely fast, without the need of learning a new programming language? Ever wondered why it is still too complicated to quickly create web pages with forms and reports and why every Rapid Application Development tool out-there becomes "rapid" only after spending a few months learning it?

Well, there is a hidden gem called Oracle Application Express (APEX) that might just be the answer for one-off web application developers, database integrators and expert programmers alike. Using a highly declarative environment you can build professional web applications with a click-and-click approach. The real surprise here is that this tool comes from Oracle, a corporation famous for valuing expensive "locked" products. APEX is free (but still not open source) and has spawned of an internal Oracle project meant to make life easier for database developers and administrator.

My experience with APEX began in 2009 while working for a Life Insurance company, as an Oracle database developer. The main task was integrating multiple software systems, both internal and customer-facing. Lucky enough, all software components were using Oracle databases; hard enough there was little time and resources to develop the user interfaces in Java, .NET, PHP or any other programming language and framework. The IT manager had little experience with APEX, but great faith in its capabilities. So it began a 3-years long intensive experience with APEX 3.2, at a time when documentation was sparse, the experts were just a few and just a dozen enthusiasts were talking about APEX in public events, forums or message boards. The result was a unique development experience, lots of documentation, guides and whitepapers created along the way and a book published in 2013 called "Oracle APEX Reporting Tips&Tricks" (available on Amazon, iBookStore and Barnes and Noble).

Oracle Application Express, common known as APEX, is a Rapid Application Development (RAD) tool that has reached a certain high level of maturity with the launch of version 4.0 in June 2010. APEX combines fast development cycles for web-based applications revolving around an Oracle database with a strong developer base and dedicated "evangelists" promoting the technology. The programming technique is highly declarative in a web-based environment, with little programming effort required.

APEX uses a unique concept that can be considered opposite of all the current web development trends. While nowadays everything in a web application must be as loosely coupled to the database as possible, with emphasis on the client side interaction, APEX has a radical approach, as everything is stored in the database, from the data to the meta-data that generates the web pages. A web server is used to generate HTML pages directly from the database, where both the web page data and metadata is stored. Although APEX comes free of cost, all the development is done within an Oracle database using unique Oracle concepts, with all the backend processing and most of the frontend processing being performed by the Oracle database stored procedures.

An Oracle APEX web application is developed by using SQL and PL/SQL, although most of the development can be done in a declarative way by using the browser-based Development interface. It is a database-centric tool, meaning that it requires - and will only run on- an Oracle database. The history of APEX starts in 2004, when it was just an internal Oracle tool called HTML DB. In 2006 it was renamed to Application Express version 2.1, currently it is at version 4.2.4 and there is already an early adopter of version 5.0 released (https://apexea.oracle.com/i/index.html).

Using APEX on an existing Oracle database instance, even the free Oracle XE one, does not require additional licensing and it is not restricted to number of developers, applications and end-users. It supports DB versions from 10gR2 up to the latest and it can be used with Exadata, ORA and RAC setups. By default, Oracle APEX is now distributed with all Oracle database editions.

From an architectural perspective, APEX uses simple 2-Tier architecture. The webpages are dynamically rendered using the metadata stored in the database and there is no code generation or file-based compilation at any time. It basically runs wherever the Oracle database runs. APEX uses a multitenant hosting principle, organizing web-pages into applications and workspaces, which can use distinct or shared database.

Although most of the underlying code is written in PL/SQL, getting started with APEX requires little knowledge of any programming language, except maybe some HTML and web basics. Being web-based, the development process consists of using a series of predefined pages and objects, from forms to reports and charts. All pages and components are based on of Oracle DB objects, usually tables and views, so a schema management tool is embedded in the project. Creating tables, views and stored procedures can be done from APEX, so the entire development process can be encapsulated, at least at the early stages, within the product"s web pages.

Accessing APEX is done by accessing a URL in a browser, whether you are accessing a locally installed version of Oracle APEX, an instance provisioned in a private cloud (SaaS) or the Oracle Database Cloud Service, Oracle"s own cloud service that relies on APEX for application development (http://cloud.oracle.com).

However, Oracle APEX is a not a tool that suits any project. The most typical use-cases where APEX should be used are data-driven applications (opportunistic and departmental productivity apps), online reporting (SQL-based), spreadsheet web-ification (by transforming Excel spreadsheets into web apps) and access replacement (where APEX can be used as a central point of access to multiple Oracle database schemas).

The main components of the APEX development environment are:

Application Builder, where applications and application pages are created declaratively using wizards. Each application is composed by one or multiple pages, translated at runtime by one or multiple web resources and each page is split into several regions. Each of the page regions can contain text, custom PL/SQL, reports, charts, maps, calendars, web service references or forms. Also there are other objects that are specific not only to the pages but to the whole application, like application items, processes, computations, authentication and authorization schemes or navigation objects like tabs, lists or breadcrumbs.

SQL Workshop, a tool that enables the management of the database objects. Ad hoc query, wizards for creating tables, view, stored procedures and other database objects make up a suite of features that will enable the developer to do schema management tasks from the browser-based APEX tool.

Team Development, an integrated team management development tool, for tracking features, bugs and milestones. The tool is linked directly to the APEX pages.

Administration, for account administration, the workspace and using a dashboard for workspace utilization.

As most RAD tools, Oracle APEX provides easy development in a declarative means, by just using pre-built items, of the following components: reports, forms, charts, calendar, UI templates, navigation, validations, processes, computations, web services, email services, translation services, authentication, authorization and logging and monitoring.

For more details on how to get started with Oracle APEX, check out my "Oracle APEX Reporting Tips&Tricks" book (2013) on: http://www.apexninjas.com/blog/2013/06/oracle-apex-reporting-tips-tricks-out-now/

Also, you can check out a simple demonstrative blogging platform built using APEX here: http://apex.oracle.com/pls/apex/f?p=20559:101:



  • Accenture
  • BT Code Crafters
  • Accesa
  • Bosch
  • Betfair
  • MHP
  • Connatix
  • BoatyardX
  • .msg systems
  • Yardi
  • Colors in projects