9 Replies Latest reply: Oct 2, 2010 5:36 AM by Arie Geller RSS

    Apex 4.0.1 Tabular Form Checksum

    640914
      Hey all,

      I am having many issues with checksum errors turning up in a tabular form, in apex 4.0.1.

      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 = "CDED1770EEE9B87CB8BAEEF33B4699E2", item checksum = "B8441A9325CBF4E4881E693DCC6BBB66"

      I understand that these things can turn up when modifying a tabular form. However, it is regardless as these changes are required per the tabular form functionality request and capabilities.
      The modifications are almost too many to mention, but to just quickly describe...
      - Dozens of fields on the tabular form that most of have different display requirements, some display only, some conditional display, some popup, some javascript,etc.
      - Dozens of hidden items on the page for other calculations.
      - Several on load processes.
      - Dozens of validations, some on form, some on page items.
      - And others.

      I have tried several times to recreate the page and see the exact spot when the checksum starts failing but with the time needed for and redundacy of recreating this, it is very difficuly to, make one change, ensure all existing functionality remains, and depending on the results, fix or move on... extremely tedious.

      All these changes are functionality that the software allows, there is nothing outside of the norm, meaning no importing custom themes, or crazy javascript, or laborious ajax calls, etc.

      Finally, and hopefully contradictory to many of the other threads I can find on this topic in this forum, the point of this thread is ...
      I don't care why it is happening I just want a fix for it!  LOL

      There must be some simple solution, I mean in the error message it tells you what value it is expecting.
      Isn't there somewhere I can slam that value into?
      Or isn't there somewhere I can bypass the checksum some how?

      Just looking for options, cause the typical development path is not sufficing for me.

      Thanks much in advance,
      Hayden

      Edited by: hszero on Sep 24, 2010 11:20 AM
        • 1. Re: Apex 4.0.1 Tabular Form Checksum
          640914
          Come on experts, someone has to have some experience with this topic!
          Or at least an opinion, I am imagining this is a very common issue.
          Thanks in advance!
          Hayden
          • 2. Re: Apex 4.0.1 Tabular Form Checksum
            Marc Sewtz-Oracle
            Hayden,

            Without knowledge of the exact modifications you made after the initial generation of your tabular form, this question in pretty hard to answer. So let me try outlining how the lost update detection works, perhaps this helps you identifying which modification(s) caused your issue.

            First of all, any tabular form column that uses an "editable" display type is assumed to be part of your underlying table (or view). This includes hidden fields, and display-as-text / submit-state fields. Those "editable" columns are mapped to the underlying table using the column alias (as shown on the report attributes pages). If any of those columns do not exist in the underlying table, you will get an error at runtime. While rendering the tabular form, the values of all editable columns are used to build a checksum value. In order for the checksum to be computed correctly, the original value must not be changed in your SQL query, so you can't make any API calls or apply any format masks, or do any other formatting as part of your query. If you do want to use format masks, LOVs, etc, then you need to use the column attributes.

            So then once the tabular form got rendered, that checksum is stored in a hidden column called "fcs". Once you're done updateing and entering data, and submit your tabular form, the first thing that happens is that APEX executes all the tabular form validations. If all of them pass, APEX computes another set of checksums for each row based on the data submitted. If those checksums don't match the initial checksum, APEX assumes that the user modified a value in that row and attempts to do an update. Prior to the actual update, APEX builds a third checksum on the then current data in the database. And if the initial checksum and the latest checksum match, it's safe to proceed with the update, since no other process or user modified that data in the meantime.

            Not sure if this is of any help in your particular case, if not I think your best option would be to go back again, re-create a wizard-generated tabular form, then start modifying, and provide a specific example for when you run into this issue. Of course, you could also not use the build-in checksum at all, and write your own PL/SQL processes to perform the updates.

            Regards,
            Marc

            Edited by: Marc Sewtz on Sep 29, 2010 2:58 PM
            • 3. Re: Apex 4.0.1 Tabular Form Checksum
              640914
              Marc, Thanks for the info, this is exactly the type of information I was looking for.
              Unfortunately, there are some dynamic updates that are required to be made in the query and not on the column attributes.

              At least one of these is causing the checksum to fail, it is just very difficult to determine which one it is.
              If I had a way of figuring out which one it is, there could maybe be changes made to the definition of the form to better align with the tabular form's best practices.
              Very hard to fix something when you don't know what is broken.
              Do you have any recommendations on how to find the troublesome column, without the brute force method?

              Also, you mentioned that apex builds the checksum several times. Do you have any additional details about how the checksum is actually built?
              I mean, is it just an extension of the apex_item.md5_checksum function?
              Or would it be possible for me to replicate the checksum building code, by providing the same parameter values?

              The information you have provided already, has been very useful, and is greatly appreciated.
              Thanks again,
              Hayden
              • 4. Re: Apex 4.0.1 Tabular Form Checksum
                Marc Sewtz-Oracle
                Hayden,

                The only recommendation I can give is not to alter your SQL statement for any columns that are update-able. APEX is using apex_item.md5_checksum, and it's including all columns identified as update-able in that md5 call. You can certainly build your own md5 call, but given that your implementation seems to require significant alternations of the SQL statement, perhaps you'd be best off to completely build your own update process. And then rather than using checksum, you could for example use a last-updated column to determine if there had been other updates, or you could add an update-counter to your underlying table for this purpose. There are many different ways to implement this type of lost-update detection, the only reason we chose md5 checksums is because APEX needs to be generic enough to work on any table a customer might have. But if you control both the application and the underlying table structure, you'd have more flexibility.

                Regards,
                Marc
                • 5. Re: Apex 4.0.1 Tabular Form Checksum
                  Arie Geller
                  Hello Hayden,

                  On top of Marc’s great explanations and advices …

                  >> Very hard to fix something when you don't know what is broken.

                  I agree with you. The problem is that if you know how MD5 is computed you know that there is no way to determine, from the MD5 value alone, which column is responsible to the final MD5 value.

                  >> Do you have any recommendations on how to find the troublesome column, without the brute force method?

                  I tend to agree with Marc’s advice. You should try to build your own update process. If you are not mastering the Optimistic Locking algorithm, you can go to SQL Workshop => Object Browser => Create => Package and choose the option of Package with methods on database table(s) on one of your tables. It will create a package that includes all the DML procedures for the selected table. The update procedure implements Optimistic Locking, using MD5. You can study it to understand how it works. Bear in mind though that the MD5 function in the package is not identical to the one the APEX engine is using.

                  >> Do you have any additional details about how the checksum is actually built?
                  I mean, is it just an extension of the apex_item.md5_checksum function?


                  The apex_item.md5_checksum function actually returns an HTML code for a hidden column that includes the checksum APEX computed for all the updateable columns in your report. The MD5 value itself is computed using the wwv_flow_item.md5 function. This is an undocumented, hence unsupported APEX function (and that’s probably the reason why Marc, a senior member of the APEX development team, didn’t mention it). The trick with this function is that you must supply it with all its 50 parameters – all the updatable columns plus null columns up to the 50 parameters. Risking some self-promotion, my book includes a detailed example on how to compute the MD5 value as the APEX engine does.

                  If it all sounds too complicated for you, you can always take Marc’s advice and use some other method for the lost update issue.

                  Regards,
                  Arie.

                  -------------------------------------------------------
                  ♦ Please remember to mark appropriate posts as correct/helpful. For the long run, it will benefit us all.

                  ♦ Author of Oracle Application Express 3.2 – The Essentials and More
                  • 6. Re: Apex 4.0.1 Tabular Form Checksum
                    640914
                    Thanks for the responses Marc, Arie!
                    It certainly helps.

                    Meant to mention in an earlier post but apparently forgot.
                    I did end up having to basically write the tabular form logic manually.
                    Including all the updates, inserts and delete, plus the update monitoring logic to know which rows to update.
                    Plus a more inclusive and capable validation process.

                    This is unfortunate in this scenario since the standard application express tools are very close to sufficing all my needs.
                    A little more flexibility in the update of the query, and maybe a little advancement of the tabular form field validation capabilities, and all the standard tools could be utilized.
                    As you all know, likely reducing the development time greatly.

                    This is not a shot at application express incababilities!
                    I am a huge proponent of this product and would recommend to many.
                    It is only a matter of fully understanding the products workings.

                    Thanks again,
                    Hayden
                    • 7. Re: Apex 4.0.1 Tabular Form Checksum
                      510477
                      Just an addition, but two things I've done about the MD5 checksum issues:

                      1) If you need to change the query, copy the query and create a new report region using the modified query. Delete the old region. This usually takes care of the MD5 thing. The report wizard is quick and easy, but depends on not having to change the underlying query once it is built. Sometimes I have to create a new page and re-link. It's painful, but a limitation of using the wizard to generate the page.

                      2) Take a look at collections. They are fairly involved in that you have to write ALL the interactions from fetch to update, but they can be very powerful and are very flexible. Once you get the hang of them you can create a set of templates to help develop new forms based on the collection. If you need advanced functionality, collection-based tabular forms can deliver. I don't use the wizard to build tabular forms anymore; I rely completely on collection-based tabular forms - mostly because my users rarely if ever know what they want the first time around! ;)
                      • 8. Re: Apex 4.0.1 Tabular Form Checksum
                        640914
                        Interestingly enough blarman I tried what you are suggesting in your first point.
                        We also use collections in many of our applications but wasn't quite a great fit for this process.

                        But back to the point, as you can't create mutliple tabular forms on a single page, you suggested a new region and deleting the old region.
                        Instead of that method I tried copying to a new page... but kept getting the same checeksum error.
                        I was expecting this to aleast be a different error, but exact same checksums were failed.
                        Pretty sure there is something I am missing about why this checksum would transfer to the new page.
                        Thanks for the suggestion,
                        Hayden
                        • 9. Re: Apex 4.0.1 Tabular Form Checksum
                          Arie Geller
                          Hello Hayden,

                          >> Instead of that method I tried copying to a new page... but kept getting the same checeksum error.
                          I was expecting this to aleast be a different error, but exact same checksums were failed.


                          If I understand your situation correctly, your problems might be explained by the following, from Marc:

                          *“In order for the checksum to be computed correctly, the original value must not be changed in your SQL query, so you can't make any API calls or apply any format masks, or do any other formatting as part of your query. If you do want to use format masks, LOVs, etc, then you need to use the column attributes.”*

                          I’ll try to give you a simple example. Let’s say you want to display the SAL column (from the EMP table) as a readable sum – 1,234.00 . The right (and working) way is to use the Number / Date Format field of the column with the appropriate mask. However, if you’ll do it as part of your SQL query, like:
                          SELECT
                          . . .
                          to_char("SAL",'999G999D99') as "SAL",
                          . . .
                          You’ll get a checksum error. The reason is that one checksum will be computed using the value “1234”, as it stores in the database table. This value will be compared with a checksum value that was computed using the value “1,234.00”, as it appears in your report. These values can’t match. This error will not be resolved by coping the SQL query into a new page. It’s emanate from the SQL query itself.

                          If you are changing the original wizard generated query in a way that modify/change/format the original column value, it will generate a checksum error. The only way to overcome these problems, assuming the built-in options can’t give you what you need, is to write your own DML processes.

                          >> A little more flexibility in the update of the query, and maybe a little advancement of the tabular form field validation capabilities, and all the standard tools could be utilized.

                          If you worked with APEX versions prior to 4.0 you are probably aware of the major improvement in Tabular Forms validations and error handling. I’m not part of the development team, but I’m pretty sure we didn’t see the last improvements in this area. As for flexibility, especially with updates, this is a tricky issue. As Marc said in his first post, a tool like APEX must be as generic as possible, and make sure that novice users (as well as experienced ones) will get as a perfect result as possible. Lost updates is a complicated issue even for advanced users, and APEX must treat it very strictly for its generated applications to work properly. The flexibility of APEX is that whenever a declarative or built-in feature can’t provide what you need, out of the box, it still allows you to do it manually. In each new version, the amount of available declarative options is increasing (In APEX 4.0, I believe they concentrated on JavaScript and AJAX options). Tabular Forms is a major feature in APEX, so I’m pretty sure it will get better and easier to use in future versions.

                          Regards,
                          Arie.

                          -------------------------------------------------------
                          ♦ Please remember to mark appropriate posts as correct/helpful. For the long run, it will benefit us all.

                          ♦ Author of Oracle Application Express 3.2 – The Essentials and More