This discussion is archived
8 Replies Latest reply: Oct 1, 2012 12:22 AM by Nattu RSS

BUG in Export causing ORA-02291 (WWV_FLOW_PAGE_DA_A_AR_FK) - Reason

ApexBine Newbie
Currently Being Moderated
I found the cause for the import failing with exception
"Error     ERR-1029 Unable to store session info. session=4478491491094170 item=40006855470898
          ORA-02091: transaction rolled back ORA-02291: *integrity constraint (APEX_040100.WWV_FLOW_PAGE_DA_A_AR_FK) violated* - parent key not found"

Usually wwv_flow_api.create_page_da_action has a pair of p_affected parameters.
Like
- p_affected_elements_type => 'REGION'
- p_affected_region_id => 247245133258676897 + wwv_flow_api.g_id_offset
or
- p_affected_elements_type => 'ITEM'
- p_affected_elements => 'P4_DISP_OBJ'
or
- p_affected_elements_type => 'JQUERY_SELECTOR'
- p_affected_elements => 'span.tooltip-plsql'

In the failing export file I found only this parameter
- p_affected_region_id => 393187746780221880 + wwv_flow_api.g_id_offset
without the corresponding
- p_affected_elements_type => 'REGION'

The affected element used to be a REGION that was changed to NONE (= '- Select Type -' on the Create / Edit Action page).
Obviously Apex changed the type but did not delete the corresponding value which then was exported.

After removing the line from the export file the import worked :-)

Best regards,
Sabine


(Application Express 4.1.0.00.32)
  • 1. Re: BUG in Export causing ORA-02291 (WWV_FLOW_PAGE_DA_A_AR_FK) - Reason
    Kofi Journeyer
    Currently Being Moderated
    Just had a similar issue. Thanks Sabine for your post.
    Do we need to raise a Bug for this or are the Apex folks monitoring? Any of the Oracle Apex development team care to comment on this?
    Cheers
    Kofi
  • 2. Re: BUG in Export causing ORA-02291 (WWV_FLOW_PAGE_DA_A_AR_FK) - Reason
    AnthonyRayner Pro
    Currently Being Moderated
    Hi ApexBine and Kofi,

    Thank you both for your posts. I am filing a bug on this at present and will update the thread with details shortly.

    Sorry this has caused you problems.

    Regards,
    Anthony
  • 3. Re: BUG in Export causing ORA-02291 (WWV_FLOW_PAGE_DA_A_AR_FK) - Reason
    AnthonyRayner Pro
    Currently Being Moderated
    Hi both,

    Just to update you, I have filed bug #13441441 for this issue, 'DYNAMIC ACTION AFFECTED REGION ID NOT PROPERLY CLEARED WHEN IT SHOULD BE'. We have this targeted to be fixed by the first 4.1 patchset.

    From my tests, I was only able to reproduce this incorrect metadata state when the DA used an 'Action' type that allowed selection of 'Affected Elements' (some don't, like 'Submit Page'), but did not mandate them (for example the 'Execute JavaScript Code' action).
    This could happen, say if you had tried to do something with one of the native action types first (for example 'Show' or 'Hide'), selecting a specific region as the affected element, but then later switched to using 'Execute JavaScript Code' maybe because the native type didn't quite do what you needed. When switching to 'Execute JavaScript Code', the affected elements are still relevant (because you can still reference them from your JS using 'this.affectedElements') so they will still be displayed. However it is here that this metadata can slip through our validations, because if you no longer wanted the region as the affected element, you would most likely just change the 'Selection Type' to '- Select Type -', importantly (and understandably) without clearing the previously selected region. You then have this incorrect metadata state. Once in this state, you can get mutating trigger errors when trying to delete the associated region, and also if you copy a page containing one of these references to a different application, this can lead to the foreign key errors when you try and import that application (because the affected region ID doesn't actually exist in the application where this was copied to).

    If this was the case, and to workaround this for the moment without having to edit the export every time, you can go back into the DA, temporarily select 'Region' again for the 'Selection Type', clear the 'Region', change the selection type back to '- Select Type -' again and save this. This should clean this out and get rid of the issue for you.

    Hope that helps, let me know if so, or your scenario was different at all. Sorry again about this, but hopefully that workaround is of some use to you and others, until we get this fixed in the patch.

    Regards,
    Anthony.
  • 4. Re: BUG in Export causing ORA-02291 (WWV_FLOW_PAGE_DA_A_AR_FK) - Reason
    Jitendra Pro
    Currently Being Moderated
    Hi All,

    I had also faced same issue while importing my application.
    Thanks for your post.

    Regards,
    Jitendra
  • 5. Re: BUG in Export causing ORA-02291 (WWV_FLOW_PAGE_DA_A_AR_FK) - Reason
    Gussay Journeyer
    Currently Being Moderated
    Hi,

    Application Express 4.0.2.00.07
    11g DB

    I faced this issue too. It was a dynamic action I had copied from one page to another.

    After reading this I removed the offending DA and rebuilt it again from scratch. This worked for me.

    IMHO, Ant deserves some points recognition here (hint to Original Poster).

    Gus..
  • 6. Re: BUG in Export causing ORA-02291 (WWV_FLOW_PAGE_DA_A_AR_FK) - Reason
    ApexBine Newbie
    Currently Being Moderated
    Gus, I can't find a button for giving points... :-(
  • 7. Re: BUG in Export causing ORA-02291 (WWV_FLOW_PAGE_DA_A_AR_FK) - Reason
    ApexBine Newbie
    Currently Being Moderated
    Ran into the problem again today. (We don't have the patch yet :-( )
    To find the offending pages I used
    SELECT *
      FROM apex_040100.wwv_flow_page_da_actions da
     WHERE affected_elements_type = 'REGION'
       AND NOT EXISTS (SELECT id
              FROM apex_040100.wwv_flow_page_plugs pp
             WHERE da.flow_id = pp.flow_id
               AND da.affected_region_id = pp.id);
  • 8. Re: BUG in Export causing ORA-02291 (WWV_FLOW_PAGE_DA_A_AR_FK) - Reason
    Nattu Explorer
    Currently Being Moderated
    Hi,

    We also encountered the same issue and the suggested work around and the query to find out the erratic dynamic actions were very useful to fix it.

    Just for easier identification, I have changed the query to return the dynamic action name also.

    SELECT de.name, da.*
    FROM apex_040100.wwv_flow_page_da_actions da
    JOIN apex_040100.wwv_flow_page_da_events de
    ON de.id = da.event_id
    WHERE affected_elements_type = 'REGION'
    AND NOT EXISTS (SELECT id
    FROM apex_040100.wwv_flow_page_plugs pp
    WHERE da.flow_id = pp.flow_id
    AND da.affected_region_id = pp.id);

    Thanks to all whoever contributed.

    Regards,

    Natarajan