Saturday, June 11, 2011


I've been working on a small hobby application recently with Oracle XE and finally put an APEX front end on it. I started with the default APEX that installed with 10g XE (Apex 2.2). Knowing APEX 4 has nicer features I considered upgrading sooner, but didn't see the need for my project.

The tipping point came when I tried to use make some changes using Firefox 4. Most things worked fine, except when I tried to modify a PL/SQL package. In FF4, the text was in a red, uneditable text box instead of the usual text box.

I exported my APEX schemas (FLOWS_020000 and FLOWS_FILES) as a precaution, then followed the APEX install/upgrade instructions found here: .

I was a little concerned with the documentation's description of the various components to think about when installing APEX (APEX listener, Oracle HTTP server, etc) but I expected the APEX install to figure out what I was running and take care of things since I was upgrading a functional, stock install on 10gXE. That expectation proved mostly true.

(Sidebar: I'm normally much more thorough about reading documentation before jumping into upgrades, and I like to understand what is changing before I change it. In this case, APEX is promoted as something that is easy to use and doesn't take a lot of deep technical knowledge to manage, so I purposely read as little as possible to see how well that assertion held up.)

After downloading and unzipping files, the actual upgrade process took about 45 minutes on a desktop-class machine, running Windows 7 Enterprise and Oracle 10g XE. The new APEX objects installed as expected into the schema APEX_040000, and my application was also upgraded without incident.

Thank you for installing Oracle Application Express.

Oracle Application Express is installed in the APEX_040000 schema.

The structure of the link to the Application Express administration services is as follows:
http://host:port/pls/apex/apex_admin (Oracle HTTP Server with mod_plsql)
http://host:port/apex/apex_admin (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)

The structure of the link to the Application Express development interface is as follows:
http://host:port/pls/apex (Oracle HTTP Server with mod_plsql)
http://host:port/apex (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)

After installation, application upgrades, and validation completed, I had to update the image directory using @apxldimg.sql C:\apex_4.0.2_en (because I was upgrading, not installing fresh) . I ran this twice: the first time I specified a deeper path to APEX files (second-guessing the documentation) and it failed. I re-ran with only the path to the directory I extracted files to (like the documentation said) and it worked. Trust the documentation. The image update took about 4 minutes.

The documentation contains a lot of steps specific to Oracle 11g. Since this is on 10g XE I skipped those altogether. Maybe if I want to explore a REST API I will install the APEX listener, but that is a project for another time.

I attempted to connect to the APEX administration site. I was asked to change the admin password, and I found the new APEX has much stricter password rules than before (at least for the administrator - default settings for APEX users are less strict, but easily configurable). After changing the password I could access the administration page. I saw several new panels and features to explore. I reset the password for my main development workspace, and re-connected to APEX as that developer.

Now that I was fully upgraded and ready to try things out, here's what I found...

So the first test - did this solve the problem I was having?

Yes. The path to the database object browser changed a little, but once I found it I was able to edit the package spec using Firefox 4 . In APEX 2.2 the Object Browser was on the home screen of the workspace. In APEX 4 It is under Home>SQL Workshop>Object Browser

Now the more important test - what changes did I observe in my application?
When I built my application in APEX 2.2 I used APEX internal authentication, so the password for the application user (call it "XYZ") was not the same as the schema owner/APEX workspace user "XYZ". After upgrading the IDs were merged and use the same password. When I used the new password I entered the application fine.

The look and features of my application continued to work as they always had. I found no differences in my application itself.

Finally - what did I see that wasn't there before (new toys)?
Where to begin?
  • The new APEX Administration tab has an Activity Monitor and a Dashboard with metrics at the database, application, web server, and user levels. There is a lot to explore here, and a lot that will be useful in monitoring a deployment (at least one larger than my hobby system).
  • The Manage Service tab has features for managing the system and application Meta Data and features to better support collaborative development.
  • The Team Development tab offers a host of project management features. I'm eager to dive into this and use it as a development task list, project tracking, and other features.
  • The Application Builder adds a tab for Supporting Objects which generates application deployment and upgrade scripts. This supports more structured deployments from a development server to test and production servers... much easier than APEX 2.2.
  • The SQL Workshop contains simpler (or at least more obvious to me) tools to import and export spreadsheet, text, and XML data.
These options only scratch the surface.

Conclusion: The upgrade was simple, but not automatic. Follow the documentation. It will save you from mistakes. In my case I didn't worry about the APEX listener yet, but I may explore that in the future.

The new tools and look are welcome improvements. I look forward to diving in with APEX 4.

Sunday, April 10, 2011

Oracle XE 11g Early Adopter

I was excited to see Oracle XE 11g has finally been released on OTN for early adopters. Loading it up, I was disappointed by a couple things right off the bat. I know, "early adopter" is a big red flag. (See update below. My disappointment is a result of my ready-fire-aim approach to trying XE 11g. Read the DOCS first and you won't be surprised).

First off, I had to uninstall my 10g XE since I can only have one "OracleServiceXE" service. I can live with this one - I purposely wanted to deal with the default install. The real surprise here was that uninstalling XE also uninstalled my 10g XE DATA, not just the binaries. I made a cold backup copy before uninstalling, but still - uninstallers should KEEP the data files or at least warn me. Grr.

Second off, I saw the terms and conditions were the usual OTN terms, including "don't use this for internal data processing without a license" (paraphrased). I found that odd, since the 10g XE had its own language that states "OK for production use" (paraphrased again, I didn't look up the exact language for the sake of this post).

So, 11g XE installed. (Still no notice of what the restrictions of the XE license are for 11g? Are they the same as 10g? More storage? Don't know, nothing in the download, install, or terms of service have enlightened me yet). Install is simple enough. I would have preferred an option to install clean or upgrade an existing 10g XE database, but this is an early adopter release, so I can live with that... I guess.

Command line sqlplus works... no surprise there... try SQL Developer. Surprise! SQL Developer is not working now. Why should that have changed? Now I have some investigation and troubleshooting to do.

I'm at Collaborate 2011 this week, so maybe in some of the evening downtime I'll poke around and try to get everything working again.

UPDATE: A quick search of the OTN Forums led me to the fine documentation I did not bother looking up before. In it is the familiar language licensing 11g XE for "purposes of developing, prototyping and running your applications for your own internal data processing operations".

So, any relaxed (improved) restrictions?

  • Single instance per server (no change)

  • Only uses 1 CPU (no change)

  • Supports up to 11GB of user data (up from 4GB, that is a welcome increase)

  • May use up to 1GM RAM (no change)

How about features? Here are the quick standouts from perusing the list of what is NOT available (see the documentation for the full list):

  • Pre-compilers and SQLJ

  • Java support in the database

  • Block Change tracking for incremental backup

  • Flashback (in just about any form)

  • Online table redefinition and index rebuilds

  • Tablespace point-in-time recovery

  • Rolling upgrades

  • Advanced Security

  • VPD / Label Security / DB Vault

  • Secure External Password Store

  • Client-side query, and other types of results caches

  • Partitioning

  • Transportable Tablespaces

  • Streams

  • Multimedia

  • Spatial

  • ASM

  • Resource Manager

Besides the license and feature summary, the full 11g XE Documentation set is available at

In the README document, we find why SQL Developer stopped working. SQL Developer 3.0 must be used. This is a known issue and is stated that it will be fixed when 11g XE is no longer beta. In the meantime, I was thinking of moving to SQL Developer 3 anyway, since it is no longer in pre-release status.