JavaData & JavaOracle XE: It's Not Your Typical Oracle

Oracle XE: It’s Not Your Typical Oracle

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Suit vs. Jeans

Try a visualization. Think about Oracle Database. If you are anything like me, you are probably envisioning large, mission-critical applications, darkened server rooms with loud fans and lots of heat, and DBAs sweatily laboring to keep the enterprise running. You might, if you have had the experience, also get a sinking feeling from the installation, configuration, and administration of the beast (in other words, oh no, not all those steps to run through again).

Now think about MySQL. You will probably think of Web applications (particularly the new Web 2.0 everyone talks about), fast and lightweight development, simple clean tools, and even simpler and cleaner installs. (It is just there on most Linux distros these days, and Windows installation is just a double-click away.)

Well—maybe I hit with this and maybe not, but this was certainly my thinking, up until a couple of weeks ago, anyway. MySQL has been adding more and more capabilities as time goes on, such as stored procedures and triggers, but for the enterprise, Oracle still rules the roost. On the other hand, the plethora of new lightweight Web applications out there tend to target MySQL; it is available on just about every hosting service out there, and is just easier to get up and running for your own development purposes.

It is the latter that appears to have been worrying Oracle for some time now. They are aware that the “cool stuff” is happening on MySQL (and to be fair, possibly Firebird or Postgres too). Clearly, Oracle needed to do some re-thinking.

The result of this re-thinking? Oracle XE (Express Edition). When I first heard about XE, my reaction was “Well, this will just be some warmed-over version of Oracle trying to stem the flow of the cool kids to MySQL.”

There are two things wrong with this analysis:

  1. I don’t believe their is a “flow” of cool kids from Oracle to MySQL. The cool kids are already using MySQL, and I don’t think that many come from Oracle; they simply start out with MySQL and stay there. Oracle, with its licensing, heavy install process, and requirements, is simply not in the running.
  2. After examining Oracle XE, it is a lot more than some half-hearted, warmed-over TV dinner made up of Oracle 10g leftovers. In fact, as blatant an attack as it might be on MySQL, it is a darned good one! As I will explain in the course of this article, there is a lot to like here. In fact, there is a lot that it does better than MySQL.

Oracle XE vs. Oracle Suit

The Oracle Universal Installer… why did they do it to us?

Actually, to be fair, the universal installer was only part of the problem. Anyone who has installed Oracle 10g (or even worse, 9i) on Unix (particularly Linux) will feel the pain here. Ten-page installation documents with user and group setups, kernel parameter tweaking, compile dependencies, and weird, platform-specific errors. Sure, you can get used to it, you can even get the Linux install fairly repeatable and reliable with a bit of effort and learning, but it’s not easy, and it’s ugly.

Of course, if you needed Oracle for your business, it was a pain that had to be borne. But if you didn’t? Well let’s just say it’s hard to imagine someone installing Oracle “for fun.”.

Windows users had it easier, but it was still far from perfect. The universal installer asks a myriad of questions that you pretty much give the same answers to (at least for a quick “development” install).

In fact, many times in the past I bemoaned the fact that Oracle seemed to ignore the excellent installation options available for Linux—RPMs and/or apt-get are a far easier and quicker way to go.

So, my first pleasant surprise is that Oracle XE finally does this. The universal installer is nowhere to be seen! Packaged versions are available for Windows 32 and many of the popular Linux distros: Debian, Mandriva, Novell, Red Hat, and Ubuntu.

The install was a breeze, not a compatibility library in sight, no DBA group or Oracle user set up. (Actually there is, but it is done for you which, when you think about it, is what computers are supposed to do anyway, right?) The package install takes less than a minute.

Of course, a successful install was only half the battle with Oracle 9i or 10g; afterwards came the configuration step. Administration assistants for several different phases had to be run, to create tablespaces, set up DB sys and user accounts, set up the listener details, and so forth.

Happily, this process has been improved by several orders of magnitude as well. On Linux, you just have to run a script after the package install has completed. The script is mentioned at the end of the package install, so you just have to copy/paste the command. You then have to answer four questions: port to run the http server on, port to run the listener on, select a sys/system password, and whether you want the database to be started automatically on bootup.

