3 Replies Latest reply on Jan 27, 2014 11:52 PM by Elena_Iv

    "Current tabular form data is too old" on apex 4.2.3

    svms

      "Current tabular form data is too old; the source data has been modified" - apex 4.2

      "report error: User-Defined Exception" - apex 4.1.1

      "Current version of data in database has changed since user initiated update process" - earlier

       

      One of the situations is that this error occurs with tabular form on query with joins and when validation is raised.

      It was always a mystery to me why it is so and just recently I was able to understand the behavior (At least I believe so)

       

      Here is a test case:

       

      create table test1
      as
      select level id, 10-level fk
      from dual
      connect by level < 10
      
      alter table TEST1 modify fk not null;
      
      alter table TEST1
        add constraint TEST1_pk primary key (ID);
      
      create table test11
      as
      select level id, 5-level val
      from dual
      connect by level <= 10
      
      alter table TEST11
        add constraint TEST11_pk primary key (ID);
      
      alter table TEST1
        add constraint TEST1_FK foreign key (FK)
        references test11 (ID);
      
      
      

       

      1. I created a new application and a new page with tabular form on the table test1, using the Wizard, and then I removed all of the default sort options from report attributes.

      2. I changed Region Source  to

       

      select t1."ROWID"
           , t1."ID"
           , t1."FK"
           , t11.val
      from test1 t1
         , test11 t11
      where t1.fk = t11.id
      
      

       

      (i.e. I added a new read-only column "VAL" by joining with another table)

       

      4. There is one more condition for this error to occur.

      A query plan must contain either merge join or hash join

       

      explain plan for select t1."ROWID" , t1."ID" , t1."FK" , t11.val from test1 t1 , test11 t11 where t1.fk = t11.id
      Plan hash value: 80085176
      
      ------------------------------------------------------------------------------------------
      | Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
      ------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT             |           |     9 |   576 |     6  (17)| 00:00:01 |
      |   1 |  MERGE JOIN                  |           |     9 |   576 |     6  (17)| 00:00:01 |
      |   2 |   TABLE ACCESS BY INDEX ROWID| TEST11    |    10 |   260 |     2   (0)| 00:00:01 |
      |   3 |    INDEX FULL SCAN           | TEST11_PK |    10 |       |     1   (0)| 00:00:01 |
      |*  4 |   SORT JOIN                  |           |     9 |   342 |     4  (25)| 00:00:01 |
      |   5 |    TABLE ACCESS STORAGE FULL | TEST1     |     9 |   342 |     3   (0)| 00:00:01 |
      ------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         4 - access("T1"."FK"="T11"."ID")
             filter("T1"."FK"="T11"."ID")
      
      

       

      and at the same time, the query plan must be changed to nested loops after adding inlist operator "and t1."ROWID" in (:bind1,:bind2,:bind3,:bind4,:bind5,:bind6,:bind7,:bind8,:bind9)"

      (most probably such plans will be generated by default)

       

      explain plan for select t1."ROWID" , t1."ID" , t1."FK" , t11.val from test1 t1 , test11 t11 where t1.fk = t11.id and t1."ROWID" in (:bind1,:bind2,:bind3,:bind4,:bind5,:bind6,:bind7,:bind8,:bind9)
      Plan hash value: 1888669757
      
      -------------------------------------------------------------------------------------------
      | Id  | Operation                     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
      -------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT              |           |     1 |    64 |     2   (0)| 00:00:01 |
      |   1 |  NESTED LOOPS                 |           |       |       |            |          |
      |   2 |   NESTED LOOPS                |           |     1 |    64 |     2   (0)| 00:00:01 |
      |   3 |    INLIST ITERATOR            |           |       |       |            |          |
      |   4 |     TABLE ACCESS BY USER ROWID| TEST1     |     1 |    38 |     1   (0)| 00:00:01 |
      |*  5 |    INDEX UNIQUE SCAN          | TEST11_PK |     1 |       |     0   (0)| 00:00:01 |
      |   6 |   TABLE ACCESS BY INDEX ROWID | TEST11    |     1 |    26 |     1   (0)| 00:00:01 |
      -------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         5 - access("T1"."FK"="T11"."ID")
      
      Note
      -----
         - dynamic sampling used for this statement (level=2)
      
      

       

      5. Now running a page and raising FK not null standard validation (e.g. removing any column value from fk column and pressing a Submit button)

      results to this error "Current tabular form data is too old; the source data has been modified. Click here to discard your changes and reload the data from the database."

      Example http://apex.oracle.com/pls/apex/f?p=73108:1

       

      The difference in query plans is that sql query with NESTED LOOPS returns different row order than with MERGE/HASH JOIN.

      So, after adding inlist operator with relevant bind variables the result set will be the same but in a different order.

       

      This error means that row checksum, calculated during first page rendering <> row checksum, calculated during second page rendering that appears after firing validation.

       

      As I can see in v$sql in the process of first page rendering apex engine uses report query without adding any additional operators while in the process of second page rendering that is after meeting validation it's using inlist operator (it's probably for returning proper rows from pagination or may be for some other reasons). Thus row order is changing. My guess is that apex engine mistook new row order with the old one, so it probably compares row checksums from different rows. Obviously they are not the same, so we are getting "Current tabular form data is too old" message here (this is my guess).

       

      By understanding the issue it's not hard to find workaround

      e.g. using deterministic sort (order by t1.rowid for this example)

       

      I'm just wondering why it's not fixed for so long.

       

      Regards,

      Igor

       

      -

      Sorry for my english it's not my native language

        • 1. Re: "Current tabular form data is too old" on apex 4.2.3
          Elena_Iv

          Hi Igor,

           

           

          Thanks for the investigation. It gives some ideas of how to solve the problem. However, though my tabular form queries only ONE table (no joins), I still have this error. It seems tabular forms functionality causes a lot of problems both during development and applications upgrade.

           

           

          There was a discussion of this issue in this thread "Current tabular form data is too old" on apex 4.2 . Unfortunately, I don't have access to Metalink to check out the bug escalated for this error (# 15851176).

           

           

          Does anybody have any ideas of how to fix this problem? Gurus, join please, too many people have been tackling the problem since 2010.

           

           

          Thank you,

          Elena

          • 2. Re: "Current tabular form data is too old" on apex 4.2.3
            svms

            Hi Elena

             

            Yes, I agree with you there are plenty of situations when you get this error.

            I investigated one of these situations only. It's important because there is no patch set to fix it.

             

            4.2.0 is a bit raw release.

            With your situation I'd suggest to install the newest version of apex 4.2.4 and import your applications and database into that new instance with new apex.

            (there is no upgrade options from 4.2.0 to 4.2.x if you don't have access to MOS)

             

            You can also try to eliminate existing validations on page and replace them with page processes

            (I didn't try it myself but there are situations when validations can cause the problems).

             

            Regards,

            Igor

            • 3. Re: "Current tabular form data is too old" on apex 4.2.3
              Elena_Iv

              Hi Igor,

               

              As I haven't found any clear solution, I had to replace the built-in apex tab forms with the custom ones, which one can create using APEX_ITEM functions. The validations have also been changed accordingly. Only after this changes I got rid of the error.

               

              Thanks for your suggestions once again.

               

              Regards,

              Elena