Monday, July 04, 2011

You shouldn't use Oracle Application Express because...

I always revel at the myriad of untruths and misinformation given about Oracle Application Express and the reasons why you shouldn't use it. Some of the most common and my favorites are:

  • It can't scale. Most people know this is my favorite to refute.

  • It's only suitable for Access and Excel replacement.

  • With the Sun acquisition, it's going to be all Java all the time. And APEX is not Java. (An Oracle partner told me this, not aware that I work for Oracle).

  • It's written in SQL and PL/SQL. It can't really be all that extensible. It's in SQL, after all. (This gem came to me from Oracle's own Siebel CRM team).

Well, I have a new one to add to the list. At the recent ODTUG Kscope conference in Long Beach, CA, I met with two partners who have been building an elegant application for use by the United Nations (yes, those United Nations). In their presentation, one of the attendees raised the issue of Oracle's commitment to APEX. He raised the point of how most Oracle products are labeled as 11 release whereas APEX has its own release numbers. For him this must be a sure sign of long term non-commitment and preference to ADF.

Let me state a few things:

  1. The difference in APEX version number has zero (as in nil) bearing on any direct or indirect commitment by Oracle for APEX

  2. We have a major revision of Oracle Application Express at least once a year. The database does not release that frequently. It's beneficial to not have the same version number, otherwise, we'd have to sit and cool our heels between 11gR1 and 11gR2, or 11gR2 and Database 12.

  3. The Oracle Database ships with Oracle Application Express, and has done so since Database 10gR1 - all the way through 10gR2, 11gR1, 11gR2 and Database 12.

  4. With respect to the forthcoming Database Express Edition (XE), to reduce the size of the download and size of on-disk distribution, the early decision was to not include Oracle Application Express. But the Senior VP of Server Technologies responsible for all of Oracle Database asked for APEX to be put back in and included with XE.

Oracle Application Express was first started in 1999. Oracle HTML DB and Oracle Application Express have been in constant development since the first supported production release in 2004. The versions and year of release are:

HTML DB 1.52004
HTML DB 1.62005
HTML DB 2.02005
Application Express 2.12006
Application Express 2.22006
Application Express 3.02007
Application Express 3.12008
Application Express 3.22009
Application Express 4.02010
Application Express 4.12011*

I think there's only one thing to safely conclude about this person who doubted Oracle's commitment to APEX solely because of the difference in version numbers....he has no idea what he's talking about.


Byte64 said...

which version of Apex are you going to ship with XE 11g?


ʬʬʬʬʬʬʬ said...

Hello Joel. We have invested in APEX for the past few years, and developed some pretty successful applications, but after careful consideration we see that APEX has many weak points that we can't seem to work around, and recent Java "frameworks" (e.g. grails and gwt) are now very attractive. We are now having an internal debate on where to go next, since we also have many java applications, and it seems that APEX will not come out as a strong candidate.