A full Linux install takes way less than five minutes. Windows is similarly easy; the questions are asked by the installer .exe and it even skips the port number questions unless something is already using those ports. Windows services for Oracle XE are installed automatically.

What’s Included?

If you work on enterprise applications, no matter what technology you use, the chances are good that there is a database of some kind at the core. For quick development projects, this has in the past been MySQL for me.

Tooling for MySQL is the primary reason I use it. Compared with, say, pointbase (shipped with the Sun development tools right now, although being replaced by Derby soon), MySQL has a lot more choice in the tools space. MySQL themselves have a GUI admin tool and query browser which, while basic and a little unintuitive, are ever so much better than nothing. In practice, I tend to use phpmyadmin most of all—this is a separate project that provides a Web-based application that combines most of the functionality of both the admin and query browser. All of these tools have to be downloaded and installed separately to MySQL itself.

The good tooling options mean less time to set up demonstration data, import data from various sources, or design new schemas. Because I dabble in databases only as a way of supporting development, my SQL knowledge is only passable, so having tools that help me define tables and queries quickly is a serious advantage.

The great news is that Oracle XE shines in this area. An excellent Web-based application for administration and query building, as long as an awful lot more, is included in the XE install. This means that the “out of the box” experience for XE is quite a lot better than with MySQL.

On top of this, the Web app is excellent. Frankly, it’s much better than I have come to expect from Oracle bundled software. (These are the guys who used to install the database and leave you with SQL/Plus as the primary tool for administration, remember.)

In fact, the Web app bundled with XE includes the following functionality (and a lot more):

  • Common system admin functionality, including schema and user creation
  • Database monitoring (load levels, resource limits, statistics, top SQL queries, sessions, and so on.)
  • Create/Edit tables, sequences, indexes, triggers, views, functions, stored procedures, packages, and the like.
  • Important note: Stored procedures and full packages are included in Oracle XE.

  • SQL editor and Query Browser (heavy on the AJAX—more below)
  • Dump and Restore database, generate DDL
  • Import/Export to Text, Spreadsheet, or XML
  • Application builder with example applications ready to install. Again, more below

As you can see, the bundle includes quite a lot, and useful functionality at that.

The other thing worth mentioning is the integration into the operating system/desktop environment. This is a fairly new thing for Oracle, especially on Linux. Start menu entries include start and stop database, get help, and go to the database home page.

Gotcha: If you need to get back to the home page, the start menu is one way to do so, but the direct URL is http://localhost:8080/apex. (This assumes that you didn’t change the default port 8080; if you did, adjust the port number accordingly.)

Some Choice Features

The best advice I can give at this point is that, if you are still reading and hence probably interested, go and download Oracle XE. However, there are a few favorite features I would like to touch on.

Table Design

This is a nice table creation wizard that leads you through the table field definitions, primary and foreign keys, other constraints, and then lets you review the SQL before creating if you desire. It even makes it easy to include a new sequence to automatically generate a primary key value.

Query Builder

This is, to date, my favorite implementation of a Web-based query builder. It uses the simple and familiar “access” type view of tables, relationships, and selected fields:

If you have used Microsoft Access, Java Studio Creator, or any of a host of other tools, this type of query builder view likely will be familiar to you. The implementation here is very slick—AJAX lets you join tables and select fields on the fly, and see the changes in the generated SQL or conditional views immediately. You even can drag and drop the tables to tweak the layout for clarity. Hit the run button, and you will see the results of the query in the results pane.

You then can store queries, or simply copy the query SQL out of the SQL view and paste it into source code for the application you are writing.

Application Builder

To be honest, this falls very much into the “neat, but will I use it” category right now. In a nutshell, this is a canned, template-driven facility for rapidly putting together Web-based applications around your database with little or no code to write. There are three included example apps that you can install to the database and examine. The approach taken is to use wizards to define pages with enclosed objects, objects being things such as queries, forms, reports, and charts, as well as conditionals that control flow through the application, among other things.

