Oracle XE, Application Express and the great divide…

 I quite often get asked three questions

  1. What is Oracle XE?
  2. What is Application Express
  3. Which one should I use?

I usually give a standard response to the first two questions of –

XE = Free edition of the Oracle database + Preinstalled copy of Application Express

There seems to be quite a bit of confusion about what exactly XE is, how it relates to Application Express (if at all) and “which one should I use?”.

It’s perhaps easier to start with a description of Application Express. Application Express is the new product name for HTMLDB, it is a browser based rapid development enviroment that consists of two parts –

  • A set of packages, procedures and functions that are installed into a database instance
  • The Oracle HTTP server (OHS), which is essentially an Apache webserver with the mod_plsql module loaded, together with some other custom changes.

The mod_plsql module enables web requests to be processed directly by the database, via the webserver. With application express you can create an ‘application’ (essentially a website) which can contain pages which are generated dynamically by the database at runtime. Because application express runs ‘inside’ the database it makes creating a database driven website extremely fast and simple. If you already have data residing in a schema in your database, you can create a simple Application Express ‘front-end’ to that data within minutes.

One of the huge benefits of using Application Express over some other development technologies is that it completely removes the need for you to worry about things like database connections, since you are already connected to the database. For example you don’t need to implement your own connection pools, or abstraction layers as you would in other technologies since you can enter your SQL queries and PL/SQL code directly into your application without having to worry about how the database connection is going to be established, or whether you have the same session as your last connection. All of these ‘peripheral’ things are taken care of for you by the Application Express engine.

The other huge benefit of the Application Express development environment is that it is browser based, which means that all the developers need is a browser. You can develop and test your application without having to install any client libraries or software.

It is impossible to sum up the abilities of Application Express in a few paragraphs, so I won’t even try, all I will say is that I haven’t yet seen a website on the internet that could not have been written (or rewritten) in Application Express (since the end result of the Application Express engine is to produce HTML that is sent to the users browser).

So, where does Oracle XE fit into this?

Well, Oracle XE is a 100% free edition of the Oracle database. It is free to use and to deploy. It has many of the features available in the other editions of the database (such as Standard Edition One, Standard Edition and Enterprise Edition).

Many people in the past have been put off using Oracle as a solution due to it’s ‘perceived’ high licensing costs. I say perceived, since many people look at the price of an Enterprise Edition licence without considering that they may not have required all of the functionality available with the Enterprise Edition and therefore could have purchased a licence for one of the other (cheaper) editions, such as Standard Edition One.

Oracle XE however is 100% free, which means that where people may have chosen MySQL since it was free, they can now choose to use XE.

Oracle XE does have some limitations, which is to be expected when you consider that there is a clear upgrade path from XE to the other editions of the database. The limitations are as follows:

  • Supports up to 4GB of user data (in addition to Oracle system data).
  • Single instance only of Oracle Database XE on any server.
  • May be installed on a multiple CPU server, but only executes on one processor in any server.
  • May be installed on a server with any amount of memory, but will only use up to 1GB RAM of available memory

Those limits will actually still be within lots of peoples requirements, for example a huge number of websites that are currently running and using MySQL won’t need anywhere near 4Gb of data storage. Also given the speed of current processors, the fact that XE will only execute on a single processor is not a great cause for concern. Also the limit of using up to 1Gb of RAM will not affect the vast majority of people likely to be using XE.

XE is also extremely simple to install and is currently available for the Windows and Linux operating systems (which obviously covers a huge potential customer base).

As I mentioned before, XE also come with a version of Application Express preinstalled. So you get the same (with a few slight differences) Application Express development environment under XE as you would with Application Express installed into an Enterprise Edition of the database.

All of this means that XE gives you a superb, zero cost way of designing and building dynamic database driven websites. You can download today, for free an edition of the database that is simple to install and gives you all you need to produce websites ranging from a simple home page through to a full eCommerce solution (or anything else you care to think of).

If you then find that you outgrow the limitations in XE, such as needing more than 4Gb of data, or you want to make use of more processors you have two choices –

  1. Upgrade the XE database to one of the other editions, such as Standard One, Standard Edition or Enterprise Edition
  2. Export your application and the corresponding data and install it into an edition of the database (SE One, SE, EE etc) that has Application Express installed into it.

This sort of migration can literally take minutes (assuming you don’t have GB’s of data to migrate). It also means that your application can then take advantage of features that aren’t available in XE, such as Virtual Private Database or XQuery.

It also means that you can easily take advantage of hosting companies such as that host production Application Express applications. You can develop your website on your home PC in XE for no cost, once you are happy that your application is ready to be deployed you can export it and upload it to the Application Express server with a few clicks of your mouse.

There is also a bit of a ‘myth’ that Application Express is only really useful for replacing Excel spreadsheets or Access databases. I would disagree with that completely, what you can do with Application Express is only limited by your imagination (within reason!).

If you’re currently thinking of using Java, or Ruby on Rails, or any one of those other web development technologies I would seriously suggest taking a look at Application Express, or even better have someone who is already familiar with it show you exactly what  it can do. You may just be surprised at how much time and money it could save you.

If you’re wondering why I put “and the great divide” in the Title of this post, it’s because I think a lot of people who are using XE are unaware that their applications are compatible with a database instance that has Application Express installed on it. There are two different Oracle forums, one specifically for XE (note you will need to register to be able to access it) and one for Application Express and many people tend to only look at one forum or the other, without realising that posts in the other forum may be helpful to them.

I think there’s a great future for Oracle XE and Application Express just so long as people continue to find inventive and new ways to use them.

3 thoughts on “Oracle XE, Application Express and the great divide…

  1. Colin Sheppard

    Nice article John! I’ll direct some (MS Background) people I know who are curious about Apex to it.

    The assertion that

    “There is also a bit of a ‘myth’ that Application Express is only really useful for replacing Excel spreadsheets or Access databases.”

    I sometimes feel that way as its often the way its prominently ‘marketed’ with a focus on converting existing Microsoft Users to gain marketshare. You find that is some of the early presentations, blog posts, and hot-to style tutorials. I think Oracle created that ‘myth’ perception unintentionally themselves. (Personally, some of my applications start with a spreadsheet to work out the basics of Database tables with.)

    But I feel now Apex is going into a new era, where you can favorably compare it with the likes of Ruby On Rails and other frameworks with different criteria, like performance and price as you mentioned, but also stability, security, and reliability.


  2. Rahul Dixit

    Our client wants to upgrade htmldb 2 to Apex 3. I’m an oracle dba and have never done this before.
    The htmldb application is on a separate server as the database( it is using. The doc’s I
    am using assume, I think, that the database and the application are on the same server. Am I
    totally wrong or just havent found the right documentation ?
    In short, can the apex application and database be on separate servers ?



  3. John Scott

    Hi Aru,

    Your APEX applications runs within the same instance that APEX is installed into, however it (your application) can access data in other instances by using database links etc.

    Hope this helps,




Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s