Considering that I do like many things in APEX and PL/SQL (I've taken the certification), I will give you my strongest arguments *against* APEX, and would like to see your best defense. Maybe you can share your internal best practices since the "APEX back office" (the dev interface) is one of the most complex APEX applications out there, and it works very well, so I'm sure you can respond to most of these points.

In my opinion APEX does have it's niche, where it performs (rapid development, ...) better than the competition, but our biggest problem is that it doesn't scale in COMPLEXITY.

The reason for this is that it doesn't use, right at its core, basic "Object Oriented" concepts that have proved successful over the past 20 years in designing complex application: encapsulation, reuse, inheritance, ...

APEX "Objects" (items, processes, ...) cannot really be shared or extended. The move for us has been putting as much as possible in the database, engineering the infrastructure we needed in packages, and emptying APEX of actual sql and pl/sql. Our APEX applications have processes that consist in one straight call to the needed pl/sql APIs. This way we were able to reuse a good deal of logic and have decent debugging possibilites, but still this suffers from very severe drawbacks: pl/sql has very awkward object oriented capabilities (we don't use them) and packages offer a very primitive form of code organisation (e.g. no namespaces of arbitrary length), and cannot be extended using something that looks like inheritance. APEX plug-ins were an interesting move in this direction, but they're still very primitive compared to what you can do in the java world.

Moving our code to the DB also had another side effect: it is very hard to have many developers working on the same project. Usually java developers run their own webservers on their development machines, but having everybody run his own oracle db is a lot more complex. Also, the packaging and deploying tools of pl/sql is also very primitive compared to the java world (e.g. maven, archiva, ...), and it would be hard to deploy their work to a shared dev db in a programmatic way without having to reinvent the wheel.

APEX "source" control is very basic (export, split, check automatically into svn, but you cannot easily see who's done what and when, and you have to look at the internals code used by APEX to export its applications). APEX also doens't offer any kind of access visibility: all the "items" are public inside the application, and you have to rely a lot more in "practices" (naming conventions) to make sure you don't create a spaghetti application.

So we are stuck with APEX which is very good only for the rapid development of simple CRUD applications developed by a single developer. We probably have something like 30 such applications in production and we always end up with a lot of "copy & paste", which is the root all programming evil (more than premature optimisation!). While this niche might fit exactly the needs of some companies, it is quickly becoming irrelevant for us.

On a similar point, we are taking a hard look at Oracle's Fusion Apps. I would be curious to know how much they rely on pl/sql (if they use it at all) for those applications, and it seems bizarre for Oracle to treat their own (incredibly powerful) database as simple "data store".

Thanks for your time

Joel R. Kallman said...


APEX 4.0.2 will ship with XE.


Lev said...
This comment has been removed by the author.
Lev said...

Hello Joel,
I agree that APEX is an excellent tool and I think that it can go far beyond basic CRUD and it does scale in complexity - because there is less complexity.

But as any other tool it has its weak points and in my opinion it's lack of source control. Unfortunately
current statement of direction is not promising - there is nothing about it.

The biggest APEX application - is APEX itself. Could you share how Oracle team manages source code? Probably we are just missing something and there are efficient ways to manage code.


Joel R. Kallman said...

Hi ʯɲʑɩʛʯɖʋɪʉ ɕɑʒʝɪɪʧʠʘɶ,

Thanks for your comments. I can't really comment on Grails or GWT, as I have only superficial knowledge of both of them. As I understand it, though, these are frameworks definitely geared towards *programmers*, probably no differently than Ruby or Django or web2py. I'm not sure I would classify the typical APEX developer as a developer of these other frameworks. It's possible, but unlikely.

If object-oriented programming fits your need, then you're correct - Application Express is probably not for you. And that's fine - it really isn't intended to be a solution for all problems.

The one thing I found odd is your statement of " having everybody run his own oracle db is a lot more complex." I completely agree! I would never recommend that to anyone. Most development teams develop on the one hosted development database instance, for all developers. Asking each developer to maintain their own database and schemas and Web server is a bit much.

Thanks again for your feedback, though. I truly appreciate the perspective.


ʬʬʬʬʬʬʬ said...

Thanks for your answer. Could you please shed some light on your internal practices? How is the APEX dev interface developed? Do you keep the sql and pl/sql inside APEX (typing into textareas ... how do you debug that dynamic sql?)? Or do you put as much as possible in the db with packages/functions/procedures? In that case, since developers share the same development database, how do you make sure they don't disrupt each other's work?

Sohil Bhavsar said...

I love APEX. You can do a lot of things with APEX. It is very much scalable.

There are some Tips and Tricks which will be helpful for developing APEX Applications and which you can master by experience and reading blogs from experts of APEX.


Sohil Bhavsar.

Morten Braten said...

Hi "Guy with strange Unicode name",

It sounds like you are doing the right stuff (pushing things to the database, using packages, plugins, etc.), so I am curious as to what kind of problems or business requirements you are unable to implement without resorting to the "encapsulation, reuse, inheritance*" offered by Java or other OO languages?

Do you have any concrete examples of what type of complexity we are talking about here?

I've worked on several business-critical applications written in PL/SQL, with up to 200,000 lines of code, and I've never had problems organizing the code using packages.

(I've often found myself wishing that Oracle allowed object names with more than 30 characters, but you work your way around it.)

Some say that Apex (and shared database) development becomes impractical if you have many (lots of) developers working on the same project. That may be true, but typically it seems that you don't need a lot of developers to deliver the goods, because the Apex environment and PL/SQL itself is so productive! As long as each developer is responsible for separate modules of an application, I don't see how conflicts would arise on a frequent basis.

- Morten

* And it should be said that PL/SQL packages offer encapsulation and reuse; while views, pipelined functions and ref cursors offer re-use. So the only thing missing compared to the typical OO toolset is inheritance, which has its benefits but certainly also its own problems.

ʬʬʬʬʬʬʬ said...

Hello Morten and thanks for your comment. We can do what we want, but it's becoming very tricky.

The "encapsulate" comment was directed at APEX and not at PL/SQL. In APEX all your "items/variables" are accessible in all pages. A good idea for a small app, not so much when you have to start relying on naming conventions making up for the lack of access visibility. Inheritance is good in many scenarios (e.g. you have in input element with some particular behaviour linked to it but you want to be able to use it somewhere else in a slightly different version). We're working around this by having functions that create these UI elements, and these functions take lots of parameters ...

we should probably split the application in more and more packages, that way we would have less problems with multiple developers, but again it's not easy to do the split, when your apex application has dynamic dependencies on these packages.

pl/sql has 3-level namespaces (schema.package.function), and we would like to keep the app code in one schema, so we're down to two levels. Everything else is put in the naming conventions of packages and function. Again very ugly.

Last thing I decided to try was using a bare apex app, and doing all the communication via json, using apex essentially as a thin layer to guarantee authentication, run the pl/sql linked to the various "services", and return the values via json (using the undocumented json_from_sql function). It works well, but it's really emptying apex of almost everything. The UI had particular requirements so it was a good use-case, but I don't know if it's a route I'd like to travel for a different app.

Maybe we could use something like google's closure to do proper JS (with all the nice features of modern language) and let the db do what it does best, running it's pl/sql, and APEX acting as a thin (but highly configurable) layer in the middle....

maybe the APEX people should move their rendering engine to JS, so the user could "hook" into their nice features and items in a proper way, inheriting from them and so on ... pl/sql is just not good for that. Your APEX application would then become a "pre-compiled" JS that the APEX back-end produces from the metadata that describes the application itself.

As Joel said they are not targeting this kind of developers, a move in such a direction might not make much sense on a cost/benefit perspective.

Morten Braten said...

"Everything else is put in the naming conventions of packages and function. Again very ugly."

Well, "ugly" is in the eye of the beholder. For example, the Win32 API is full of "ugliness" with strange conventions, inconsistent naming and general spaghetti, yet people manage to use it (either because they have to, or because it offers some other benefits).

Trust me, there are worse conventions than having to name your items after the page they belong to...!

And to me, Objective C looks really ugly -- its OO features don't tempt me, because I mostly work with data-driven applications, where PL/SQL's (non-OO) features are much more useful.

Here are some of my thoughts on structuring PL/SQL code, in general and for use with Apex:

With regard to Apex as a tool, the fact that you can create apps like the JSON-powered experiment you described, speaks to the strength and flexibility of Apex to make it whatever you need.

So I say, leverage PL/SQL packages for data processing, and pick from Apex what you need in terms of authentication and authorization, session management, navigation and templates, Interactive Reports, Flash charts, auto DML (for those quick CRUD apps), and so on.

- Morten