Real developers might get some usage out of this, but I suspect that business staff who want to throw some of their own reports and simple CRUD (Create, Read, Update, Delete) applications will find it a lot more useful.

Many of the newer RAD tools emphasize the speed of construction of CRUD and reporting tools, and this appears to hold its own in ease and speed to many of the new options out there. Experienced developers will chafe at the bit from limitations and the approach taken, but there is no denying that it is easy and fast to throw together a simple app this way.

The definition for the page demonstrated above is:

Different links in the various sections define the components displayed, SQL queries used, conditionals like branching to other pages, security, and shared parts of the application (navigation bars, tab sheets, and so forth).

Something like this could help at the very least with prototyping, even if the finished product is constructed in something else. Likewise, it could be used by business folks or customers to create and tweak their own reports.

Compatibility

If an enterprise application environment only supports one database, it’s usually a safe assumption that the one database is Oracle (unless it comes from Microsoft, perhaps). In the testing I have done, Oracle XE works everywhere I would use Oracle. There are some features that are missing, but these are not highly used features and certainly are not cross-database features. For example, there is no provision for writing stored procedures using Java in Oracle XE, but that is a feature that is not at all common outside of Oracle’s database anyway.

I have tested Oracle XE with all of the application environments I am using at present, and it works across the board. It is fairly straightforward to set up and use with Ruby on Rails, and works easily with NetBeans and Java Studio Creator, as well as JDeveloper, naturally. Even the persistence options I tried in Eclipse worked with no problem. This looks, feels, smells, and tastes like the full Oracle as long as you don’t try and use the enterprise features.

This is quite an advantage for XE over MySQL. Certainly within the company I work for, and with our customers in particular, Oracle is king. Any application we write that uses a database has to be compatible with Oracle. And, if you write an application using XE, I think it is a pretty safe bet that it will work with full Oracle without much—if any—work.

Gotchas

These are just some general points that might save you a bit of time and head scratching if you decide to give Oracle XE a go:

If you are coming from a MySQL background, Oracle works a little differently to what you might be used to. Oracle has several identifying pieces of information for a server: the host name, the port on which the listener is running, the SID, and the Service Name.

When I installed XE and started trying to use it, I did not know what the SID or Service Name for the database were. All of the various external applications need to know this to allow the drivers to connect.

Also, there are two common types of driver for Oracle, the Java native (thin) driver (which is slightly faster the last time I checked) and the OCI driver used by C, PHP, Ruby, and other languages. The Java thin driver hooks up directly using host, port, and SID information presented in a JDBC URL. The OCI driver goes through a mechanism based around looking up the details in a tnsnames.ora file.

For the default install, the SID, Service Name, and TNS NAMES entry are all simply XE.

If you use Rails, and want to connect it to Oracle XE, first install the Ruby OCI drivers. Details on these can be found in the link above. After the installation edit your database.yml file and use the details:

adapter: oci
host: XE

You also will need a valid user name and password for the database, naturally.

Secondly, do not try and install Oracle XE on a machine with a full Oracle installation on it. It may choke on the install because the full Oracle processes are already sitting on the ports it needs. Also, even if the full Oracle installation is not running when you install XE, we saw an instance where the full Oracle installation was disabled by the XE installation.

Finally (and this is a detail you may or may not need to know, but I include it in case it is useful), Oracle XE, like full Oracle, has more than just an on/off state for the database. In fact, there is a process called the oracle TNS listener that runs and can be used to start and stop the databases.

To use the Start Database option in the start menu on Linux, I found it was necessary to already have the TNS listener running; otherwise, the Start Database operation failed. This means that you cannot remove the service completely and just start and stop it from the menu. If this confuses you, please don’t worry about it; however, if you (like me) like to trim down the number of services run automatically during boot, be aware of this.

The Catch?

Okay, so there has to be a catch, right?

And there are some, given the space at which this offering is targeted, I don’t believe any of them are prohibitive to using Oracle XE.

