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:

Logout
Logout


Print
Print


Logout
Logout


Print
Print


Home
Home


Customers
Customers


Products
Products


Orders
Orders


Charts
Charts

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 apex.oracle.com, 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.

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 apex.oracle.com, 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
from APEX_APPLICATION_TABS
where application_id = 70000

tab_label: Emp
tab_id: 1573281607527253166


select tab_label, tab_id
from APEX_APPLICATION_TABS
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:

begin    
    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
    --
    apex_application_install.set_application_alias( 
        'F' || apex_application.get_application_id );  
end;
/


@f70000.sql

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.

APEX_APPLICATION_INSTALL

Overview

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.

APEX_APPLICATION_INSTALL Summary

Workspace

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;

Offset

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;

Schema

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;

Name

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;

Alias

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;

Proxy

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

procedure set_proxy( p_proxy in varchar2 );

function get_proxy return varchar2;

Clear

Clears all values currently maintained in the APEX_APPLICATION_INSTALL package.

procedure clear_all;

Examples

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:

@f645.sql

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

begin
  apex_application_install.set_application_id( 702);
  apex_application_install.generate_offset;
  apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id );
end;
/

@645.sql

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:

begin
  apex_application_install.generate_application_id;
  apex_application_install.generate_offset;
  apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id );
end;
/

@f645.sql

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:

declare
    l_workspace_id number;
begin
    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.generate_offset;
    apex_application_install.set_schema( 'FREDDY' );
    apex_application_install.set_application_alias( 'FREDPROD_APP' );
end;
/

@f645.sql

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

declare
    l_workspace_id number;
begin
    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.generate_application_id;
    apex_application_install.generate_offset;
    apex_application_install.set_schema( 'STUDENT1' );
    apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id );
end;
/

@f645.sql

declare
    l_workspace_id number;
begin
    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.generate_application_id;
    apex_application_install.generate_offset;
    apex_application_install.set_schema( 'STUDENT2' );
    apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id );
end;
/

@f645.sql

declare
    l_workspace_id number;
begin
    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.generate_application_id;
    apex_application_install.generate_offset;
    apex_application_install.set_schema( 'STUDENT3' );
    apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id );
end;
/

@f645.sql

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.

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.

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 apex.oracle.com, 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.

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 http://apex.oracle.com. We're currently averaging around 600 new workspaces per week on apex.oracle.com, 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.

apex.oracle.com upgraded to APEX 4.0, version 4.0.0.00.46

Tonight, I patched apex.oracle.com to version 4.0.0.00.46. 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 apex.oracle.com 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.

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 http://tryapexnow.com, 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.

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.

apex.oracle.com upgraded to Oracle Application Express 4.0

Oracle Application Express at http://apex.oracle.com 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!

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 http://tryapexnow.com.

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 http://184.73.244.154/apex, 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.

Oracle Learning Library

Marcie Young, from Curriculum Development, has just released the Oracle Learning Library. This is an application which she developed on apex.oracle.com 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.

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

+1.559.299.4992

and

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

+1.831.423.1530

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

SCOTT SPADAFORE MEMORIAL FUND

and donations can be sent to:

SSMF
PMB#3
1187 N. Willow Avenue, #103
Clovis, CA 93612

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.

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 http://tryapexnow.com.

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 tryapexnow.com.

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 http://184.73.244.154/apex, 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.

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.

Making apex.oracle.com fast (again)

For the latter part of 2009, the performance on apex.oracle.com 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 apex.oracle.com, 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 apex.oracle.com 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: http://apex.oracle.com/robots.txt.




3) Number of Database Access Descriptors - Since the days of the original 'marvel.oracle.com', the Database Access Descriptor used for the site was named 'otn'. Earlier this year, I wanted to have apex.oracle.com 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 apex.oracle.com 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 apex.oracle.com - 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 apex.oracle.com 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 apex.oracle.com 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 apex.oracle.com. 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 apex.oracle.com 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 11.2.0.1. 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 apex.oracle.com 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 apex.oracle.com. 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 apex.oracle.com 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!"

The Perils of Modifying the Application Express Metadata

Last week, Oracle Support contacted me about an escalated Service Request they received from a customer. This customer, who was running Application Express 3.2, was unable to take an application export file from their production environment and import it into their development environment. During import, it would fail with:


