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:
- The customer was first directed to David's blog posting at http://dpeake.blogspot.com/2009/01/preserving-user-saved-interactive.html.
- 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
- 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.
- 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.