4 Replies Latest reply: Sep 18, 2013 10:20 AM by 988166 RSS

    Dirty rows in tabular form

    marksminkey
      Hi All,

      I've written a custom PL/SQL process which iterates over apex_application items to update the records in a tabular form:

      BEGIN

      FOR i in 1 .. APEX_APPLICATION.G_F01.COUNT
      LOOP
      -- do update
      UPDATE <table>
      set column1 = APEX_APPLICATION.G_F01(i);
      column 2 = APEX_APPLICATION.G_F02(i);
      ...
      where key_column = APEX_APPLICATION.G_F03(i);
      END LOOP;

      END


      Question: I would only like to run the update statement only if the any of the data in the row has changed.

      e.g.

      FOR ...
      LOOP
      IF <rowdata has changed>
      THEN
      UPDATE....
      END IF;

      END LOOP;


      Any ideas on how I might be able to do this?


      Thanks,
      Mark
        • 1. Re: Dirty rows in tabular form
          jariola
          Hi,


          This is not best solution, but might get you some ideas
          https://apex.oracle.com/pls/apex/f?p=40323:2


          Regards,
          Jari
          • 2. Re: Dirty rows in tabular form
            vee
            Mark,
            You can handle that in the normal SQL query itself without any changes in apex side

            <li>Method 1
            BEGIN
              FOR i in 1 .. APEX_APPLICATION.G_F01.COUNT
              LOOP
              -- do update
              UPDATE <table>
              SET column1 = APEX_APPLICATION.G_F01(i);
                  column2 = APEX_APPLICATION.G_F02(i);
              ...
              WHERE key_column = APEX_APPLICATION.G_F03(i)
              --Conditions to ensure that records get updated only when any of the updateable columns are modified
              AND   ( NVL(column1,'') != NVL(APEX_APPLICATION.G_F01(i),'NULL')
                   OR NVL(column2,'') != NVL(APEX_APPLICATION.G_F02(i),'NULL')
                     ...
                    );
              END LOOP;
            END
            Method 2
            <li>You can also compare checksums of the DB record(just before updation) and the new data using only the updateable columns and update the record only when there is a mismatch (hope you can understand the difference between this approach and the commonly used checksum approach for lost-update detection).
            This method also does not need any changes in apex side and involves only the original data and the updated data.

            The trouble with JS based approaches is that its hard to identify data changes when users reverts back a change to its original value.If you don't have duplicate columns to compare each of the modifiable fields to their original value anytime a change event happens. Depending on what you consider as dirty column, this apporach would also work for you.
            • 3. Re: Dirty rows in tabular form
              Denes Kubicek
              This example shows how to use checksum for that kind of check and update only changed rows:

              http://apex.oracle.com/pls/otn/f?p=31517:170

              Denes Kubicek
              -------------------------------------------------------------------
              http://deneskubicek.blogspot.com/
              http://www.opal-consulting.de/training
              http://apex.oracle.com/pls/otn/f?p=31517:1
              http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
              -------------------------------------------------------------------
              • 4. Re: Dirty rows in tabular form
                988166

                Hi Can you explain with an example the method 2 you suggested?