1 2 Previous Next 15 Replies Latest reply: Mar 20, 2013 8:38 AM by user10341872 RSS

    mru internal routine: ORA-20001:

    210640
      Hi all,
      I am trying to update the detail record from a master - detail form and I am getting following error message. I have no clue what the problem is, and how to make ID uneditable.
      =========
      Error in mru internal routine: ORA-20001: Error in MRU: row= 1, ORA-20001: ORA-20001: Current version of data in database has changed since user initiated update process. current checksum = "1A4B00EBB2107BA61B1F764B909A1B92", item checksum = "89C17DAD3E2403A2C9968F443B0C5D68"., update "CVS"."CVS_VENDOR_CONTACTS" set "ID" = :b1, "VENDOR_ID" = :b2, "FIRST_NAME" = :b3, "LAST_NAME" = :b4, "CONTACT_TYPE" = :b5, "PHONE" = :b6, "FAX" = :b7, "EMAIL" = :b8, "EMP_RELATIONSHIP" = :b9, "RELATIONSHIP_DESC" = :b10, "PRIMARY_CONTACT" = :b11, "STATUS
      =========
      I have ID column in my tabular form as hidden as well. Inserting a new record is not a problem. I have hidden the delete features as the records are not allowed to be deleted. I have look into this thread, but have no idea what is the meaning of changing the query afterward.

      http://forums.oracle.com/forums/thread.jspa?messageID=3257438&tstart=0#3257438


      Can someone please advise? Thank you.

      SHY
        • 1. Re: mru internal routine: ORA-20001:
          700201
          Hello SHY,

          If this only happens time to time, ask user to re-enter data when it happens. Someone - or a process - changed its record just he was editing it.

          But if this happens always this have to be investigated - I do not have a pre-conceived idea of what is happening -. Update post and we will try to get an idea.
          • 2. Re: mru internal routine: ORA-20001:
            210640
            Hi Manel,
            Thanks for your response. It happen all the time whenever I edit the existing data in the detail tabular form. Updating fields in the master form alone is not a problem. And since it is in the development stage, only one user is currently using the databases. Please advise. Thanks.
            SHY
            • 3. Re: mru internal routine: ORA-20001:
              710270
              Hi,

              I'm encountering same kind of error as you describe, did you find the solution to solve this ?
              • 4. Re: mru internal routine: ORA-20001:
                ATD
                Hi,

                Typically, this message will appear if another user has edited one, or more, of the rows being submitted in the tabular form between when the data was retrieved and when it was submitted.

                However, it can also happen if the tabular form itself doesn't submit the same fields as it retrieved and/or the data retrieved has been changed in someway in the SQL statement. As a simple example, suppose your tabular form includes in its SQL statement EMPNO, ENAME and JOB from the EMP table but the submit process only submits the EMPNO and ENAME values. The checksum calculated when the row is retrieved is based on all three fields but the checksum calculated when the row is submitted will only contain two fields. In these cases, the first thing to do would be to check that ALL fields used in the SQL statement are included in the submit process and that you are not doing anything to the data in the SQL statement (such as adding styling tags).

                Andy
                • 5. Re: mru internal routine: ORA-20001:
                  710270
                  Hi, thx for the reply and I suppose your last comment is my problem:

                  my region source SQL is as follows

                  select
                  "WORKHOUR_ID",
                  "PERIOD_ID",
                  "USER_NAME",
                  TO_CHAR(from_date,'DD') AS "DAY",
                  TO_CHAR(from_date,'HH24') AS "FROM_HOUR",
                  TO_CHAR(from_date,'MI') AS "FROM_MIN",
                  TO_CHAR(to_date,'HH24') AS "TO_HOUR",
                  TO_CHAR(to_date,'MI') AS "TO_MIN",
                  "FROM_DATE",
                  "TO_DATE",
                  "DESCRIPTION"
                  from "#OWNER#"."TEO_WORKHOURS"
                  where PERIOD_ID = :P18_PERIOD
                  and USER_NAME = :P18_USER

                  and I suppose only the "WORKHOUR_ID",
                  "PERIOD_ID",
                  "USER_NAME",
                  "FROM_DATE",
                  "TO_DATE",
                  "DESCRIPTION"

                  are really used at submit ?

                  Any way how I can fix this ? Possible to exclude some columns from the checksum within the select ?
                  • 6. Re: mru internal routine: ORA-20001:
                    ATD
                    Hi,

                    That field list should be ok provided all of them are included in the submit and not just shown for display purposes. For example, you have from_date and to_date split in to individual dateparts, presumably for display purposes but are they also editable as whole fields?

                    Andy
                    • 7. Re: mru internal routine: ORA-20001:
                      710270
                      Hi,

                      the
                      TO_CHAR(from_date,'DD') AS "DAY"
                      TO_CHAR(from_date,'HH24') AS "FROM_HOUR"
                      TO_CHAR(from_date,'MI') AS "FROM_MIN"
                      TO_CHAR(to_date,'HH24') AS "TO_HOUR"
                      TO_CHAR(to_date,'MI') AS "TO_MIN"

                      are also enterable

                      however
                      "FROM_DATE"
                      "TO_DATE"
                      are not displayed (hidden) and supposed to be populated based on the values entered in the 5 fields above

                      However I've commented those two fields out, still have the same error.

                      "WORKHOUR_ID", -> populated by db trigger, not editable
                      "PERIOD_ID", -> populated by page item value, not editable
                      "USER_NAME", -> populated by page item value, not editable
                      • 8. Re: mru internal routine: ORA-20001:
                        ATD
                        Hi,

                        OK - it is, possibly, the non-editable items that are causing the problem.

                        If you were to create a tabular form, using the Wizard, to display EMPNO, ENAME from EMP, you will get TWO versions of EMPNO - one as EMPNO and one as EMPNO_DISPLAY. The EMPNO version will be a hidden item. This ensures that EMPNO is included in the submit process. The EMPNO_DISPLAY is a Standard Report Column that displays the value to the user. You should try the same thing for your three non-editable fields - have one version hidden and one version displayed. As long as the date items are being populated correctly, you should then have all items submitted correctly.

                        Andy
                        • 9. Re: mru internal routine: ORA-20001:
                          710270
                          Hi,

                          did some more tests and mru error is caused by the

                          TO_CHAR(from_date,'DD') AS "DAY",
                          TO_CHAR(from_date,'HH24') AS "FROM_HOUR",
                          TO_CHAR(from_date,'MI') AS "FROM_MIN",
                          TO_CHAR(to_date,'HH24') AS "TO_HOUR",
                          TO_CHAR(to_date,'MI') AS "TO_MIN",

                          fields

                          those fields are editable but shouldn't submitted to the database, actually no table column exists for those fields.

                          I would like to use the values entered in those fields (day, from_hour, to_hour,...) to populate the from_date and to_date table columns, but I'm not sure if this kind of functionality is feasible within apex ? any hints are more than welcome.
                          • 10. Re: mru internal routine: ORA-20001:
                            ATD
                            Hi,

                            I think you have at least three choices:

                            1 - Those items can be created as part of the output only by creating them in a new Derived Column's HTML expression - not particularly nice way to do it, but should work. You would need to use javascript to handle the update of the hidden date items based on the displayed day/hour/min items
                            2 - Create the tabular form "manually" - this allows you to create the exact checksum values you need when retrieving and saving data (the process would also have to be done manually, but is fairly simple)
                            3 - Create a SQL View and base the tabular form on this view - this would require you to include those extra fields as part of the view and create an INSTEAD OF trigger on the view to handle any inserts/updates/deletes

                            Andy
                            • 11. Re: mru internal routine: ORA-20001:
                              710270
                              Hi,

                              thanks for the options, but could you point me to a manual/example on how to implement option 2 ? I suppose this one is the easiest option ?

                              Thx,
                              Peter
                              • 12. Re: mru internal routine: ORA-20001:
                                ATD
                                Hi Peter

                                I don't have an exact example that gives you all you need, but here's one that explains creating a manual tabular form: [http://apex.oracle.com/pls/otn/f?p=33642:189] - ignore the bits about creating a function, just take note of the use of APEX_ITEMs to create the form's input items, the MD5_CHECKSUM to create the checksum value for the form and the final APEX_ITEM.MULTI_ROW_UPDATE bit which is how a manual tabular form can create/update records. The APEX_ITEM package is explained here: [http://download-uk.oracle.com/docs/cd/B32472_01/doc/appdev.300/b32471/api.htm#sthref2698]

                                Andy
                                • 13. Re: mru internal routine: ORA-20001:
                                  644738
                                  FYI... I hit this error today and couldn't figure it out for a while. I had set the "row_selector" display option to No since the user can not delete rows thinking there was no reason for them to see that check box. When I set the option back to display = yes everything worked ok.
                                  • 14. Re: mru internal routine: ORA-20001:
                                    Yeucai
                                    I am facing the same problem and have been reading through this thread but can't seem to find the solution. Can someone please help
                                    1 2 Previous Next