Oracle Application Express (APEX) Architectural Review

Dr. Paul Dorsey, Dulcian, Inc.

 

There have been many papers and articles written about how to use Oracle Application Express (APEX) and even a few books on the market, but what seems to be missing is a paper which architecturally describes the structure of the product. From a designer’s perspective, what was the core philosophy behind the product? How does it do what it does?

I took a slightly different approach to this paper since my company, Dulcian, Inc., produces a complete suite of business rules-based products (BRIM®) designed to build and generate entire systems,  including database design, process flow, data validation, Web Service generation, data migration and screen generation. Usually, one of the most expensive elements involved in building an information system is creating the user interface in an integrated development environment (IDE). The APEX user interface has garnered a lot of attention for being very user-friendly. I wanted to find out whether or not I could use APEX as the UI for my business rules repository in production application development. Ultimately, the requirements of most of our projects went significantly beyond APEX’s capabilities, so I was not able to integrate it into the BRIM® environment. However, in the course of my investigation, I was able to understand and appreciate what APEX is, and thought that an architectural description might prove useful to organizations contemplating adopting APEX, as well as improve the understanding of current users of the product.

 

A. Architectural Review Components

What is meant by an architectural review? Every user interface development product must solve the same problems. By asking how each of these problems is solved, it is possible to structurally compare different products. The following are the questions I was trying to answer with regard to APEX:

1.       What was the overall philosophy behind the product? What were the reasons for its creation? What differentiates it from other products?

2.       The Model-View-Controller design pattern used in Java EE is a useful way to think about the core architecture of a system design tool.

·         The Model refers to data sources and how the application binds to, maintains, and updates them.

·         The View layer encompasses how things are implemented on the screen.

·         The Controller layer controls how pages navigate from one to another.

3.       Does APEX implement the Model-View-Controller structure? If so, how?

4.       How do you define the layout of the application screens? What is the layout model? Can you create very sophisticated screens? What are the limitations of the layout and available components?

5.       What is the event model? When clicking a button on the screen, how do you tell the button to do something? How much can you tell it to do? Is it easy to write complex code? APEX uses an Event Model for these tasks.

6.       During implementation, what is physically happening behind the scenes? How are things stored? How are they rendered? Understanding the implementation layer provides indications about the scalability of the architecture and how well it might work in your environment.

7.       Because APEX uses a largely 4GL approach, what can be done if the project needs to go beyond its capabilities? Is it possible to extend the product to do things that it was not designed to do? How hard is this?

 

1. Overall Philosophy

APEX has an interesting history. Before APEX was released as a product, it was a hosted development serviced called “Project Marvel,” which then became apex.oracle.com. Even before that, it was an internal framework called “Flow Builder” used for Oracle Consulting projects and as the framework behind the calendar and resource scheduler Oracle used at that time (2001-2002). Its conception as a product began with Web DB which was originally coded in the late 1990s. It was not intended to be an application development tool. It was created as a utility to give database professionals a “quick and dirty,” functional (but not pretty) application to View, Insert, Update, and Delete data in the database. It was a code generator rather than a repository-based tool (as APEX now is). Web DB was entirely a point-and-click 4GL+ environment. That user-friendly concept is still evident in the current version of APEX.

APEX (then called HTML DB) first became a product in 2005, when it was introduced with the Oracle 10g database.  Philosophically, APEX sits in a unique place in the industry. It has raised 4GL thinking to the next level by creating a product to build applications with no coding required. As a result, there are many screens, each to specify a different feature, behavior, or component in the architecture. Rather than going out of its way to make manual coding easy and efficient, with APEX, Oracle has gone out of its way to provide a screen and an option for everything.

The pluses and minuses to this approach are varied. It is reasonably easy to learn to use APEX, much more so than any other tool in the Java EE environment, including Oracle’s own Application Development Framework (ADF). Once you figure out all of the myriad screens, you can be very productive in APEX. However, APEX’s strength is also one of its main weaknesses. Because you are doing everything declaratively on property screens, there are many screens with which developers need to become familiar; so many in fact that APEX uses high-level wizards to allow you to quickly build applications. Later in the application design process, the Developer mode can be used to edit and enhance the created applications. Sometimes, you will need to go beyond the application and use the lower-level APIs to manipulate the repository. This can be problematic since it effectively provides multiple ways to specify the same or similar functionality as well as multiple places where business logic may be hiding. For someone not very familiar with the product architecture, ferreting out and fixing bugs might be quite challenging.

