Friday, November 19, 2010

Oracle Application Express 4.0.2 released

Today, Oracle Application Express 4.0.2 was released. This corrects a number of issues discovered in the original Application Express 4.0 release as well as the Application Express 4.0.1 release. In the README of the Application Express 4.0.2 patch set, there is a listing of all of the bugs corrected in this patch set. You can view a copy of it here. The patch set is cumulative and includes all of the patches and bugs fixed in the Application Express 4.0.1 patch set.

We have also repackaged the Oracle Application Express full distribution. If you have never installed or upgraded to Oracle Application Express 4.0, you can download the latest full distribution and install this and you'll have Application Express 4.0.2 out-of-the-box. Since this always creates confusion for customers (do you need the patch set or the full distribution?), use this guide:

  1. If you have Oracle Application Express release 4.0 or Oracle Application Express 4.0.1 installed, download the Oracle Application Express 4.0.2 patch set from My Oracle Support and apply it. Look for patch number 10173973. Even though the file is named, seemingly only for DB, it can actually be used against any supported database version running Application Express 4.0.

  2. If you have Oracle Application Express release 3.2.1 or earlier installed (including Oracle HTML DB release 1.5), download and install the entire Oracle Application Express 4.0.2 release from the Oracle Technology Network (OTN).

  3. If you do not have Oracle Application Express installed, download and install the entire Oracle Application Express 4.0.2 release from the Oracle Technology Network (OTN).

And how do you determine what version you're running? Well, if you're a DBA, you can connect to the database as SYS using SQL*Plus and issue:

select version from sys.dba_registry where comp_id = 'APEX';

If you can login to the Application Express development environment, you'll see the version number in the lower right of every page.

As mentioned in an earlier post, there are two new and very polished themes included in Application Express 4.0.2. A lot of work has also gone into correcting the display and usability issues of all of the modern themes.

Oracle Application Express at had the final patch set applied today, November 19, 2010.

Sunday, November 14, 2010

Did You Get Your Free Server, Yet?

Did you know that Amazon Web Services is giving away free virtual servers? I got mine yesterday. As of November 1, 2010, Amazon Web Services has a free usage tier for a year if you sign up for a new Amazon Web Services account. You can sign up and read more here. According to Amazon:

"To help new AWS customers get started in the cloud, AWS is introducing a new free usage tier. Beginning November 1, new AWS customers will be able to run a free Amazon EC2 Micro Instance for a year, while also leveraging a new free usage tier for Amazon S3, Amazon Elastic Block Store, Amazon Elastic Load Balancing, and AWS data transfer. AWS’s free usage tier can be used for anything you want to run in the cloud: launch new applications, test existing applications in the cloud, or simply gain hands-on experience with AWS."

If you've ever wanted to experiment with a server in the cloud and experience the robust offering that Amazon Web Services has in this space, now's your chance. We have had tremendous success running the Early Adopter programs of Oracle Application Express on Amazon Web Services.

Saturday, November 13, 2010

Application Express 4.0.2 applied to

On Friday evening, 12-NOV-2010, a pre-release version of the Application Express 4.0.2 patch set was applied to A listing of all issues corrected in this patch set will be published next week when we release this patch set on My Oracle Support.

Normally, new features are not included in a patch set but there are two new themes included in APEX 4.0.2 (themes finely crafted by Shakeeb).

Theme 21

Theme 22

Wednesday, October 27, 2010

Application Express, Network ACLs and Oracle Database 11gR2

In Oracle Database 11gR1, a new feature was introduced called Fine-Grained Access to External Network Services. Succinctly defined, this feature gives an administrator control over which database users are permitted to access external network services, and on which ports. If an application relied upon the PL/SQL packages UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP or UTL_INADDR, they would now need to be given permission to access the external network service via a Network ACL. An excellent writeup of this feature is on ORACLE-BASE.

There have been two changes in this feature in Database 11gR2 which may impact Application Express users. These aren't necessarily documented so prominently in the release notes or README of Database 11gR2, so I felt it necessary to share them here.

  1. In Database 11gR2, the precedence order in evaluation of the network ACL entries has been changed to most specific to least specific. More about this below.

  2. In Database 11gR2, the network ACL now applies to any use of DBMS_LDAP.

In the installation guide for Oracle Application Express, we document this feature in Oracle Database 11gR1 and also how to create a network access control list which permits the APEX engine to access any network service on any network port. The name of the ACL in our example is power_users.xml. It just so happened that someone else on our instance of Application Express needed access to an outbound HTTP proxy, so I created a separate network ACL for this user.

The Network ACL privileges looked like:

SQL> column host format a30
SQL> column acl format a40
SQL> select host, lower_port, upper_port, acl from dba_network_acls;

------------------------------ ---------- ---------- ------------------------------------ 80 80 /sys/acls/proxy_rule.xml
* /sys/acls/power_users.xml

SQL> column acl format a40
SQL> column principal format a30
SQL> select acl, principal from dba_network_acl_privileges;

---------------------------------------- ------------------------------
/sys/acls/proxy_rule.xml SOME_OTHER_USER
/sys/acls/power_users.xml APEX_040000

Prior to, if APEX_040000 needed to access the outbound HTTP proxy, this would be evaluated in terms of the least specific rule to the most specific rule. The ACL power_users.xml permitted access to all hosts on all ports, and thus, there were no issues. But in and higher, this is now evaluated from most specific to least specific. If APEX_040000 now needs to access the outbound HTTP proxy, it is blocked and we'll encounter the dreaded error message "ORA-24247: network access denied by access control list (ACL)". A match for the host in the outbound HTTP proxy is found in /sys/acls/proxy_rule.xml, APEX_040000 does not have privileges on that ACL, and now the fine-grained access control blocks the request.

To correct this, I had to grant privilege on the proxy ACL to APEX_040000:

acl => 'proxy_rule.xml',
principal => 'APEX_040000',
is_grant => TRUE,
privilege => 'connect' );

To address the second point, in, access to the host and port specified by methods in the DBMS_LDAP PL/SQL package are now controlled via this same fine-grained access control to external network services.

I can understand, from a security perspective, why this behavior was changed. However, if you've come to rely upon this behavior, upgrading to Oracle Database 11gR2 may introduce some changed behavior when accessing external network resources.

Wednesday, October 20, 2010

Custom Authentication Scheme for Oracle Application Express and Oracle Access Manager - Addendum

As mentioned in my earlier post about Oracle Application Express integration with Oracle Access Manager, Dilip Gowda, an Oracle consultant, very kindly shared a Word document detailing all of the steps he performed to get Oracle Access Manager 10.1 working with Oracle Application Express 3.2. It can be downloaded from here. The custom authentication scheme for APEX should work with any APEX version - it's a generic header variable authentication scheme.

Once I get Oracle Access Manager 11gR1 installed and configured, I hope to prepare and share a similar document. The eventual goal is to turn this into an official whitepaper and then ultimately provide an out-of-the-box header variable authentication scheme in a future version of Oracle Application Express.

Tuesday, October 19, 2010