First, let’s start with the good:

  • It’s free to download and develop on
  • It’s free to deploy (including distribution, as long as you make the other party aware of the licensing details)
  • It is the first really simple installation on either Windows or Linux that I have ever seen Oracle do
  • The included tools are superb and very modern and AJAXy

However, there are limitations:

  • Although you can install it on a multiple CPU machine, and it will run on one, it will only use one CPU on that machine
  • It will only use a maximum 1 gigabyte of memory (again, it will run on machines with more memory; it just won’t use it)
  • It has a database size limit of 4 gigabytes for user data (this is all available to the user; system data is counted outside of that 4 gig limit)

These limitations sound pretty serious, until you remember one thing. In the all important SMC (Small and Medium Companies) PC development space, the most widely used database is either Microsoft Access, or the MSDE (Microsoft Database Engine). Both have an upper limit of only 2 gigabytes of data storage, and it doesn’t stop them being used all over the place.

In fact, for development work, none of these limitations are a problem, and even for small to medium applications, they should not prove limiting. You will not be able to run a massive multi-user enterprise system for sure, but then that’s where the big Oracle offerings come in. Certainly, I can see developing such a system using Oracle XE, and then deploying onto a bigger server when you need the size (at which time, a for-pay version with support is probably a good idea anyway).

Also, I actually find the limits a welcome addition in a strange kind of way. In the past, Oracle has displayed a bad habit of systematically taking over any machine on which it is installed, becoming an enormous resource hog. I am sure that a good DBA could prevent this, but I am just a developer who wants to write something quickly, and in my experience, Oracle has always been a bit of a resource hog.

This is the first Oracle database I have installed, with the default settings, That has not taken over my development machine. In fact, it is behaving as a model citizen on my laptop, living in harmony with all of the other applications and development environments I run on it. In fact, I have been running Oracle XE constantly on my laptop for well over a week (I suspend the laptop in between uses rather than rebooting), and Oracle is still using only 6.6% of the memory, even though I have been hitting it to write this article and try it out with a number of different development environments.

Other important limitations worth mentioning are that XE is only available for 32-bit Windows and 32-bit Linux right now. I hope that Oracle will release it for Mac, and in 64-bit flavors for both Windows and Linux. If Oracle really wants to go after the hip crowd, someone needs to tell them that Macs and 64-bit chips feature pretty heavily in that crowd.

Conclusion

As I mentioned at the start of this article, I came into using Oracle XE with some pretty heavy pre-conceived opinions. In the past, Oracle has proven itself to be powerful and competent but also heavy, extremely tricky to install, and basically kind of “square.” I thought XE would be a half-hearted attempt to make Oracle cool and relevant to the new Web 2.0 crowd. In fact, I find myself delighted to be totally wrong in my pre-conceived ideas. Oracle XE is an extremely good product, and so “cool” it’s kind of hard for me to believe it came from Oracle.

So, kudos to the Oracle guys. Now, please take some of the lessons learned from this and apply them to the “real” Oracle. If nothing else, XE proves that installing, configuring, and administering Oracle does not have to be like performing heart surgery on yourself.

Just one more thing before I go. If you do decide to grab Oracle XE (and I hope you do; there is nothing to lose by trying it out for yourself except for a little time, and maybe a pre-conceived notion or two), do yourself a favor and grab Oracle’s SQL Developer at the same time. This is a pure Java application, based on a kind of cut-down version of JDeveloper. It is free to download and use, and it fits hand in hand with XE, giving you a desktop application that you can use to configure and develop in. I may cover Oracle’s SQL Developer in a future article; in the meantime, just grab it and give it a try. It’s a bit more advanced than the Web tools provided with Oracle XE, and hence might be useful as you start to get more ambitious.

Further Reading and Links

About the Author

Dick Wall is a Principal Systems Engineer for NewEnergy Associates, A Siemens Company based in Atlanta, GA that provides energy IT and consulting solutions for decision support and energy operations. He can be reached for comment on this and other matters at dick.wall@newenergyassoc.com. He also co-hosts The Java Posse, a podcast devoted to Java news and the Java community. It can be found at http://javaposse.com.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories