Oracle APEX: Doing More with Less

APEX is only getting better with time. If you need a web-based application or interface into your data, and want to empower developers and end-users (in terms of letting them do more with less overhead), APEX is the way to go.

In a bit more than six years since its initial release as HTML DB, Application Express (APEX) has evolved into quite the robust rapid application development tool. It seems of late that many Oracle products are being merged with related (and formerly competitive) products and tools. APEX is getting better and becoming more feature rich from release to release, and the good news is that you don’t have to worry about learning a (new) replacement product down the road.

Why bother with APEX? If you consider Oracle Warehouse Builder, Streams, and Forms & Reports as examples of products or tools whose independent futures don’t look so bright, each with its own significant investment of time, money and effort, it would be understandable that users looking for a new tool might be a little hesitant in terms of getting on board with something new (or old, depending on your frame of reference). Warehouse Builder requires a good bit of effort in terms of learning (well, they all do), but OWB as we know it today will be something different when merged with Oracle Data Integrator (formerly Sunopsis Data Integrator). Oracle Streams, as stated in its Statement of Direction, is being replaced in favor of Oracle Golden Gate, yet another recently acquired product. Finally, Oracle Forms isn’t quite dead, but several other products are being pushed in terms of a “gee, look how easy it is to migrate forms to me” approach. Oracle Reports isn’t doing any better either, considering where BI Publisher now appears (in E-Business Suite, Oracle BI Suite, and yes, even in APEX).

APEX, however, appears to be entrenched as APEX, so an investment in adopting this RAD tool is safe and worthwhile. The languages behind APEX, including SQL, PL/SQL, JavaScript, HTML and Cascading Style Sheets (CSS) aren’t going away anytime in the foreseeable future (what is ever going to replace SQL?). If you consider the licensing costs of Forms & Reports, migrating to APEX – which is 100% free (after you’ve paid for the RDBMS product) – will probably save you time and money down the road. With APEX, you can build fairly sophisticated applications without ever having to write a single line of code. The installation footprint of APEX is also hard to beat. As far as the server is concerned, at least from a DBA’s perspective, several issues are addressed in Oracle Application Express – A DBA Perspective. For development work, everything is 100% browser based.

APEX 4.0 (released in summer of 2010) introduced the Team Development new feature. Team Development is described as:

“…a built-in project management tool that enables you to manage the development process by tracking new features, miscellaneous development tasks, bugs and milestones. Team members can also provide real-time feedback which then can be categorized into features, general tasks, or bugs.”

An application in APEX consists of pages, and each page has its own page definition, which includes rendering (properties related to how the page looks), processing (how changes on a page are processed), and shared components (which objects are shared among pages). Since more than one developer can work on a page at the same time, two APEX features should be used. The first one is to lock a page before working on it. This prevents other developers from making concurrent changes on top of yours. The second is to periodically examine the page history. APEX has a built-in audit trail, so to speak, of who made what changes on a page and when (and what, for that matter).

Now, let’s compare all of that to Oracle Forms. Page rendering’s counterpart is how you design a canvas (color, look and feel, placement of controls/buttons/tabs/etc.). Page processing’s counterpart is all of the background PL/SQL you code in triggers and stored procedures. Within APEX, the vast majority of everything you would have to do for navigation, DML buttons, and reporting is built-in for you. Finally, shared components are analogous to templates and inheritance. When you factor in the Team Development features, what does Oracle Forms have to offer in comparison? Practically nothing is how I see it.

Another new feature, Dynamic Actions, takes care of the JavaScript coding drill related to the showing, hiding and enabling of objects. The JavaScript approach to this was a big improvement over how that’s implemented in Forms (figure out what state a block is in, and then go through a series of setting visibility and enabling/disabling objects one by one).

APEX also provides a decent interface into an object’s metadata. Using the Object Browser under SQL Editor, you have access to pretty much everything there is have with respect to managing a table. From column definitions to the CREATE TABLE statement used for DDL (including indexes, triggers and constraints), this interface is hard to beat, especially when you consider that all of this is browser-based (i.e., nothing to install).

APEX also provides a decent interface into an object’s metadata.

The Model tab is a quick and dirty ERD-like depiction of how a table is related to other objects.

Oracle APEX Model tab

This interface also supports the downloading of reports. When in the Data tab, the “Download” options include dumping the data into a CSV file. This process is also reversible in that you can take a spreadsheet and upload it into a new table. Say goodbye to the “select col1||’,’||col2||etc.” drill of concatenating commas between column output and spooling to an output file (all the while trying to remember differences between UNIX and Windows as far as suppressing the select statement, page width, line size, and feedback are concerned). I did mention that APEX is free, right?

So how do you learn how to use APEX? Ranging from free to not so free, your options are:

  • Oracle’s “2 Day +” guide for developers
  • Documentation in the APEX 3.2 release, which includes an advanced tutorials guide with 14 examples.
  • The Oracle By Example site, coincidently based on APEX, which offers an extensive set of demonstrations spanning several releases.
  • Books (most based on older versions, with 4.0 books coming out in a few months)
  • Oracle University, which offers two classes (introductory and advanced, based on release 3.0)
  • Third party consulting and training firms, which are more likely to be up to date with respect to the current release.

APEX is only getting better with time. If you need a web-based application or interface into your data, and want to empower developers and end-users (in terms of letting them do more with less overhead), APEX is the way to go.

» See All Articles by Columnist Steve Callan

Steve Callan
Steve Callan
Steve is an Oracle DBA (OCP 8i and 9i)/developer working in Denver. His Oracle experience also includes Forms and Reports, Oracle9iAS and Oracle9iDS.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles