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.