Apex for Web Applications Based on Oracle Databases

Oracle has just released version 3.1 of Oracle Application Express (Apex), the tool of choice for building secure web applications based on Oracle databases without the need for a middle tier.

Oracle has just released version 3.1 of Oracle Application Express (Apex), the tool of choice for building secure web applications based on Oracle databases without the need for a middle tier.

I just finished upgrading my development and demonstration environments from 3.0 to 3.1 and Apex seems like a natural place to start blogging on application development tools. The only hitch here is that Oracle tools and software are very rarely covered in DDJ, which has been unfortunate considering the richness and capabilities of the tools.

If you are not aware of Apex, there is a large amount of information available on under both database and application development. The links point to the same place. Apex is included for free with Oracle 10g and comes preloaded in the new Oracle 11g version of the database product.

I use Apex to build web apps that are in the 100-200 page range which includes both functional pages and administration pages. The apps themselves are build on an Oracle schema containing a data model representing the business functionality. The Apex application is generated by the Apex Application Builder and then interpreted at runtime by the Apex engine, all of which runs from the database. The application web pages are generated either through Apache configured with an Oracle extension mod_plsql or through a plsql gateway. There is no application server involved.

The application builder creates pages using templates and common templates are available for overall page and application look and feel as well as actual page layout. My experience is that this is an enormous time saver since database-based applications generally have a lot of the same functionality repeated as needed. For instance, maintenance pages for lookup tables are a bane and are generally coded last and not given much attention. With Apex, a form on report template is used that builds a report on the table with selected columns displayed and provides a link for editing. The link navigates to a forms page that allows for input or modification, provides standard buttons for controlling actions, and also provides the DML (data manipulation language) to fetch as well as save a row. The upshot is that a maintenance page on a table can be assembled fully functional and tested in less than 5 minutes. Once created, the pages can be customized as needed.

In some sense, it is old school in forcing a lot of attention on the data model, but with a proper data model that represents the functional business of the application, the application development becomes the easy part. Data modeling is also something we do not hear a lot about, but is in my mind a fundamental skill regardless of whether it is for a database engine like Oracle or is for a java app.

I am in the middle of another one of these 100-200 page web applications intended to help protect personal data in other enterprise applications like Social Security numbers. You may not be aware that an SSN is worth about $14 each and the organization that lost the SSN usually have to spend upwards of $184 per SSN to correct the damage. SSN security breaches usually involve 10s of thousands of SSN making theft very profitable and recovery very expensive. Applications that manage SSN need to be hardened against hacker attack and the Apex applications I have build so far have passed 3rd party security audits. In general, security has to be in everything; I can't imagine applications built without some form of hardening including linking into identity management processes to make sure only the right people in the right roles can access the application and see only that data they are allowed to see.

More to come including some illustrations and code. Apex uses Oracle's procedural language - PL/SQL - which will look very familiar to Ada programmers. PL/SQL is the choice because Apex can execute code dynamically from within the application, a feature not supported for Java. To do Java, the procedural code needs to be compiled into the database schema separately where the Java is wrapped in PL/SQL then can be referenced from the Apex application. PL/SQL is specific for Oracle creating a portability problem, but considering how many customers have the Oracle database and all of them know PL/SQL, portability is not really a problem. In fact, I have some customers that will not take an application not in PL/SQL due to support issues.

All of this is available on The database is available for free with a developer license and runs nicely on recent windows XP Pro laptops with 2G RAM giving you a chance to take a look at the full technology. Apex is bundled with the database for free. If you build web apps on top of Oracle databases, this is the tool. Works for me. More to come on this.


Editor's Choice
Samuel Greengard, Contributing Reporter
Cynthia Harvey, Freelance Journalist, InformationWeek
Carrie Pallardy, Contributing Reporter
John Edwards, Technology Journalist & Author
Astrid Gobardhan, Data Privacy Officer, VFS Global
Sara Peters, Editor-in-Chief, InformationWeek / Network Computing