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.