APEX encourages developers to build within the framework. To be fair, there are many things that APEX can be effectively used for. If you are willing to compromise somewhat on UI standards and screen capability, APEX can work well for you as a development tool. Although many people using the tool may believe that APEX was not designed to support enterprise-level applications, that not completely true. There is more to this product than may be obvious. It is not a “toy.” It is possible to build serious, reasonably scalable, secure applications with Oracle Application Express.

 

2. APEX Meta-Model

The actual repository that supports APEX is quite large and complex. There are over 200 tables in the repository. However, a simple, high-level class diagram can be used to explain the core structure of APEX as shown in Figure 1.

 

 

Figure 1: APEX Meta-Model

  

At the highest level of the diagram are “Workspaces.” These allow different groups of developers to all work in the same repository and not have to interact.

“Applications” (Flows in the physical tables) correspond to the standard concept of an application. Because APEX is repository-based, there is no worry about applications having too many screens in them or referencing too many different objects.

Each application is split into “Pages” (Steps in the physical tables). Pages correspond to screens in the UI.

Pages are broken up into “Regions” (Page_plugs in the physical tables). Regions are areas on a page. Each region can contain either a single complex component (like a grid or a report) or several fields (like in an Edit screen) that all point to the same table or view.

There are many possible “Components” that can be placed in a region. All of the standard components that one would expect are available.

The Region type determines whether a region is a report, form, chart, etc. Some regions have child table-like report columns for report regions.

Binding occurs at the Page and Component levels for fields (meaning that the table is set at the Page level and the column binding is set for each component) as well as directly in the component for complex components. For example, in a report component, the SQL that defines the report is stored with the component.

Runtime tables store the current data values of all screen components as well as session-specific information.

Events are triggered at the Page level (such as after data population), or Component level (such as a button press). Actions triggered by events consist of PL/SQL code stored with the event.

 

3. Model-View-Controller

Traditional client/server systems contained persistent sessions. As a result, it was possible to flip back and forth between many screens, update database tables, and then send a Commit command at the end of the process. Since web applications are inherently stateless (or should be since stateful applications do not scale well), most frameworks have some type of model layer (like ADF BC) that allows complete transaction control to be maintained in the middle tier with all of the tables updated in a single transaction. APEX does not have a model layer. If you have a transaction spread across several screens, it is necessary to code the transaction consistency by hand.

It appears that the APEX development team is currently contemplating a model layer. They use a collection when cached data is pulled from external sources such as Web Services. That same structure is used to cache session-specific data (like a shopping cart in a retailing application or data on screens with multi-step wizards.) If some capabilities to populate collections from simple database queries were added, APEX would be close to supporting a rudimentary model layer. Also, single values for page items and application items are stored in the session state, so you can build multi-step/multi-page wizards (or transactions). Once you reach a final step, you can update and commit the data in the actual schema tables.