Custom Authentication Scheme for Oracle Application Express and Oracle Access Manager

Our customers frequently ask about how to integrate Oracle Access Manager authentication with Oracle Application Express. There is currently a thread on the Oracle Technology Network discussion forum, asking for this type of solution. It has always been my intention to present this as an official whitepaper and recommended solution from Oracle. However, I have been struggling with some Oracle Access Manager configuration issues and I simply did not want to delay any further. The "official" whitepaper and detailed instructions will have to come later.

Back in March, 2010, I took careful note of a message that Scott Spadafore on our team had sent to someone in Oracle Support. It was a generic solution for authentication via an HTTP header variable. A couple months ago, this question came up again and Tyler Muth provided me a slightly modified version of what Scott had originally authored. With some more minor modifications on my part, I can share this custom authentication scheme, which can be used with Oracle Access Manager and really any environment which will securely set a header variable to an authenticated username.

The page sentry function is:

create or replace function header_variable_page_sentry ( p_apex_user in varchar2 default 'APEX_PUBLIC_USER' )
return boolean
l_cgi_var_name varchar2(100) := 'REMOTE_USER';
l_authenticated_username varchar2(256) := upper(owa_util.get_cgi_env(l_cgi_var_name));
l_current_sid number;
-- check to ensure that we are running as the correct database user
if user != upper(p_apex_user) then
return false;
end if;

if l_authenticated_username is null then
return false;
end if;

l_current_sid := apex_custom_auth.get_session_id_from_cookie;
if apex_custom_auth.is_session_valid then
apex_application.g_instance := l_current_sid;
if l_authenticated_username = apex_custom_auth.get_username then
return true;
else -- username mismatch. unset the session cookie and redirect back here to take other branch
apex_application.g_unrecoverable_error := true; -- tell apex engine to quit
return false;
end if;

else -- application session cookie not valid; we need a new apex session
apex_application.g_unrecoverable_error := true; -- tell apex engine to quit
if owa_util.get_cgi_env('REQUEST_METHOD') = 'GET' then
wwv_flow_custom_auth.remember_deep_link(p_url => 'f?'|| wwv_flow_utilities.url_decode2(owa_util.get_cgi_env('QUERY_STRING')));
end if;
-- -- register session in APEX sessions table,set cookie,redirect back
p_uname => l_authenticated_username,
p_session_id => nv('APP_SESSION'),
p_app_page => apex_application.g_flow_id||':'||nvl(apex_application.g_flow_step_id,0));
return false;
end if;
end header_variable_page_sentry;

The high-level steps to be performed are:
  1. Compile this function header_variable_page_sentry in the parsing schema of your application
  2. Create a new custom authentication scheme. In the Page Sentry Function attribute of the custom authentication scheme, enter: return header_variable_page_sentry;
  3. Add directive PlsqlCGIEnvironmentList inside the corresponding APEX Database Access Descriptor. By default, OAM Webgate uses the header variable REMOTE_USER.
  4. Secure the APEX application in Oracle Access Policy Manager by defining its corresponding Policy Domain.
  5. Back in your APEX application, make this new authentication scheme "current" for your application.

The custom authentication scheme should work in any version of Application Express. A gentleman from Oracle Consulting got this to work at a customer site using APEX 3.2 and OAM 10g. He very graciously put together a document detailing all of the steps he performed in Application Express and Oracle Access Manager to get this to work, which is invaluable to someone like me who is essentially OAM-ignorant. I've asked for his permission to share this document, and when/if I get his okay, I'll make it available from this blog.

Monday, September 27, 2010

Brief Discussion of Oracle Application Express

Last week at Oracle OpenWorld 2010 in San Francisco, Justin Kestelyn of the Oracle Technology Network interviewed Oracle Application Express Product Manager David Peake. If you're looking for a fairly short discussion about Oracle Application Express, this is a good place to start as I thought David's message was clear.

Sunday, September 19, 2010

Customizing the look of Interactive Reports

If you've ever wanted to have precise control over the look and feel of the Interactive Reports in your Oracle Application Express application, take a look at Shakeeb Rahman's recent blog post on his new blog Shakeeb Rahman is on the Application Express product development team and is the master of all things HTML and CSS.

Wednesday, September 15, 2010

APEX events at Oracle OpenWorld 2010 - for your iPhone

Marc Sewtz from the Oracle Application Express development team has been busy crafting a set of HTML templates and styleswhich are suitable for an iPhone or other small device. And to demonstrate this, Marc has crafted an APEX application running on (and Application Express 4.0) listing all of the Application Express-related sessions at Oracle OpenWorld 2010.

And you can read more about it on Marc's blog.

P.S. Don't bother using this application with Firefox nor Internet Explorer. It will work with any Webkit-based browser (Safari, Chrome). But it will look even better on your iPhone or Droid.

Tuesday, September 14, 2010

SINCE when?

This will not be new information for long-time Oracle Application Express users, but it's worth mentioning for those new to Oracle Application Express.

In APEX page items (those using Automatic DML) and columns in SQL Reports & Interactive Reports, you have the ability to format date and timestamp values using a variety of format masks. Every report column and form page item has a format attribute. For date and timestamp columns, you can specify any valid Oracle date or timestamp format mask, with a full listing provided here. Some examples include:


In APEX applications, you can also employ the SINCE format mask on date and timestamp columns. This is specific to APEX applications in the context of the format mask for report columns and Automatic DML page items. Instead of showing your end-users values like:


you can present a value of:

3 days ago

The time period is relative to the difference between the time it is rendered and the value itself. Values can include 'seconds ago', 'minutes ago', 'hours ago', 'days ago', 'weeks ago', 'months ago', and 'years ago'. The benefit of using SINCE is:

  • It's time zone agnostic. Regardless of the time zone of your end user, 3 minutes ago is always 3 minutes ago.
  • It's locale agnostic. Some locales interpret 09/11 as September 11, other locales interpret 09/11 as November 9. Everyone interprets '3 days ago' as 3 days ago.
  • It's translated into the 10 languages provided for Application Express 4.0. The output will be correctly translated based upon your user's language preference.

