This discussion is archived
3 Replies Latest reply: May 3, 2012 3:32 PM by GiedriusDeveikis RSS

ORA-02291: integrity constraint (APEX_040100.WWV_FLOW_PAGE_DA_A_AR_FK)

Kiran Expert
Currently Being Moderated
Hello,

Technical Specifications for Oracle APEX:
<ul>
<li>Oracle Application Express Version: Application Express 4.1.0.00.32</li>
<li>Oracle Database Version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production</li>
<li>APEX Web server architecture: Embedded PL/SQL Gateway</li>
<li>Operating system: Red Hat Enterprise Linux 5 (32-bit)</li>
</ul>

Sorry for posting again the same problem! But the solutions proposed here didn't resolve my problem:

{thread:id=2313802}

I cleared the dynamic actions from the export file as suggested by @ApexBine but to no avail.

I did thought about the solution @Anthony Rayner suggested but preferred to delete the page containing faulty dynamic actions.
After clearing the page I crosschecked with the Application Express View - APEX_APPLICATION_PAGE_DA_ACTS for inconsistency if any in the metadata for dynamic actions but found it clean.

Also checked with APEX_APPLICATION_PAGE_DA view for any inconsistency and then exported the application.

But on import in a new workspace I got stuck with the same error again:

<pre>
ERR-1029 Unable to store session info. session=317579182630046 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
</pre>

session=317579182630046 in the above error is the session id for the workspace I am currently connected to.

What is item=40006855470898 ?
Is it one of the application deployment checks ?
Please suggest where am I going wrong.

Thanks in advance,
Kiran
  • 1. Re: ORA-02291: integrity constraint (APEX_040100.WWV_FLOW_PAGE_DA_A_AR_FK)
    Kiran Expert
    Currently Being Moderated
    Hello,

    I was able to resolve my problem by querying the APEX_040100 schema and correcting the faulty DAs.

    After encountering and many failed trials for resolving APEX_040100.WWV_FLOW_PAGE_DA_A_AR_FK integrity constaint violation, I thought of having a peek at APEX_040100 schema.

    I found that metadata for dynamic actions is stored into WWV_FLOW_PAGE_DA_ACTIONS whose constraint WWV_FLOW_PAGE_DA_A_AR_FK was getting violated is defined in APEX_040100 as:
    CONSTRAINT "WWV_FLOW_PAGE_DA_A_AR_FK" FOREIGN KEY ("AFFECTED_REGION_ID") REFERENCES "APEX_040100"."WWV_FLOW_PAGE_PLUGS" ("ID") ON
      DELETE
        SET NULL DEFERRABLE INITIALLY DEFERRED ENABLE
    So I queried it to find which DAs were at fault (inconsistent metadata) :
    select ID, PAGE_ID from APEX_040100.WWV_FLOW_PAGE_DA_ACTIONS
    where FLOW_ID = 140
    and AFFECTED_REGION_ID NOT IN (select ID from APEX_040100.WWV_FLOW_PAGE_PLUGS where FLOW_ID = 140);
    Here FLOW_ID represents the Application Number.

    I got four IDs (dynamic action id) which were at fault and page numbers viz. PAGE_ID on which they were present which were then resolved by the method @Anthony Rayner mentioned in above thread i.e. by reassigning the appropriate affected regions in the action.

    Please don't do something like:
    delete from APEX_040100.WWV_FLOW_PAGE_DA_ACTIONS
    where FLOW_ID = 140
    and AFFECTED_REGION_ID NOT IN (select ID from APEX_040100.WWV_FLOW_PAGE_PLUGS where FLOW_ID = 140);
    as this will further aggravate the problem of inconsistent metadata(I have not tried but will advise a strict no). I just used the APEX_040100 schema to query it.

    I got curious about the APEX_040100.WWV_FLOW_PAGE_DA_A_AR_FK integrity constaint and why it was causing problem in APEX 4.1, so I had a previous schema APEX_040000 of APEX 4.0 in which the constraint APEX_040000.WWV_FLOW_PAGE_DA_A_AR_FK was defined as:
    CONSTRAINT "WWV_FLOW_PAGE_DA_A_AR_FK" FOREIGN KEY ("AFFECTED_REGION_ID") REFERENCES "APEX_040000"."WWV_FLOW_PAGE_PLUGS" ("ID") ON
      DELETE
        SET NULL ENABLE
    Regards,
    Kiran
  • 2. Re: ORA-02291: integrity constraint (APEX_040100.WWV_FLOW_PAGE_DA_A_AR_FK)
    417717 Newbie
    Currently Being Moderated
    Thanks Kiran for your speedy reply and lengthy explanation.

    I actually solved the problem as follows:
    1) In the source APEX app in Shared Components, create a Build Option that excludes objects from export
    2) Search for and find all the Dynamic Actions that have "Execute JavaScript Code" in their sub-actions (Use global search at the top)
    3) Apply the export exclusion Build Option to all the DAs in the source app identified in step 2 (at the DA level NOT the sub-action level)
    4) Create a new source export and if you import it into the target APEX instance you no longer get the import error. This identifies that it was at least one of
    the DAs in the source application. (But of course you now have lost the DA functionality in the target application!)
    5) Go back to the source app and turn off the Build Option against each DA identified in step 3.
    6) Do another Export/Import cycle and hopefully the import now works! (Well it did for me anyway).

    hope this helps someone down the track.
    PaulP
  • 3. Re: ORA-02291: integrity constraint (APEX_040100.WWV_FLOW_PAGE_DA_A_AR_FK)
    GiedriusDeveikis Newbie
    Currently Being Moderated
    Integrity constraint at DB level here helps only to ensure that affected region exists in apex installation, but logically dynamic actions will affect region only in same page.
    I encountered same error when copied some pages from one application to another, translated the latter and tried to publish translated application.
    Problem with APEX_APPLICATION_PAGE_DA_ACTS that it also like constraint looks for region name in all instance regions and you cannot see where error is.
    I quickly found incorrect element by executing this SQL:
    SELECT workspace_display_name, application_id,application_name, page_id, page_name, dynamic_action_name, action_name,action_sequence
      FROM apex_application_page_da_acts
     WHERE (application_id,page_id, affected_region_id) 
        IN (SELECT application_id,page_id, affected_region_id
              FROM apex_application_page_da_acts
             WHERE affected_region_id IS NOT NULL
             MINUS
            SELECT application_id,page_id,region_id
              FROM apex_application_page_regions)
    After identifying it, I corrected region reference (it was referring to region in original page, but not to region in new page copy) and successfully published translated application.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points