Import Problems with Application Express 4.2.2

While upgrading from APEX 4.1 to 4.2.2 we ran into a surprising error during application imports.  Near the end of the import we’d get a unique constraint violation on APEX_040200.WWV_FLOW_WORKSHEET_RPTS_UK, while I’m sure we’re not the only shop to run into this, it doesn’t seem very common.  As pointed out by Dave in his OTN post deleting the application prior to import allows the import to run successfully and without the unique constraint exception.  Subsequent installs still required a full delete/install, so getting the 4.2.2 version of the export installed once doesn’t fix the underlying issue.

After harassing the rest of the team to ensure nobody had fiddled directly with the flows tables I set about trying to find a more sustainable solution.  The constraint in question is created from a functional index whose definition was foreign to me

CREATE UNIQUE INDEX APEX_040200.WWV_FLOW_WORKSHEET_RPTS_UK 
 ON APEX_040200.WWV_FLOW_WORKSHEET_RPTS (
   CASE WHEN "SESSION_ID" IS NULL THEN "WORKSHEET_ID" END , 
   CASE WHEN "SESSION_ID" IS NULL THEN "APPLICATION_USER" END , 
   CASE WHEN "SESSION_ID" IS NULL THEN "CATEGORY_ID" END , 
   CASE WHEN "SESSION_ID" IS NULL THEN "NAME" END 
);

I believe this creates a composite index where each column is a simple function consisting of one CASE statement, and include a unique constraint on that index.  Supposing this means the complete tuple must be unique I set out to find two interactive reports with the same WORKSHEET_ID which could cause a collision.  For all my searching I couldn’t find anything that might cause a unique constraint violation in my export.

But I did happen across a page that contains two interactive reports with mutually exclusive conditions on the report regions (The big problem with multiple interactive reports in APEX comes from the JavaScript, not the APEX rendering).  Here’s what the two IR on the page looked like in apex_040200.wwv_flow_worksheet_rpts

So, since the worksheet_ids are unique the CASE tuples should be unique.  This didn’t look like it could be the problem, but since nothing else was working I tried re-factoring the page to use a single IR and embed the conditional logic in the interactive report definition.  After stripping the page back to one IR, the export/import worked perfectly!  So, we’re back to being able to install the app in place with minimum disruption.

If you run into this error, I hope this approach works for you too.  But, I still don’t understand how this exception could be thrown here.  The part of the import that kicks off the exception is in application/deployment/buildoptions.sql and is a call to wwv_flow_api.post_import_process, which is of course obfuscated — it could be manipulating ids to be functionally dependent on the page_id, or using an internal api to raise a reserved exception, or just demonstrating my ignorance of how composite functional indexes work!