New in Application Express 4.0:

  • Support for future dates and timestamps. You can now have values like '3 weeks from now', '2 days from now'.
  • Support for the SINCE format mask against columns of type TIMESTAMP, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE.
  • And an API (should be documented, but I find that it's not right now) to compute this yourself:




Impress your boss and your end-users with the SINCE format mask!

Thursday, September 02, 2010

Going to Oracle OpenWorld 2010?

Are you going to Oracle OpenWorld 2010, which starts on September 19, 2010? If so, be sure to attend the "APEX Meetup" on Tuesday night. Dimitri has more information on his blog.

Automatic Time Zone support in Application Express 4.0

A feature of Application Express 4.0 which hasn't received a lot of press but is useful for those building applications that span time zones is the Automatic Time Zone application attribute.

The Oracle database has this wonderfully rich data type called TIMESTAMP WITH LOCAL TIME ZONE. The elegance of this data type is that the value stored in this column will be displayed in the user's current database session time zone. Having written a PL/SQL package to do time zone conversion, it is a non-trival exercise to develop this type of functionality let alone maintain it. Wouldn't it be great if we could put this burden of maintaining constantly evolving time zone rules and daylight saving time dates on the database? Well, you get this for free with TIMESTAMP WITH LOCAL TIME ZONE.

So if all we need to do is set the database session time zone, then:

  1. How do we elegantly derive this for each end user of our application?
  2. How do we ensure that every page view and page submission in Application Express has its database session time zone set correctly for a particular user?

There were numerous suggestions in the past, of storing a user's preferred time zone as a preference and then authoring a PL/SQL block in the VPD attribute of an application like:

execute immediate 'alter session set time_zone =''' || :MY_USER_TIMEZONE || '''';

Not exactly obvious. And this still doesn't answer question #1 of how do we elegantly derive this. This is where the new Automatic Time Zone attribute is useful.

In the Application Builder, if you edit the Application Properties and navigate to the Globalization subtab, you should see something like:

By default, Automatic Time Zone will be set to 'No'. When set to 'Yes', this will now change the behavior of your application:

  1. At the beginning of an Application Express session (which happens at the beginning each time a user runs your application ), the time zone offset will be calculated from their Web browser client.
  2. This time zone offset information will be sent to Application Express and recorded in the APEX session information for that user.
  3. Then, each and every page view for the duration of their APEX session, the Application Express engine will read this value and set the database session time zone to this value.

All you have to do is employ data types which are time zone aware (like TIMESTAMP WITH LOCAL TIME ZONE; DATE is not time zone aware) and check a box in your application definition. It couldn't be simpler!

To demonstrate this, I created a simple application using the following DDL:

create table tz_log(
id number primary key,
username varchar2(255) not null,
tz varchar2(512) not null,
created_ts timestamp not null );

create or replace trigger tz_log_trg1
before insert on tz_log
for each row
if is null then := to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
end if;
:new.created_ts := localtimestamp;

Then, I just built an application with a SQL report on this table and added an on New Instance PL/SQL Process of:

insert into tz_log (username, tz) values(:APP_USER, apex_util.get_session_time_zone );

You can run this sample application here. Just keep in mind - it will require you to authenticate with your credentials (the same credentials you use if you login to the OTN discussion forum) and it will record your visit in a log table, which others can view. Here's what it looks like - nothing fancy:

If you pay close attention, immediately after authentication, you'll see a URL like:

Obviously, your APEX session identifier and time zone value will be different than what I show above. But you'll see that there is a new parameter 'tz' to the 'f' procedure. And it is through this interface that you can create a URL to an APEX application and explicitly set the APEX session time zone to a different value. After you login, change the time zone value in the URL to something else (e.g., tz=0:00) and watch the values in the "Inserted into the Log Table (in your local time zone)" report column automatically adjust to that time zone. The underlying report definition didn't change - we're still simply selecting the TIMESTAMP WITH LOCAL TIME ZONE column out of the database, just now the database is automatically converting that value to display in the current session time zone.

You can also programmatically set and get the APEX session time zone setting using two new APIs in Application Express 4.0, namely APEX_UTIL.SET_SESSION_TIME_ZONE and APEX_UTIL.GET_SESSION_TIME_ZONE.

Sunday, August 22, 2010


Sorry, this is not a post about anything Oracle-related. But as our dear, departed friend Carl used to say - it's my blog.

I had the privilege of participating in this year's Pelotonia bike tour. What is Pelotonia, you may ask? "Pelotonia is a grassroots bike tour with one goal: to end cancer. Pelotonia raises money for innovative and life saving cancer research at The Ohio State University Comprehensive Cancer Center - James Cancer Hospital and Solove Research Institute."

This event had special significance for me, as our next-door neighbor, Jacob Carlino, was diagnosed with a rare form of cancer earlier this year and is undergoing treatment now. Jacob is 12 years old. A child like Jacob or any person, for that matter, shouldn't have to suffer through this horrible disease. Jacob's father Dave organized a team in support of his son Jacob, and I had the privilege of being on this team. Above is the team - Dan, Matt, Scott, me and Dave. On the front of our shirts is a picture of Jacob who we were honoring with our team's tour.

Pelotonia was a most-impressive and well-organized event. There were over 4,000 riders and thousands of volunteers in addition to a great deal of corporate sponsorship. The estimate is that this one event will raise over $8 million, with 100% of this money going directly to fund cancer research at the Ohio State University Cancer Care Center and Solove Research Institute. The honorary chair of this year's Pelotonia tour was Ohio State football legend Chris Spielman, who he himself lost his wife to cancer just 9 short months ago.

My goal for this blog post? To simply raise awareness of this disease, and to also let the world know that there is an ever-growing army of people right here in Ohio who is contributing in ways small and large to the eradication of this disease. Lastly, I would like to recognize and thank the sponsors of me for this ride - I am grateful for your generous support: Sue, Sergio & Priscila, Jason & Shelley, Eric, Bob & Marge, Matt & Gretchen, Tim & Susie, Anton & Chris, Kathy & Terry, Neil & Margaret, Tom, Nada & Matt, Frank, Harry, and Mike & Anita.

Wednesday, August 18, 2010

Application Express 4.0.1 released

Today, Oracle Application Express 4.0.1 was released. This corrects a number of issues discovered in the original Application Express 4.0 release. In the README of the Application Express 4.0.1 patch set, there is a listing of all of the bugs corrected in this patch set. You can view a copy of it here.

We have also repackaged the Oracle Application Express full distribution. If you have never installed or upgraded to Oracle Application Express 4.0, you can download the latest full distribution and install this and you'll have Application Express 4.0.1 out-of-the-box. Since this always creates confusion for customers (do you need the patch set or the full distribution?), use this guide:

  1. If you have Oracle Application Express release 4.0 installed, download the Oracle Application Express 4.0.1 patch set from My Oracle Support and apply it. Look for patch number 9976149. Even though the file is named, seemingly only for DB, it can actually be used against any supported database version running Application Express 4.0.

  2. If you have Oracle Application Express release 3.2.1 or earlier installed (including Oracle HTML DB release 1.5), download and install the entire Oracle Application Express 4.0.1 release from the Oracle Technology Network (OTN).

  3. If you do not have Oracle Application Express installed, download and install the entire Oracle Application Express 4.0.1 release from the Oracle Technology Network (OTN).

And how do you determine what version you're running? Well, if you're a DBA, you can connect to the database as SYS using SQL*Plus and issue:

select version from sys.dba_registry where comp_id = 'APEX';

If you can login to the Application Express development environment, you'll see the version number in the lower right of every page.

Oracle Application Express at had the patch set applied on Saturday, August 14, 2010.

Friday, August 13, 2010

Application Express and parsing of SQL

Recently, I worked with Oracle Support who was helping a customer who had a variety of questions about Oracle Application Express. One of the questions was:

"Please provide a detailed explanation of how apex interacts with database security. It appears that apex is manipulating queries prior to presenting them tot he database to be run either by wrapping them in a pl/sql block that prevents role based security from working or doing a pre-security check that doesn't check role level security prior to issuing a query, or some other even that bypasses role based security. "

Since there really isn't any great explanation of this anywhere, I'd like to provide a brief but lucid explanation about how parsing of SQL works in Oracle Application Express.

  • The Oracle database ships with a supplied PL/SQL package named DBMS_SQL, to perform the execution of dynamic SQL.

  • The Oracle database also ships with a supplied but undocumented PL/SQL package named SYS.DBMS_SYS_SQL. This package enables the execution of dynamic SQL but it also enables the parsing of a statement as a specific database user and with the privileges of this specific database user. This PL/SQL package is highly privileged, obviously. The EXECUTE privilege on this package is not granted to database user, by default. It should almost never be granted to any database user, ever. When asked to grant execution on this package to someone, a smart DBA will always say "no."

  • Until Database version, both DBMS_SQL and DBMS_SYS_SQL would not observe roles when parsing SQL. This was consistent with database object access in PL/SQL itself (database roles are not observed in PL/SQL, in case you didn't know).

  • In Database and later (and XE), SYS.DBMS_SYS_SQL was changed to support a flag which enabled the observation of database roles when parsing SQL.

  • In Application Express 2.1 and later, support was added to Oracle Application Express SQL Workshop on DB versions and later to observe database roles, to be consistent with SQL*Plus. However, the execution of SQL in an Application Express application still does not observe roles when parsing user SQL. This remains true for the recently released Application Express 4.0.

So this explains a few things:

  1. If you've ever wondered how Application Express can parse SQL as a specific database user and with the privileges of this database user but without ever connecting as that database user, this is the answer - DBMS_SYS_SQL.

  2. When a new database user/schema is provisioned through Application Express, the discrete system privileges are granted to this new database user and not through any database role.

  3. When you look the underlying database view V$SESSION, it will show that the database sessions associated with Application Express applications are connected as the minimally privileged database user APEX_PUBLIC_USER (or ANONYMOUS, if you're using the embedded PL/SQL gateway). But within that session, the underlying Application Express engine is being invoked and, after determining who the SQL can be parsed as for that specific page view, the SQL is being parsed as a different database user.

  4. This also explains why, in some database versions, it will appear that roles are enabled when you issue DML statements from SQL Commands in SQL Workshop, but in the development of your application and in the execution of your application, it will appear that database roles are not enabled and that direct object privileges are required.

Friday, July 23, 2010

Moving your XLIFF Files

The XML translation files generated from Oracle Application Express are produced in XML Localization Interchange File Format (XLIFF) format. XLIFF is a recognized standard for the localization of computer software. "It is intended to give any software provider a single interchange file format that can be understood by any localization provider."

One of the unique characteristics of Application Express is that it is one of the few development frameworks where the decision to localize and translate an application can be made after the application is actually completed. Because the definition of the application is maintained in meta data in the APEX repository, it's already known in advance which attributes of your application are translatable and which are not.

The process to produce a translated application is pretty straightforward. It's as simple as:

  1. Seed the translation repository from your existing application
  2. Export the XLIFF file
  3. Translate the XLIFF file
  4. Upload the XLIFF file
  5. Apply the XLIFF file
  6. Publish your translated application

The first few translation unit lines of a sample XLIFF file generated from Application Express look like:










Each translatable string is included as a 'trans-unit' in the XLIFF file. The last two elements of each translation unit ID are the meta data ID and the application ID. For example, in translation unit with id S-4-885632445599895776-25721, the meta data ID is 885632445599895776 and the application ID is 25721. (S-4 is an internal code signifying that this is a meta data string and corresponds to the text of a tab).

As I've discussed in a recent blog post about saved Interactive Reports, I explained how the internal meta data IDs "shift" or are recalculated when importing an application to a new ID. And this has presented problems for those customers who make use of the translation facilities of Application Express. Because the meta data IDs are a part of the XLIFF translation unit IDs, when those IDs change, the existing XLIFF files for the original application cannot be used against a new version of the application imported elsewhere as a new application ID. What a dead end!

I have authored an APEX application which helps customers overcome this problem. You can run the hosted version of the XLIFF Transformation application which is running in my workspace on, or you can download a copy of it and run it on your own APEX 4.0 or later instance. You need to provide 3 things when running this application:

  1. The original XLIFF file
  2. The application ID of the new application
  3. The offset value between the two applications

To compute #3, I'll refer you to this same blog post where I give a couple examples how to determine the offset value.

The logic is really quite simple. After importing the application and installing the supporting objects, only 3 objects will be created - a table named XLIFF_FILES, a trigger on this table, and a small PL/SQL package named XLIFF_TRANSFORM. The PL/SQL package parses the XML file and uses some XDB APIs to replace certain elements of the XML file. By exploiting the native functionality of the database, this was really quite easy to write. For anyone who says the Oracle database is only good for "persisting data", I say smoke this!

This isn't my ideal solution. In a future release of Application Express, I'd like to make it as simple as choosing to include your translations in your application export file, and they move around with you. As the metadata gets transformed on a new import, so do the translations. But until then, this solution can be used.

Tuesday, July 20, 2010

Where Did My Saved Interactive Reports Go?

A problem I've seen reported numerous times from customers is that users' saved (or customized) interactive reports are missing after they import a new version of their application. This is a problem we've known about for a while with no adequate remedy. However, given the introduction of the APEX_APPLICATION_INSTALL API in Application Express 4.0, I can offer a solution. Granted, it's not an ideal answer but it's certainly a feasible and supported solution. Firstly, some explanations are in order.

When an APEX application is imported into a workspace, the very first thing that's done is the existing version of the application is completely deleted. All of the meta data associated with the application is deleted - the definition of the pages, the reports on the pages, the templates, the buttons, the branches, the shared components, everything - it's all deleted. Once this is complete, then the application meta data of the APEX application being imported is then inserted. This whole process is atomic - so if an error occurs, the transaction is rolled back and the net effect is no change.

In the case of saved Interactive Reports, it's a little bit different. Imagine you have a production instance running application 645 - you have numerous users who have saved many Customized Interactive Reports. Upon import of a new version of the application, all meta data associated with application 645 is first deleted except the Customized Interactive Reports. In essence, these are left "dangling" until the new application 645 is installed. Once the application import is complete, then the meta data of the Customized Interactive Reports will reference real interactive report definitions again.

But there's a catch. If the application ID changes upon import, then this results in totally new meta data IDs being generated. (This is done in an attempt to prevent collisions of meta data, so you can freely export your application and give to anyone in the world to use on their own APEX instance). A meta data offset number is randomly generated and added to all of the existing IDs. This is done uniformly across all of the application meta data (this is important, and you'll see why shortly). Since the IDs of all of the application meta data have changed, all of your users' customized reports in the previous version of the application are forever left orphaned until they're cleaned up by an internal APEX batch process. Yikes!

Let's look at an example. On, I created a simple application with an Interactive Report on the EMP table. I defined this application as application 70000. I then exported this application and imported it back as application 70001.

Using SQL Commands and the APEX Data Dictionary views, I ran the following queries:

select tab_label, tab_id
where application_id = 70000

tab_label: Emp
tab_id: 1573281607527253166

select tab_label, tab_id
where application_id = 70001

tab_label: Emp
tab_id: 3146580610985521585

And the difference between the two IDs is 3146580610985521585 - 1573281607527253166 = 1573299003458268419

Let's do this again, but this time, for the APEX data dictionary view for page templates:

select template_id from APEX_APPLICATION_TEMP_PAGE where template_name = 'Login' and application_id = 70000

template_id: 1573270610302252883

select template_id from APEX_APPLICATION_TEMP_PAGE where template_name = 'Login' and application_id = 70001

template_id: 3146569613760521302

If we once again compute the differences between these two IDs, we get: 3146569613760521302 - 1573270610302252883 = 1573299003458268419

This happens to be exactly the difference between the IDs of all of the application meta data, with the exception of the application and page IDs. All of the meta data is consistently "pushed" or offset to a new value.

How is this relevant to missing saved Interactive Reports? Simple. Since we're able to compute the offset which was used between the two applications, if we had a way to ensure that the same offset is used every time upon application import, then there would be no issue with the old saved Interactive Report IDs matching with the newly imported meta data. They would be married again. And how is this done? In Application Express 4.0, there is a new API named APEX_APPLICATION_INSTALL which enables you to control this offset value.

To ensure that I didn't lose the saved Interactive Reports on subsequent imports of application 70000 to application 70001, I included the computed offset before importing this application via SQL*Plus:

apex_application_install.set_application_id( p_application_id => 70001 );
apex_application_install.set_offset( p_offset => 1573299003458268419 );
-- set the alias so it doesn't collide with the alias from app 70000
'F' || apex_application.get_application_id );


That's all there is to it. Note that I didn't have to call apex_application_install.set_workspace_id above, because application 70000 and 70001 are in the same workspace where I performed this test.

As I stated earlier, this isn't the most elegant solution on the planet and most people don't want or need to know about meta data IDs or offsets or any of this complexity. But for those experienced users who are stuck with this problem of losing saved interactive reports when migrating from one application ID to another or across workspaces or instances, this is a supported and feasible solution.



Oracle Application Express provides two ways to import an application into an Application Express instance:

  1. Upload and installation of an application export file via the Web interface of Application Express.
  2. Execution of the application export file as a SQL script, typically in the command-line utility SQL*Plus

Using the file upload capability of the Web interface of Application Express, developers can import an application with a different application ID, different workspace ID and different parsing schema. But when importing an application via a command-line tool like SQL*Plus, none of these attributes (application ID, workspace ID, parsing schema) can be changed without directly modifying the application export file.

As more and more Application Express customers create applications which are meant to be deployed via command-line utilities or via a non-Web-based installer, they are faced with this challenge of how to import their application into an arbitrary workspace on any APEX instance.

Another common scenario is in training classes, to install an application into 50 different workspaces, all using the same application export file. Today, customers work around this by adding their own global variables to an application export file (never recommended and certainly not supported) and then varying the values of these global variables at installation time. However, this manual modification of the application export file (usually done with a post-export sed or awk script) shouldn't be necessary - and again, not supported.

In Oracle Application Express 4.0, there is a new API available named APEX_APPLICATION_INSTALL. This PL/SQL API provides a number of methods to set application attributes during the Application Express application installation process. All export files in Application Express 4.0 contain references to the values set by the APEX_APPLICATION_INSTALL API. However, the methods in this API will only be used to override the default application installation behavior.



Used to set and get the workspace ID for the application to be imported. This number can be determined by querying the view APEX_WORKSPACES.

procedure set_workspace_id( p_workspace_id in number );

function get_workspace_id return number;

Application ID

Used to set and get the application ID for the application to be imported. The application ID should either not exist in the instance, or if it does exist in the instance, it must be in the workspace where the application will be imported into. This number must be a positive integer and must not be from the reserved range of Application Express application IDs.

procedure set_application_id( p_application_id in number );

function get_application_id return number;

Generates an available application ID on the instance and sets the application ID in APEX_APPLICATION_INSTALL.

procedure generate_application_id;


Used to set the offset value during application import. This value is used to ensure that the metadata for the Application Express application definition does not collide with other metadata on the instance. For a new application installation, it's almost always sufficient to call generate_offset to have Application Express generate this offset value for you. This number must be a positive integer.

procedure set_offset( p_offset in number );

function get_offset return number;

procedure generate_offset;


Used to set the parsing schema ("owner") of the Application Express application. The database user of this schema must already exist, and this schema name must already be mapped to the workspace which will be used to import the application.

procedure set_schema( p_schema in varchar2 );

function get_schema return varchar2;


Sets the application name of the application to be imported.

procedure set_application_name( p_application_name in varchar2 );

function get_application_name return varchar2;


Sets the application alias of the application to be imported. This will only be used if the application to be imported has an alias specified. An application alias must be unique within a workspace, and it's recommended to be unique within an instance.

procedure set_application_alias( p_application_alias in varchar2 );

function get_application_alias return varchar2;

Image Prefix

Sets the image prefix of the application to be imported. The default can usually be used, as most Application Express instances use the default image prefix of /i/.

procedure set_image_prefix( p_image_prefix in varchar2 );

function get_image_prefix return varchar2;


Sets the proxy server attributes of the application to be imported.

procedure set_proxy( p_proxy in varchar2 );

function get_proxy return varchar2;


Clears all values currently maintained in the APEX_APPLICATION_INSTALL package.

procedure clear_all;


Using the workspace FRED_DEV on the development instance, you generate an application export of application 645 and save it as file f645.sql. All examples below assume you are connected to SQL*Plus.

To import this application back into the FRED_DEV workspace on the same development instance using the same application ID:


To import this application back into the FRED_DEV workspace on the same development instance, but using application ID 702:

apex_application_install.set_application_id( 702);
apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id );


To import this application back into the FRED_DEV workspace on the same development instance, but using an available application ID generated by Application Express:

apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id );


To import this application into the FRED_PROD workspace on the production instance, using schema FREDDY, and the workspace ID of FRED_DEV and FRED_PROD are different:

l_workspace_id number;
select workspace_id into l_workspace_id
from apex_workspaces
where workspace = 'FRED_PROD';
apex_application_install.set_workspace_id( l_workspace_id );
apex_application_install.set_schema( 'FREDDY' );
apex_application_install.set_application_alias( 'FREDPROD_APP' );


To import this application into the Training instance for 3 different workspaces (each workspace with their own schema):

l_workspace_id number;
select workspace_id into l_workspace_id
from apex_workspaces
where workspace = 'TRAINING1';
apex_application_install.set_workspace_id( l_workspace_id );
apex_application_install.set_schema( 'STUDENT1' );
apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id );


l_workspace_id number;
select workspace_id into l_workspace_id
from apex_workspaces
where workspace = 'TRAINING2';
apex_application_install.set_workspace_id( l_workspace_id );
apex_application_install.set_schema( 'STUDENT2' );
apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id );


l_workspace_id number;
select workspace_id into l_workspace_id
from apex_workspaces
where workspace = 'TRAINING3';
apex_application_install.set_workspace_id( l_workspace_id );
apex_application_install.set_schema( 'STUDENT3' );
apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id );


Now a final word of caution - with great power comes great responsibility. You should almost never set the offset value yourself unless you absolutely know what you're doing. One of the primary benefits of letting Application Express generate the meta data offset value for you is you avoid the possibility of any "collisions" with the meta data of any other application on any other APEX instance on the planet. If you have no known reason to manually set the offset value, then simply let Application Express set it for you.

In summary, the APEX_APPLICATION_INSTALL API in Application Express 4.0 now enables you to overcome a limitation in all previous versions of Application Express - namely, to take an arbitrary application export file and import it into any workspace on any arbitrary Application Express instance using SQL*Plus or any other command-line tool.

Friday, July 02, 2010

Welcome Mike Hichwa to the Blogosphere!

My manager since 1999, Mike Hichwa, has finally decided to join the blogosphere. Mike is the visionary and driving force behind Oracle Application Express and has been since he authored the very first line of APEX (then called "Flows") in 1999. Mike is the Vice President of Database Tools and is responsible for Application Express, SQL Developer, .NET tools, Java in the database, and others.

Wednesday, June 30, 2010

Language Preference of the Application Development Environment in APEX 4.0

A customer filed a Service Request with Oracle Support, asking the following:

In Apex 4, Irrespective of the Client OS / Locale / Browser Language settings, the Language picked up the interface shows as “ Language: en “ . Where as from the same browser/client if I login into Apex.3.2.1, it shows the correct language as "DE" , "TH", "FR", etc.,

Has anybody seen this issue? Am I missing something?

This is expected behavior. In Application Express 4.0, the development environment uses the new "Session" language derivation, bringing it in line with modern Web applications which do not use the browser language as the primary language preference. This new language derivation type is documented briefly here and additionally in the item-level help.

If you go to the Login page of, you’ll see the language bar at the bottom. This language list is determined by the translated versions of Application Express which are installed in the instance. You can change your language preference from this language bar or within Application Express too. The language preference is "remembered" via a persistent cookie.

Tuesday, June 22, 2010

Oracle Application Express 4.0 is released

Oracle Application Express 4.0 is now available for download from the Oracle Technology Network here. What began in 2008 has culminated in the most ambitious release of Oracle Application Express since the very first version of Oracle HTML DB in 2004. Major enhancements include:

  • Websheets
  • Dynamic Actions
  • Plug-Ins
  • Team Development
  • Improved Charts, including Maps and Gantt Charts
  • Support for RESTful Web Services
  • Enhanced Interactive Reports
  • Dramatically improved themes/templates
  • Tabular form validations
  • Dynamic calendars
  • and many more...

You can read about the new features of Application Express 4.0 here.

If you wish to kick the tires of Oracle Application Express 4.0, feel free to sign up for a free workspace at We're currently averaging around 600 new workspaces per week on, so the interest in the APEX Community is growing every day.

Our thanks go out to the thousands of customers who participated in the three different Early Adopter releases since December 2009. Your feedback, bug reports and suggestions have all made this a far better release in terms of quality and functionality.

Friday, June 18, 2010 upgraded to APEX 4.0, version

Tonight, I patched to version This is our proposed final release candidate of the software.

All translated versions have been installed too, which includes German, Spanish, French, Italian, Japanese, Korean, Brazilian Portuguese, Simplified Chinese and Traditional Chinese. Unlike previous releases of Application Express, the language for the Application Builder is no longer determined by your browser language setting. You can simply choose your language from the Login page or from within the home page in Application Express (after you login).

It has proven extraordinarily useful to upgrade and gather the feedback prior to release. There were a number of bugs which were uncovered and fixed that would have otherwise been missed. Thanks to all who reported any issues.

Thursday, June 17, 2010

Oracle Application Express and the Corporate IT Architect - Part I

A few weeks ago, I attended an event in Columbus, Ohio called "IT Martini Hour 10: Agile of All Trades", sponsored by IT Martini. It's a very well organized event, gathering a few hundred people from the local IT community. I also was interested in going because a friend of mine from Pacejet was part of a discussion panel, hosted by Amazon Web Services. We've had such smashing success with Amazon Web Services in the hosting of, I just simply wanted to meet them and thank them.

During this event, I ran into a gentleman I worked with 20 years ago, where he and I were both in the same product development organization. This gentleman, "Mr. G", was a smart guy 20 years ago and is a smart guy today. Today, "Mr. G" works for a large financial institution where he is an architect for corporate IT. After glancing at my name tag which said "Oracle", he asked what I was doing and I gave him the 20-second story of Oracle Application Express. I even went so far as to say that his own company is running over 100 internal applications, all built with Oracle Application Express. I was expecting a positive reaction. That's not what I got.

In a rather abrupt and candid response, "Mr. G" said that he learned of Application Express just a couple weeks ago. He said it was stupid. It's RAD and RAD is bad. It's for "quick and dirty applications that don't scale". When I told him what organization was a primary user of APEX in his company, he referred to them as "the business users", and of course they'd use APEX. He wasn't surprised. They tend to use one of everything, whether or not it's the IT standard. He went on - a problem with APEX, like other frameworks, it's that it's a black box, and when there's a problem with the black box, you're stuck. Of course, I asked him if he doesn't use any framework, since presumably all frameworks are bad - but of course they do.

Another problem he had - he said I'd be surprised if I looked at what crappy systems the "business users" run these systems on. Then, when they fail, it becomes an IT problem. I had to point out that this wasn't a criticism of Application Express but a flaw within their organization.

So I asked him what alternatives are provided by "corporate IT". He said they have a bundle of software, some of it open-source, that is the official development platform distributed amongst the various businesses, which corporate IT can support. When I asked him if this can be used by the "business users", of course he said no. To which I concluded that he'd rather have the business users not solve their problems.

This went on for a few more minutes until the beer and testosterone kicked in. I finally blurted out how he was arriving at conclusions based upon no knowledge or experience. Surprisingly, "Mr. G." agreed. I offered to come to his place of business and give him a personal demonstration, being as honest and forthcoming as I can be about the positioning of Application Express and its strengths and weaknesses.

When the dust of Oracle Application Express 4.0 settles, I'll be visiting him.

Monday, June 07, 2010

Oracle Application Express 3.2: The Essentials and More

Last week, Packt Publishing shipped the book Oracle Application Express 3.2: The Essentials and More. This book is authored by my friend Arie Geller and Matthew Lyon. Many people have been helped by Arie on the Application Express discussion forum on OTN for years, and I can easily say that Arie is one of the foremost experts in the world on creating multi-lingual and globalized applications in Oracle Application Express. I've even learned a few things from Arie about properly developing applications which support RTL (right-to-left) languages like Hebrew and Arabic.

Friday, June 04, 2010 upgraded to Oracle Application Express 4.0

Oracle Application Express at has been upgraded to a pre-production version of Oracle Application Express 4.0. There's nothing like upgrading an instance of 8,000 workspaces and 31,000 applications to ensure that the upgrade works perfectly and what used to work in Application Express 3.2 continues to run flawlessly

As most users in the APEX Community have come to realize, this is one of the final milestones in our development cycle before release. So we're getting close. You can read about what's new in Application Express 4.0 here.

The participation of the thousands of people in the Early Adopter program since December has been overwhelming. We are grateful for all of the work and feedback and suggestions and bugs reported by everyone. The APEX Community is alive and well...and growing!

Monday, May 10, 2010

Application Express 4.0 Early Adopter Phase 3 is now available

The third (and hopefully final phase) of Oracle Application Express 4.0 Early Adopter is now available. A new instance with the latest build of Application Express 4.0 is now available on

If you had used the second Application Express 4.0 Early Adopter instance, your workspace and schema have not been migrated to this instance. You will need to sign up for a workspace but you shouldn't have to take the survey again. The original Application Express 4.0 Early Adopter 2 instance is available at, and it will be available for the next couple of weeks.

Thanks to everyone who has participated and provided feedback, as the feedback and suggestions have been invaluable as always.

Tuesday, April 06, 2010

Oracle Learning Library

Marcie Young, from Curriculum Development, has just released the Oracle Learning Library. This is an application which she developed on and is a directory listing of free online training from Oracle - Oracle By Examples, Tutorials and Demonstrations. This application lets you search for free training covering numerous product and functional categories. Product categories include Database, Beehive, Enterprise Linux, Enterprise Manager, Fusion Middleware, JDeveloper and Oracle VM.

Tuesday, March 30, 2010

Memorials for Scott Spadafore

There will be two memorials for our dear friend, Scott Spadafore:

Saturday, April 10, 2010 at 2:00P PDT
Clovis Masonic Lodge
375 5th Street (at DeWitt)
Clovis, CA 93612



Saturday, May 1, 2010 at 2:00P PDT
Santa Cruz Masonic Lodge
828 N. Branciforte Avenue
Santa Cruz, CA 95062


In lieu of flowers, donations may be made to a college fund for Scott’s daughters, Blaire and Keely. Checks should be payable to:


and donations can be sent to:

1187 N. Willow Avenue, #103
Clovis, CA 93612

Monday, March 22, 2010

Scott Spadafore

I am quite saddened to say that our friend, team mate and long time contributor to Oracle Application Express, Scott Spadafore, died last night of a heart attack.

Scott has made extraordinary contributions to Oracle Application Express since he joined our team in 2001. Scott also worked tirelessly for many years on the Application Express discussion forum on OTN, where he helped (literally) thousands of customers to become successful with Application Express. His patience and precision were to be admired.

I ask that you pray for Scott's wife, Gail, and their two daughters, at this difficult time.

Friday, February 26, 2010

Application Express 4.0 Early Adopter Phase II is available

The second phase of the Application Express 4.0 Early Adopter is now available. A new instance with the latest build of Application Express 4.0 is now available on

  1. This database was created with database character set AL32UTF8 (we're still a little confused by the Amazon Machine Images from Oracle aren't already AL32UTF8.
  2. The Websheets feature is now available. The security of Websheet applications still needs to be refined, but the functionality and ease of creation of these types of applications is amazing.
  3. The new tree region makes its debut.
  4. Collections now support NUMBER and DATE
  5. apex_collection.create_collection_from_query_b and apex_collection.create_collection_from_queryb2 support bind variables and row limits
  6. Debugging and logging has been completely rewritten - no longer is debug output emitted within the page, but it's collected and can be viewed later (and also queried via views).
  7. You won't be able to see this, but the tablespaces / data files of provisioned workspaces will now autoextend
  8. There have been many improvements in the Plug-In infrastructure, which Patrick will surely expand upon.
  9. A Migration Assistant has been created, which lets you upgrade all of the elements of your pre APEX 4.0 application in one place.
  10. Native validations and declarative validations have also been rewritten and improved (again, Patrick will expand upon this).
  11. All of the item types have been consolidated (so you no longer see 1,000 different Date Picker types in your item type select list, as well as other item types)
  12. A large number of bugs and usability issues have been corrected, thanks in a large part to the tremendous amount of feedback we received.
This isn't an exhaustive list. The updated list of new features will be available on

If you had used the first Application Express 4.0 Early Adopter instance, your workspace and schema have not been migrated to this instance. You will need to sign up for a workspace but you shouldn't have to take the survey again. The original Application Express 4.0 Early Adopter instance is available at, and it will be available for the next couple of weeks.

Thanks to everyone who has participated and provided feedback, as the feedback and suggestions have been invaluable.

Wednesday, February 24, 2010

FamZoo goes live!

Nine years ago, when Oracle started down the path of turning Application Express (nee HTML DB) into a real, supported product that was shipped with the Oracle database, we had the good fortune of being mentored and led by Bill Dwight. Bill was a hands-on Vice President in the Oracle Tools division and he led the development of JDeveloper, Developer, Forms, Designer, and iLearning. Bill had a lot of experience and success at Oracle, but also he had the right kind of savvy to guide us from a "project" to a product. He imparted a lot of sound advice that still helps us today.

Fast forward to today, and Bill Dwight is the founder and CEO of FamZoo, Inc. In a nutshell, FamZoo is "a family-friendly web site that helps parents teach their children the practical skills they’ll need to thrive in the real world." FamZoo the company, and FamZoo the Web site have been under development and in private beta for a few years, but as of January 21, 2010, FamZoo is now open for business to the public. As you navigate around the FamZoo site, you'll see the tell-tale signature of Application Express in the URL. There is an extensive FamZoo tour also available.

FamZoo has been an interesting case-study for Oracle Application Express, in that:
  1. They made the conscious decision to write the customer-facing portions of FamZoo in lovingly hand-crafted HTML / JavaScript / AJAX. According to Bill, this was important because "our consumer facing UI is very, very custom and particular - a key part of our brand."

  2. The internal administration and dashboard pages of FamZoo were written using the traditional wizards and declarative APEX infrastructure.

  3. Even with the very custom customer-facing UI, they still capitalize on other benefits of the Application Express framework, including security, session state, page templates, etc.

  4. With the exception of one local APEX instance for upgrade testing, the development and deployment of FamZoo has been done on commercial, hosted instances of Application Express from two different hosting providers. Not only was this extraordinarily cost effective, but it let the FamZoo team focus on building their business and their brand, and not have to worry about database and Web site administration, backups, power, etc.

The user interface of FamZoo is really superb and the overall flow and navigation of the site is elegant. To develop this kind of site takes a lot of time and effort and careful planning, regardless of the development framework that is chosen. I'm just happy they chose to go the Application Express route.

Monday, February 08, 2010

Making fast (again)

For the latter part of 2009, the performance on was lacking at times, to put it kindly. As evidenced by this long-running thread on the APEX discussion forum on OTN, performance issues started cropping up in August and things only got worse as the year went on. Ben Burrell, a very frequent contributor on the APEX discussion forum said it best with:

"The dedication and patience shown by the contributors to this forum is nothing short of incredible. Oracle should be magnificently proud of the APEX community - and reward it with a decent service."

I wholeheartedly agree! The least Oracle can do for the army of APEX experts who graciously give of their time to help others with Application Express is provide them a usable service. Additionally, for those users of Application Express who sign up for a test account on, a first impression is everything. Even if the cause of the issue is not "an APEX problem", per se, it will always have the perception that APEX is slow and cannot scale.

This has been a learning experience for me, and I'm simply here to report a number of the issues uncovered and the steps we took to remedy them.

1) KeepAlive - In November 2009, I was monitoring the database listener log files. There were a very large number of incoming connection requests to the database listener - in some instances, on the order of 30 connection requests per second. One of the most expensive actions you can take is to establish a new database session, hence, why session pooling is so commonly used. As it turns out, an administrator had turned off the KeepAlive setting in Oracle HTTP Server. Every new APEX page view request had to establish a new database session. For a site that gets millions of page views a day, this is not good. I had them turn KeepAlive back to On and with a KeepAliveTimeout of 6 seconds. As busy as is today, we now only average about one new database session request every 10 to 15 seconds.

2) robots.txt - The robots.txt file is supposed to be used by Web crawlers and search engines, although not all of them seem to observe it. This file enables a Web site administrator to control when Web crawlers are allowed to crawl a site and how frequently. The "window" of time that we permitted Web crawlers was too great - that's the last thing we want is to have some new user of Application Express competing for the same resources with some Web crawler which is hurling requests at our site. So I reduced the open window for Web crawlers:

3) Number of Database Access Descriptors - Since the days of the original '', the Database Access Descriptor used for the site was named 'otn'. Earlier this year, I wanted to have support the documented and standard Database Access Descriptor named 'apex', so we added that one to our configuration. Then, with the migration of AskTom to the instance, we created a third Database Access Descriptor named 'asktom' (otherwise, we would have broken any bookmarks to Tom's questions and answers).

The Web server software used by Oracle Global IT on this instance is Oracle Application Server 10.1.2. Unfortunately, Oracle HTTP Server and mod_plsql do not support true connection pooling in this version (see the documentation for the explanation). Each HTTP Server process would establish and maintain a database connection per DAD - that's three database connections times the number of HTTP Server processes. There are two Web servers that front-end - so 2 Web servers * 3 connections per Oracle HTTP Server process * 200 - 250 HTTP Server processes - and we easily had more than 1,000 database sessions during peak times. Even if only a handful of these sessions were active at any given time, the memory consumed by all of these sessions was too large. So large, that the system log was recording messages like "Out of Memory: Killed process 31088 (oracle)" every few minutes. Not good.

With the help of Kris Rice, we were able to remove the 'asktom' Database Access Descriptor and replace it with the following rewrite directives in httpd.conf:

RewriteEngine On
RewriteCond %{THE_REQUEST} ^.*/pls/asktom.* [NC]
RewriteRule ^.*/pls/asktom(.*)$ /pls/apex$1 [NC,QSA,PT]

With this one change, the database server wasn't swapping memory and the OS was not randomly killing processes because it was out of memory. The next day, Tom even commented how snappy it was.

4) File System Caching - The system administrators reviewed the network performance and I/O systems. They changed the "noac" and "actimeo" parameters so that the system would cache file system parameters, thus reducing the I/O on the system. Remember that this was a database with over 15,000 tablespaces and datafiles - that's a lot of potential I/O savings. Don't ask me for any more detail on this one, though - it's beyond my domain of expertise.

5) Purge of Stale Workspaces - There is no built-in workspace purging process in Application Express. The way the process has worked over the past 3 or 4 year is I personally would run a hand-crafted collection of custom PL/SQL programs which identified the workspaces that have not been touched in the last 45 days, notify the administrators of these workspaces via e-mail that their workspace was going to be purged, let them retain the workspace if they wished, and then after a two-week period - go through and purge the ones which hadn't been retained.

The problem with this solution is that would go through these peaks and valleys - dropping to 7,000 workspaces and then over time, creep up to 17,000 workspaces. I would manually run the purge process a year later, bring it back down to 8,000 workspaces, and then the process starts all over again. The system would be fastest after the purge and then slowly degrade over time.

I rewrote the purge process from scratch, but this time, I designed it to run perpetually (I have to give credit to my manager, Mike Hichwa, for this idea). The parameters are roughly the same - every night, it wakes up, computes who hasn't used their workspace in the past N days and isn't already on "the list", sends the administrators an e-mail, gives them the chance to retain their workspace, sends a reminder 3 days in advance of the purge, and and then after a total of 10 days, purges the workspace. Purging the workspace involves dropping the tablespace & data file, dropping the database user corresponding to the workspace schema, and deleting the workspace and all associated meta data. The great thing is this runs every day - I don't have to kick it off and monitor it, I just get daily e-mails about the status and statistics.

Since the first workspace was purged by this process on January 18, 2010, this perpetual purge process has been used to remove 5,998 workspaces and 5,860 tablespaces, reclaiming 64,531,415,040 bytes from the file system. And now is on auto-pilot.

My manager, Mike Hichwa, also suggested I simply fold this feature into Application Express 4.0 - so Application Express can become a self-maintaining system. I'm not convinced this feature would be used by that many customers outside of I tend to believe DBA's and our customers would rarely want to automatically drop data.

6) Memory - This past Saturday, February 6, 2010, the memory on was upgraded from 16 GB to 32 GB. Since then, the machine is really very, very fast. I've monitored the system load and it is consistently running at about 50% of maximum capacity, even during peak times.

Going forward, I am going to request that we upgrade the database to version Additionally, I am going to request an upgrade to Oracle HTTP Server which supports true connection pooling and which should dramatically reduce the number of overall database sessions to the database server, itself. If the upgrade to Oracle HTTP Server can't be done in a reasonable time frame, then I'm going to try to setup server-side pooling via Database Resident Connection Pooling. Even though I always advise against multiplexing multiplexed connections, I can't really describe the connection pooling on our version of Oracle HTTP Server today as "multiplexed."

If I were in charge of this site, I would have done these upgrades yesterday. But probably like the company you're at, our IT organization that actually runs this site has their software levels and it takes time to uptake new versions and roll it out. I'm not complaining, as I understand and appreciate this when you have to manage hundreds of databases and Web applications, as they do.

Contrary to some assertions, the performance of was of great concern to us and to me personally. However, you cannot fix a problem until you have diagnosed the actual cause of the issue. And unfortunately, that takes time. Additionally, some of these steps took a great deal of time to actually implement and test before rolling out on As much as I would have liked to flip the "fast=true" bit, that wasn't reality. This has been a learning process and one which will ultimately benefit the users of going forward as well as the product, Oracle Application Express. I am grateful to those customers and members of the APEX community for their patience during this time. To paraphrase Frank Costanza, "We're back, baby!"