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

multiple_ir_on_page

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!

Posted in APEX (Application Express), Weird Bugs
3 comments on “Import Problems with Application Express 4.2.2
  1. Cyndi Chie says:

    Thank you. I had the same thing occur and your explanation helped me find and resolve the issue quickly.

  2. same problem, same work around (delete the existing application before import)

  3. ericyuri says:

    we can use this view too

    SELECT *
    FROM apex_application_page_ir_rpt cond

    WHERE application_id = :APP_ID

    And then go to the REPORT_COLUMNS and REPORT_TYPE COLUMNS .

    And you’ll see the duplicate reports :@

Leave a Reply to Cyndi Chie Cancel reply

Alan Warren

Alan Warren

These days I develop software using the Oracle Database, and Application Express (APEX). Outside of work I enjoy cottaging, trivia, and billiards. I'm a long time resident of Guelph, Ontario Canada.

View Full Profile →

Subscribe

Enter your email address to subscribe to this blog and receive notifications of new posts by email.