For complex applications with many screens, most system architectures include a Controller layer for page navigation to abstract the page control into its own isolated area. Many Java EE developers use Struts, which was used by ADF, then replaced with the Faces controller and now updated again with Oracle’s own custom Controller in JDeveloper 11. APEX does not have a separate Controller layer. Page flow logic is embedded in the pages. This is not as big a concern as it might appear. Since APEX is repository-based, it is a relatively straightforward task to extract a diagram showing the page flow logic. Though it is not in the product, some users have implemented this functionality. (See Patrick Wolf’s paper at the following website: 

http://www.inside-oracle-apex.com/apexlib-get-page-flow-diagram-of-your-application/

For the View layer, APEX serves up HTML pages. Oracle has partnered with a nice charting product called AnyChart to insert pie, bar or other types of charts into your applications.

 

4. Layout

The APEX layout model is relatively basic. Although there are many widgets with the ability to customize elements on the screen, the most common task when developing screens is laying out components on a grid. There is no ability in APEX to nest layout models or panels on the screen, so the ability to control screen layout is quite limited. If you are used to a product with pixel-level control (like Oracle Forms), screen design in APEX will be a significant step down. However, given its mission of quickly building functional applications, APEX accomplishes this extremely well. The lack of sophisticated screen control is compensated for by the extensive list of customization items available for components. For example, when creating a grid, it is possible to place paging control on the grid. To manually lay out components for grid paging is quite challenging. APEX provides many different options for handling paging. If you wanted to create your own paging model, beyond the APEX options provided, there is no mechanism in the current version to do this.

One APEX layout model limitation is the way in which components are bound. Multi-record components such as “reports” are populated individually by a SQL statement. However, all individual, editable fields on a page must be bound to the same table or view. This is less of a restriction than you might think. If you wanted to have fields pointing to more than one table from a screen, you could create a database view that queries all of the attributes from the different objects and place INSTEAD OF triggers on that view to support DML.

There is some customizability (called “themes”) in APEX that some customers use. Themes are similar to Cascading Style Sheets (CSS). You can also reference actual CSS files, just as in any other web development framework. Themes define templates for each part of an application, such as page templates, region templates, report templates, button templates, label templates, etc. For each template type, there are always a handful of choices available. APEX ships with 18 themes. Customers can add their own themes to re-create their own corporate look-and- feel. One of the big advantages of using APEX is that themes can be switched at any time, thanks to the meta-data driven on-the-fly page generation. For larger projects, developers can start with their own theme of choice. Once the customer has settled on the final look-and-feel, the new theme can simply be installed into all applications. Likewise, if your corporate user interface changes or your company is merged with another, you can simply create a new theme and switch.

 

5. Event Model

Architecturally, the APEX Event Model is a standard HTML event model. A button on a screen that needs to do something actually involves submitting the page back to the database. Contingently, on the Submit event, the system needs to detect what object caused the Submit and fire some action accordingly. Rank and file developers never see any of this activity. Developers use wizards to declaratively take care of this two-step process. What is triggered by these events is PL/SQL code written and stored in the repository. At execution time, APEX uses Dynamic SQL.

Having much of the functionality defined in wizards means that there are a few holes in the APEX product that have not yet been coded. In the current version, declarative validation can be done on a single record. Even though tabular forms (multi-record table objects) are editable, this same validation is not available for records in tabular forms.

Unless you really need to dynamically change properties at runtime, you can isolate your coding logic in complex PL/SQL routines. One particularly nice feature allows you to use procedures with IN/OUT parameters. The APEX code passes component names to these procedures and the OUT parameter can be used to update the value of that component.

There is a nice, rich Event Model to trigger at any reasonable point. For example, in addition to things like Button-Pressed, APEX allows actions to be coded on BEFORE/AFTER DRAW content events.

Going beyond the APEX declarative framework forces you to write everything in PL/SQL and manipulate the repository using API commands. If your application never or rarely needs to resort to this technique, the APEX Event model is quite convenient. However, there are applications where this declarative Event model fails. For example, for a system requirement with a complex role and object-based security model where, depending upon who is manipulating which object, different fields are visible or editable, you would have to attach your security framework to every attribute on every field. It would be much more convenient to simply code the security requirements in PL/SQL using APEX APIs and implement all security requirements in code.

This is one of the biggest risks of using APEX. The declarative model might work 90% of the time but the remaining 10% of the time, it is less convenient than coding application logic using code. I had tried this with BRIM® in attempting to work within a repository-based UI logic architecture for a number of years. Every project we worked on required extending the depth of the repository until ultimately, the repository was more complex to use than a programming language. We abandoned it in favor of writing all UI logic in PL/SQL.

 

6. Implementation

APEX does not simply send out HTML pages which are then submitted back to the application server like a traditional web application tool. There is a complete copy of the data values on every screen that a user has accessed in the database. There is a runtime table storing the name of each component and its data value. Before the first screen is generated and sent back to the client, a copy of its values is stored in a physical table called WWV_FLOW_DATA. This means that if a user has viewed a screen, you can reference the values on that screen even though it was viewed at an earlier point in the application session. This is very useful in many situations. For example, query criteria may be spread across more than one screen but still referenced at the same time without any additional hand coding required. APEX stopped short of having a complete copy of the entire UI in the database. After a page has been submitted, there is no easy way for developers to ask questions like “Which fields were visible or required?” when that screen was last accessed.

 

The code to generate the HTML page is mainly created using the Oracle HTP package.

To deploy APEX applications, you need some sort of application server. You need the Apache HTTP server and mod_plsql (an Apache plug-in that allows calling PL/SQL procedures). The Apache HTTP server ships with the Oracle database and is also part of the Oracle Application Server. Both options will work equally well.

Starting with Oracle XE and Oracle 11g, the embedded PL/SQL Gateway is included. This is essentially an HTTP server built into the Oracle database, which can be used as an alternative to Apache HTTP. It is much easier to set up and configure; however in this case all images, Style Sheets, JavaScript files, etc. are deployed into the database (XML DB). Some customers prefer Apache HTTP because it allows deploying those files into the middle tier.

From the client’s perspective, the page is a plain HTML page. There is enough minimal JavaScript in the page to submit it back in the appropriate context. Going forward, APEX will likely take more and more of the declarative business rules and send them to the page as JavaScript. This strategy seems to be what most frameworks are currently leaning towards. But this is a dangerous road to go down. The more client-side validation, the larger the page becomes. The more JavaScript on a web page, the slower it will execute on the client. With modern “AJAX-y” web applications, it is not uncommon to significantly degrade the performance of the application because the client machine is waiting for a 2MB web page to load, followed by hundreds, if not thousands of lines of JavaScript to execute. On a page that takes 10 seconds to load, it is not unusual for it to take less than 1 second of processing time in the application server and database, 2-5 seconds to load the page, and the remaining 3-5 seconds spent waiting for the JavaScript to execute on the client. I hope that as the Oracle APEX architects provide the ability to perform business rule checking client-side, they provide the option for developers to choose where they want that validation to be enforced.

Currently, the pages generated in APEX are small compared to ADF based on test applications built last year for a presentation as shown in Table 1.

 

APEX

ADF Faces

Initial load

87KB

237KB

Average screen size

10KB

20KB

Session total

1.3MB

2.3MB

Table 1: Page Load Comparison

 

In real production applications, a typical APEX page is about 20-40k, whereas similar screens built using ADF are 200k or more (although they have more features).

APEX takes a database server-centric approach to application development. The HTML pages (or portion thereof if doing a partial page refresh) are all calculated in the database and not in the application server. As a result, the load on the application server is trivial. The only code in the server is a small amount of logic that accepts the submitted pages and passes the HTML back to the client.

All of the work is done in the database. Although this will likely make Java EE architects nervous, in practice, APEX applications should scale quite well, if not significantly better, than their Java EE counterparts.

There are challenges associated with I/O on the runtime tables. The redo logs of a database running a large APEX system might have a significant portion of their resources consumed by I/O to the runtime tables. If your system is trying to support offsite backup using a product like DataGuard, you might end up unnecessarily backing up a lot of data that is only runtime session-specific. Solving this problem is not simple and may require setting up a separate database instance. Something the APEX designers should contemplate is trying to adapt their engine to move the metadata repository and runtime data to a TimesTen database. APEX could achieve significant scalability and performance gains using this strategy.

 

All of the logic written in code or specified using the APEX wizards sits in the metadata repository and is not used to generate code. It is queried at runtime, fashioned into PL/SQL as text, and then executed using DBMS SQL.

Similarly, each time that components are populated from the database, the query is generated on the fly and again executed using DBMS SQL.

All of this dynamic calculation of code does not seem to cause significant performance problems. If it does become an issue, behind the scenes, it should be possible for the APEX designers to re-architect their system and generate all of this code into packages that would be accessed at runtime. This might make some DBAs nervous since APEX would need the ability to create packages automatically at design time.

APEX detects that the record being updated is about to be updated by someone else by doing a CHECKSUM on the record when it is read. Then it rereads the record just before updating and recalculates the CHECKSUM. If a different number is returned, then the record has been modified. Starting with version 10 of the DBMS, there is an optional pseudo-column called ORA_ROWSCN which would provide a much more elegant solution to this problem.

APEX includes a nice mechanism for exporting and importing applications. For example, you can export an application (including all of your images, style sheets, Java Script files, DDL scripts, etc.) from one system and import it into another system without fear of colliding primary keys. APEX uses an OID idea where the primary key generated for each object is a long string that includes elements such as dates and database instance IDs to ensure uniqueness.

An APEX development environment nicely supports multiple projects at the same time. When you set up an APEX instance, you set up workspaces for each of your user groups, (departments, different groups of developers, etc). All workspaces share the same metadata tables, but are completely separated by including a security group ID in each metadata table. For example, at apex.oracle.com there are over 15,000 companies signed up. They all have their own workspaces, each workspace associated with one or many database schemas.

 

7. Going Beyond APEX

APEX should be considered a WYGIWYG – “What you get is what you get” tool. If you are not willing to live with what you get, you should use a different product. Even more than most 4GL products, APEX is somewhat hostile to extensions. This is a conscious decision on the part of the architecture. All operations in APEX are done through a wizard or property panel. Although it is possible to code directly with APIs behind the product, most successful APEX projects avoid doing this. If there are a few odd things that must be done using APIs with APEX, you may still be successful. But if your application requires manual extension of components, writing HTML templates, manually creating JavaScript to go to the client, you may not be using the right tool or you should compromise on the requirements to stay within the APEX framework. This seems to be the critical success factor for APEX projects. The basic development strategy should be “If you can’t do it by pointing and clicking, don’t.”  However, there are cases where a small amount of JavaScript is called for, such as pop-up alerts. The APEX forum contains quite a few lightweight examples of sensible JavaScript in APEX.

 

B. APEX Learning Curve

APEX is orders of magnitude easier to learn than any other tools in the Java EE space, including Oracle’s ADF. But it is still not a trivial product to become proficient at using. There are many features that can frequently be accessed from more than one place, each requiring one or more screens. It is not always obvious where a particular feature can be found. After some number of months, developers get the hang of building applications in APEX. But like any product, it is easy to abuse. You can select a sub-optimal way to solve a problem that ends up being a lot more work than it would have been if the product had been used correctly.

APEX is not completely “user-obvious.” There are numerous examples of projects undertaken by skilled developers who made architecturally sub-optimal decisions, thereby significantly increasing the cost of system development and/or degrading performance. Even though it is easier to learn to use than other Java EE environments, organizations embarking on significant projects would be wise to invest in training and bring in an experienced APEX architect to help make development and design decisions.

C. Security in APEX

Depending upon the way in which an application is written, it is certainly possible to create websites vulnerable to hacking. A large Oracle user group’s website built in APEX was hacked by a security expert in less than 15 minutes. That said, APEX is probably less prone to security problems than most other frameworks, if for no other reason than that the APEX team has gone to some lengths to prevent security holes in the architecture.

The paper “How to Hack an Oracle Application Express Application” by Anton Nielson is quite good. Anton has been presenting it at all the conferences. You can contact Anton directly for a copy (http://c2anton.blogspot.com/2008/02/security-how-to-hack-oracle-application.html)

One of the best descriptions of APEX security is in the 3.2 new features white paper. There is a section about security that provides a pretty good summary of existing and new security features:

http://www.oracle.com/technology/products/database/application_express/html/3.2_new_features.html#02

D. Conclusions

APEX is frequently used to create a prototype or “get something out the door quickly” until the “real” application is built using some enterprise-level tool. APEX systems are typically completed in a few months and two years later, people are still waiting for the “real” application to work. Using APEX is not a particularly high-risk strategy.

The $64,000 question for Forms developers seems to be “Can I convert my forms to APEX?” The answer is “only to some extent.” The Oracle Forms layout model is much more sophisticated than the APEX model. APEX could conceivably convert most of the Forms PL/SQL code into server-side code. The Event Model in Forms is much richer but APEX has equivalents for most of the commonly used Forms triggers. The only current plans that the APEX development team has in this area are to bring over Forms blocks and items but there are no plans yet to convert Forms PL/SQL.

Based on the information about APEX described here, is APEX fundamentally sound? The answer is “Yes.” The lack of sophisticated layouts, model layer, and insistence on a virtually exclusive 4+GL interface seems to mandate living within the APEX box. However, everything possible has been done to make this “box” as large as possible. As long as you can live within the confines of the APEX user interface logic and do not need to significantly extend the UI layout, functionality, or include requirements with logic that goes outside of the framework, it is a very useful tool.

 

About the Author

Dr. Paul Dorsey is the founder and president of Dulcian, Inc. an Oracle consulting firm specializing in business rules and web-based application development. He is the chief architect of Dulcian's Business Rules Information Manager (BRIM®) tool. Paul is the co-author of seven Oracle Press books on Designer, Database Design, Developer, and JDeveloper, which have been translated into nine languages as well as the Wiley Press book PL/SQL for Dummies. Paul is an Oracle ACE Director. He is President Emeritus of NYOUG and the Associate Editor of the International Oracle User Group’s SELECT Journal. In 2003, Dr. Dorsey was honored by ODTUG as volunteer of the year, in 2001 by IOUG as volunteer of the year and by Oracle as one of the six initial honorary Oracle 9i Certified Masters. Paul is also the founder and Chairperson of the ODTUG Symposium, currently in its tenth year. Dr. Dorsey's submission of a Survey Generator built to collect data for The Preeclampsia Foundation was the winner of the 2007 Oracle Fusion Middleware Developer Challenge and Oracle selected him as the 2007 PL/SQL Developer of the Year. He can be contacted at paul_dorsey@dulcian.com