11 Replies Latest reply on Aug 23, 2018 5:12 PM by PotatoBond

    Unique constraint violated while verifying theme

    Scott Wesley

      I've gone to verify the theme to one of our larger applications, and I've received the same error Pierre Yotti and sect55 received in this archived post:

      I am trying to update to apex 5.1 and have this error "1 error has occurred ORA-00001: unique constraint (APEX_050100.WWV_FLOW_PAGE_PLUG_IDX2) violated". I think it is because i have create my Own Template. Somebody can help please?

       

      ORA-00001: unique constraint (APEX_050100.WWV_FLOW_PAGE_PLUG_IDX2) violated

      This particular application has some original templates, which come up as "Not subscribed"

      I've successfully verified other applications that also have their own templates, so I'm not convinced this is the cause, particularly since the error relates to a unique index on WWV_FLOW_PAGE_PLUG_TEMPLATES(FLOW_ID, PAGE_PLUG_TEMPLATE_NAME, THEME_ID).

       

      I'm not sure what I can do to mitigate this?

       

      This is on APEX 5.1.3

       

      Scott

        • 1. Re: Unique constraint violated while verifying theme
          Pierre Yotti

          Hi Scott,

           

          Have you already check that Direction Right To Left in 5.1 ?

           

          I had check that, but it not worked for me. I don't find any Solution for my Problem

           

           

          Regards

          • 2. Re: Unique constraint violated while verifying theme
            Scott Wesley

            It seems unrelated. You need to verify the theme to "be able to use RTL", though I already see the option in app attributes. It's something we don't use here in Australia.

            • 3. Re: Unique constraint violated while verifying theme
              Pierre Yotti

              Hi Scott,

              Could you please share your answers if it works for you?

              Thanks

              Pierre

              • 4. Re: Unique constraint violated while verifying theme
                Scott Wesley

                I have no answer that works, I was describing why I don't think RTL is related.

                • 5. Re: Unique constraint violated while verifying theme
                  Scott Wesley

                  Replicated on 5.1.4 and 18.1 EA2, for our particular application.

                  wwv_flow_page_plug_idx2.png

                   

                  I'm systematically removing any custom templates to (hopefully) find the one causing the problem.

                  • 6. Re: Unique constraint violated while verifying theme
                    Pierre Yotti

                    are the templates still used in the application?

                     

                    Are you remove all custom style for the Theme roller too?

                     

                     

                    Maybe it can help

                     

                    Regards

                    • 7. Re: Unique constraint violated while verifying theme
                      Scott Wesley

                      The problem was not related to a custom template.

                       

                      After removing all of those, I tried to work out if I found them all, so I added the "subscribed from" column in the template list and noticed that it was missing for the standard "Interactive Report" template.

                      And sure enough, when I queried the table being complained about, I see it's missing

                      select id, page_plug_template_name, reference_id
                      from apex_050100.WWV_FLOW_PAGE_PLUG_TEMPLATES
                      where flow_id = 202
                      and theme_id = 42;
                      

                      So I found the ID from an application that was ok, and updated the meta-data.

                       

                      update apex_050100.WWV_FLOW_PAGE_PLUG_TEMPLATES
                      set reference_id = 2099079838218790610
                      where flow_id = 202
                      and theme_id = 42
                      and internal_name = 'INTERACTIVE_REPORT';
                      

                       

                      And then I was successfully able to refresh the theme.

                      I'm not sure what the historical differences are between our applications, so I'm not sure why this reference would have been lost. But I guess I'm not the only one.

                       

                      Scott

                      1 person found this helpful
                      • 8. Re: Unique constraint violated while verifying theme
                        Pierre Yotti

                        Why is was missid from "Interactive Report" template"?

                         

                        Any reasons?

                         

                        Regards

                         

                        Pierre

                         

                        • 9. Re: Unique constraint violated while verifying theme
                          Scott Wesley

                          I'd love to know that myself.

                          And if others experiencing this issue had the same cause.

                          • 10. Re: Unique constraint violated while verifying theme
                            Sven W.

                            I have the same issue, but for the "Standard" and "Title Bar" templates.

                             

                            Also one application hat the problem, that not only the reference was missing, but also no internal names were set.

                             

                            Since REFERENCE_ID has no FK, I'm not 100% sure where the correct origin for the data of this field is.

                            So I slightly modified your update and used the IDs that I found in FLOW_ID=8842.

                             

                            update apex_050100.WWV_FLOW_PAGE_PLUG_TEMPLATES  t
                            set reference_id = (select ut.id from apex_050100.WWV_FLOW_PAGE_PLUG_TEMPLATES  ut
                                                where ut.flow_id = 8842
                                                and ut.page_plug_template_name = t.PAGE_PLUG_TEMPLATE_NAME
                                                )
                              ,internal_name = nvl(t.internal_name, replace(upper(t.PAGE_PLUG_TEMPLATE_NAME),' ','_'))
                            where t.flow_id in (200,201, 202 )
                            and t.theme_id = 42 
                            and t.PAGE_PLUG_TEMPLATE_NAME in ('Standard','Title Bar')
                            and t.reference_id is null;
                            

                             

                             

                            After that I still wasn't able to refresh the theme. I also tried to update the missing internal names first.

                             

                            One thing I noticed is that the clob in the TEMPLATE2 column was NULL for the Standard and the Title Bar templates. And it was empty for the other templates.

                            So I updated this column also to an empty_clob() - with no success.

                             

                            update apex_050100.WWV_FLOW_PAGE_PLUG_TEMPLATES  t
                            set template2 = empty_clob()
                            where t.flow_id = 200
                            and t.theme_id = 42  
                            and dbms_lob.getlength(template2) is null;
                            
                            

                             

                            I believe the problem has to do with the subscription.

                            When I verified the theme subscripion, then the "Standard" and the "Title Bar" region template was listed twice.

                            Once as "Not subscibed" and a second time as "Not Defined in Subscribing Theme".

                             

                             

                             

                             

                            In the end I deleted the "Standard" and "Title Bar" region templates. After that I was able to update the theme.

                            • 11. Re: Unique constraint violated while verifying theme
                              PotatoBond

                              Hi Scott,

                               

                              Thank you for the informative fix. I am having a similar problem when trying to assign a schema to a workspace. The error looks like this:

                              The weird thing is that the workspace in question (OBE) shows up twice. I managed to change the scheme for one, but for the other, it gave the error message. So I tried to apply your fix by updating the table APEX_050100.WWV_FLOW_COMPANY_SCHEMAS (without the "_IDX" from the screenshot), but it gives the following error:

                               

                              The _IDX constraint is nowhere to be found (btw, i'm new to this so maybe that would be the reason i can't find it ).

                               

                              Sorry to revive this topic, but any help would be greatly appreciated.

                               

                              Thank you,

                              Razvan