ORA-20001: GET_BLOCK Error. ORA-20001: Execution of the statement was unsuccessful. ORA-00001: unique constraint (APEX_030200.WWV_FLOW_WORKSHEET_RPTS_UK) violated


I received a copy of this application export file, uploaded it to apex.oracle.com, and immediately ran into the same error when I attempted to install it into my workspace. Unfortunately, the customer was in a situation where they could no longer import this application into any other workspace or instance. It appeared that the generated export file and metadata on their production instance was corrupted.

How did they possibly get into this state? Well, it was a sequence of:

1. The customer was first directed to David's blog posting at http://dpeake.blogspot.com/2009/01/preserving-user-saved-interactive.html.
2. That was unsatisfactory, so they followed a link to Martin's blog posting at: http://apex-smb.blogspot.com/2009/10/saving-saved-interactive-reports-when.html
3. Then, not based on any information in either of these blog postings, the customer dropped a unique index and disabled two foreign key constraints from the APEX_030200 schema.
4. Finally, via some manual DML, it appears that the customer was able to modify the metadata of saved Interactive Reports such that they now violated the conditions of this unique constraint.

While I completely understand why the customer went down the path that they did (to restore their end user's Interactive Reports), they should have never resorted to manually modifying the metadata and database objects in the Application Express schema. Fortunately, I was able to easily reproduce the customer's problem and was able to craft a custom patch script for their environment to restore what had been done to it.

Did the customer perform an action which was unsupported by Oracle Support which resulted in the corruption of their environment? Yes. Did the customer have any recourse? No, not really. Was Oracle Support obligated to help this customer recover? Maybe not. But even though they knowingly did this, I didn't want to have one unhappy Oracle and APEX customer when we were done.

In general, Oracle Support will make every effort to help a customer with their Oracle Application Express product issues. But in cases like this one, where the customer performed actions directly against the undocumented, internal Application Express schema, there really aren't any guarantees that Oracle Support nor Application Express product development will be able to help a customer recover from this type of corruption.

Typical unsupported actions which can lead to corruption include dropping users, revoking privileges, dropping/modifying constraints, inserting/updating/deleting data, etc.. This situation is really no different than if a customer dropped SYS.STANDARD from their database or dropped internal packages or views from their eBusiness Suite environment.

Moral of the story: When you're considering to directly modify any objects in the Application Express schema, it's in your best interest to abstain.

Application Express 4.0 Early Adopter Refresh is coming

The Application Express 4.0 Early Adopter's program at http://tryapexnow.com has been a smashing success, so far. 2,440 workspaces have been requested and created on this instance, and a plethora of feedback has been received. Our customers have reported both bugs and made numerous feature suggestions, some of which you will see implemented in Application Express 4.0 production. So thank you for this tremendous amount of participation - this is further proof that Application Express has such a vibrant community.

In the coming weeks, we will be refreshing the Application Express 4.0 Early Adopter's Instance with an updated build of Application Express 4.0. One of the significant features to be enabled in this build will be Oracle Application Express Websheets.

Although it has been our intention to preserve the existing workspaces when we install the new Application Express 4.0 environment, the odds are about 97% that you will need to sign up for an entirely new workspace and schema - that is, nothing from the current Early Adopter's will be preserved. An application export *may* work in the updated instance, but that is not guaranteed either. Certainly, though, all of the submitted feedback from our customers will be preserved and available. We'll provide advance notice of at least a couple weeks before this refresh (really, "rebuild") of the environment will take place. And this new environment will happen in a database with an AL32UTF8 character set.

Thank you for your many contributions to the success of Application Express 4.0.

OTN Developer Day in the Big Apple

For those of you in or near New York City, next week Wednesday, January 13, 2010, at the New York Marriot Marquis, there is an Oracle-sponsored OTN Developer Day- Hands-on Oracle Database 11g Applications Development. There are four different tracks, and one of the tracks is dedicated to Oracle Application Express. The other three tracks are for Database Development, Java and .NET.

This event is free. Also, you should bring your own laptop for the labs throughout the day. You'll walk away with a virtual machine containing Oracle Database 11gR2 with Oracle Application Express 3.2.1 installed. Lastly, this will be a good time to meet with several members of the Oracle Application Express development team, including Mike Hichwa (our VP), Marc Sewtz, David Peake and Christina Cho.