Wednesday, January 27, 2010

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.

5 comments:

Roel said...

So the message is: "Don't mess with the repository"

Unknown said...

Interesting post Joel....

It's kinda funny that what you said highlights the fact that Oracle still fixes unsupported actions :)

Customers always win, don't they?? They can simply claim it was a rogue developer, or jump up and down and throw a tantrum!

You can warn all you like, but it's like telling your kids not to do something... they still do it!! If anything, telling them not to dot it brings it to their attention so they probably will do it. If you don't punish them they'll do it again!

Your post might have the opposite affect than you intended ;-)

Joel R. Kallman said...

Enzo,

Interesting analogy. I compare this more to the situation when you tell your child not to do something again "or else", they still do it, and then then you hold true to your word and deliver the "or else".

This is really just a warning, more than anything (to adults, not kids). Oracle Support cannot guarantee they can help. And if they can't, well...good luck explaining that to your management.

Joel

Gaus said...

Hi Joel,

Can you kindly share the steps for how you went about to fix this issue. We have been facing the same issue with an application when exporting from Staging to Production and now we find that the error has also reproduced when installing from Development to Staging... :(

Joel R. Kallman said...

Hi Mohammed,

It's unclear if you're encountering this specific problem, so I'm hesitant to describe any steps to remedy this. Did you also directly manipulate objects in the APEX schema? Have you contacted Oracle Support about this?

Joel