This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Dec 9, 2012 3:00 AM by Arie Geller RSS

Tabular form - Insert vs update

VANJ Journeyer
Currently Being Moderated
Apex 4.2/Oracle 11gR1

I have some tabular forms which ADD and UPDATE rows but instead of using the Add button and presenting an empty row, it basically makes-up rows that don't exist in the underlying table by using an outer-join in the query. Basically the idea is to default as much information on the row as possible and when the user fills in the editable columns, the built-in MRU process (as I understand it), finds the PK column blank and proceeds to INSERT the row instead of UPDATE it. The table has a row-level trigger that fills in the PK from a sequence.

All this works swimmingly well on Apex 4.0.2. But after the upgrade to 4.2, the MRU throws the usual scary error about Current version of data in database has changed since user initiated update process. current row version identifier = "xxx" application row version identifier = "yyy"

Any idea how to make this tabular form trick work in 4.2 without major surgery to the page?

Thanks
  • 1. Re: Tabular form - Insert vs update
    VANJ Journeyer
    Currently Being Moderated
    Christian, Patrick - Any ideas?

    I see that the create tabular form wizard asks about what DML operations are to be allowed on the table and creates processes/buttons accordingly. But after the page is created, I don't see a way to change this. Is that what's going on here? The page thinks it is in "update mode" and doesn't like it when it is tricked into doing an insert? Is there a way to change this without re-creating the page?

    Thanks
  • 2. Re: Tabular form - Insert vs update
    Arie Geller Guru
    Currently Being Moderated
    Hello Vikas,

    >> the built-in MRU process (as I understand it), finds the PK column blank and proceeds to INSERT the row instead of UPDATE it.

    I’m not sure this is how it works. I believe the MRU process rely on the value of the fcud (hidden) column to determine which DML action to take. On candidate rows for Insert, this column should be set to C. Can you check the value of this column in your new row(s)?

    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
  • 3. Re: Tabular form - Insert vs update
    VANJ Journeyer
    Currently Being Moderated
    Can you check the value of this column in your new row(s)?
    Arie - I will check that tomorrow but be that as it may it just re-states the question. My hypothesis was that the engine "knows" what DML action to take based on the existence of a value in the PK column. Even if this were to be determined by the fcud hidden element, my question really is how to control and change this behaviour as per my needs. Also, note that this form was working fine in 4.0.2 as I described in my initial post so 4.2 would appear to have non backward-compatible change that broke this. Again, the tabular form create wizard asks us what DML operations are permitted. How, if at all, does the answer to this question change the page rendering & processing? If it does, can it be changed after the page is created.

    Thanks
  • 4. Re: Tabular form - Insert vs update
    Arie Geller Guru
    Currently Being Moderated
    Hello Vikas,

    >> Also, note that this form was working fine in 4.0.2 as I described in my initial post so 4.2 would appear to have non backward-compatible change that broke this.

    There have been some major changes in the behavior of Tabular Forms between 4.0.x and 4.1-4.2. For example, the FCUD column was introduced in APEX 4.0 but became fully functional only with 4.1. The APEX engine relies on this column to determine if the row needs to be Created, Updated or Deleted. Unfortunately, this functionality was designed for internal use only, i.e., it is not exposed through public APIs, so it can be used only with wizard generated Tabular Forms.

    For an existing row, the FCUD value is set to U. A new (empty) row is added to the Tabular Form with the value of the FCUD set to D, and if you make any change to an updatable field, the FCUD is changed to C. This tells the APEX engine to insert the row and not ignore it (as an empty row).

    If I correctly understand the technique you are using for generating new rows, these rows are considered by the APEX engine as existing rows - they are JOINed to all the other rows that were fetched from the database – and as such, their FCUD value is set to U. With 4.0.2 it probably didn’t have any effect on the MRU process, but this is not the case with 4.2. As you are not using a “regular” add row technique, it is possible that upgrading to 4.2 broke backward compatibility (because your technique doesn’t change the FCUD value for the new rows).

    >> How, if at all, does the answer to this question change the page rendering & processing?

    The APEX engine only generates code to create buttons and DML processes that correspond with the DML options you chose while creating the Tabular Form. If, for example, you didn’t choose the option of Insert, The Add Row button will not be rendered on your page. If you chose not to include the Delete option, the ApplyMRD process will not be added to your page.

    >> If it does, can it be changed after the page is created

    Yes, it can (as long as we are dealing with a wizard generated Tabular Form).

    First, you should define an appropriate button to be associated with the requested DML action. Then, on the Processing branch of Page Processing you can Create Process and select your Tabular Form (currently, only one option is available). Now you can choose the option of Data Manipulation and define, for example, an Add rows to tabular form process, which should be conditioned by the associated button.

    >> Even if this were to be determined by the fcud hidden element, my question really is how to control and change this behaviour as per my needs.

    Well, I didn’t test it, but if you can identify your new row(s), based on a specific value (or lack of it), you can define a process, which should precede the DML processes, in which you’ll change the value of the pseudo column APEX$ROW_STATUS to C. Bear in mind that this process should distinguish between new but “empty” rows, and the ones that need to be inserted into the table.

    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
  • 5. Re: Tabular form - Insert vs update
    VANJ Journeyer
    Currently Being Moderated
    Just to put some context, the original tabular form query created by the wizard was of the form
    Select Rec_id,uk,attr from data
    The DML options chosen were Insert and Update

    After the page was created, the query was changed to
    Select
     rec_id,
    Nvl(d.uk,def.uk) uk,
    D.attr
    From data d, defaults def
    Where def.uk = d.uk(+)
    And the Add Row button was removed.

    The intent was to have the form query show both existing and new rows without an explicit Add Row button. If the user enters a value in the attr column, the MRU processs in 4.0.2 Inserted a new row. If no value is entered, the row is ignored. In 4.2, the MRU protests at being tricked!

    I am not sure I understand how to implement your suggestion to initialize the row status pseudo column only for the "new" rows in my situation. Mind elaborating?

    As always, thanks for the patient explanation. You should write a book about APEX. Oh wait, you already did :-)
  • 6. Re: Tabular form - Insert vs update
    Arie Geller Guru
    Currently Being Moderated
    Hello Vikas,

    >> If the user enters a value in the attr column, the MRU processs in 4.0.2 Inserted a new row. If no value is entered, the row is ignored.

    I’m assuming that rec_id is the differentiating column between existing rows (not null) and new ones (null).

    You should create a PL/SQL anonymous block process with the lowest sequence, and select the Tabular Form (without this selection it will not work properly). The process should be conditioned by Value of Item / Column in Expression 1 Is NULL, and expression 1 is, of course, rec_id.

    You should set the Execution Scope to All Submitted Rows. This field can only be set after the process was created (it is not part of the Create wizard). For Tabular Forms, the Execute Condition should be For Each Row.

    The PL/SQL code should look something like:
    if :attr is null then       -- new but empty row
      :APEX$ROW_STATUS := 'D';
    else                        -- new row to be inserted
      :APEX$ROW_STATUS := 'C';
    end if;
    The condition of this process will be evaluated against All Submitted Rows, but the process code will be executed only for rows without a rec_id. For these rows, the APEX$ROW_STATUS value will be determined according to the user input in the attr (updatable) column.

    I hope it makes some sense to you.

    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
  • 7. Re: Tabular form - Insert vs update
    VANJ Journeyer
    Currently Being Moderated
    It makes perfect sense and I was hoping it would work but it doesn't. I get the same error. I ran the page in debug mode to make sure the set row status process is executing before the MRU and it does execute for all the rows shown on the page but then the MRU process raises the same error.

    Looks like APEX$ROW_STATUS is meant to be a read-only variable, we cannot modify its value.

    ...Validation "Valid date" - Type: ITEM_IS_DATE
    ......Skip for row 1 because row hasn't changed
    ......Skip for row 3 because row hasn't changed -- Row 2 is where the ATTR column was filled in
    ......Skip for row 4 because row hasn't changed
    ......Skip for row 5 because row hasn't changed
    Branch point: Before Processing
    Processes - point: AFTER_SUBMIT
    ...Process "Set row status" - Type: PLSQL
    ......Process row 1
    ...Execute Statement: begin IF :ATTR IS NULL THEN       -- new but empty row
        :APEX$ROW_STATUS := 'D';
    ELSE                        -- new row to be inserted
        :APEX$ROW_STATUS := 'C';
    END IF;
    end;
    ......Process row 2
    ...Execute Statement: begin IF :ATTR IS NULL THEN       -- new but empty row
        :APEX$ROW_STATUS := 'D';
    ELSE                        -- new row to be inserted
        :APEX$ROW_STATUS := 'C';
    END IF;
    end;
    ......Process row 3
    ...Execute Statement: begin IF :ATTR IS NULL THEN       -- new but empty row
        :APEX$ROW_STATUS := 'D';
    ELSE                        -- new row to be inserted
        :APEX$ROW_STATUS := 'C';
    END IF;
    end;
    ......Process row 4
    ...Execute Statement: begin IF :ATTR IS NULL THEN       -- new but empty row
        :APEX$ROW_STATUS := 'D';
    ELSE                        -- new row to be inserted
        :APEX$ROW_STATUS := 'C';
    END IF;
    end;
    ......Process row 5
    ...Execute Statement: begin IF :ATTR IS NULL THEN       -- new but empty row
        :APEX$ROW_STATUS := 'D';
    ELSE                        -- new row to be inserted
        :APEX$ROW_STATUS := 'C';
    END IF;
    end;
    ...Process "ApplyMRU" - Type: MULTI_ROW_UPDATE
    Add error onto error stack
    ...Error data:
    ......message: Current version of data in database has changed since user initiated update process. current row version identifier = "5FCC97E25322B5A948EAD39752458F76" application row version identifier = "4099F9450C724746E18D1618C8523A6D"
  • 8. Re: Tabular form - Insert vs update
    Marc Sewtz Pro
    Currently Being Moderated
    Hi Vikas,

    Just want to confirm that Arie's observations are correct, we started to make use of the hidden FCUD arrays in 4.1, and this is now used in addition the the state of the PK column to determine which records to update, insert or delete. So while your technique to determine what rows are now - by checking if the PK value(s) are NULL - would have worked in 4.0.2, this is no longer sufficient. For all existing rows, the FCUD column value us 'U'. When adding new rows using the add row button, we create a copy of an empty row. This empty row does contain default values if defined - so perhaps those default in the column attributes would be sufficient for what you need to do? In any case though, those rows are appended to the tabular form, and the FCUD value is set to 'D' initially. Once you touch one of these new rows, we change the value to 'C'. So then, once submitted, we do updates for the rows set to 'U', we ignore any new rows set to 'D' and attempt an insert for those rows set to 'C'. We also still check of PK not NULL. So potentially you could just use some JavaScript to set the FCUD values to 'C' for those pseudo-new-rows you're including in your query, make sure the PK values remain NULL as before, and then see if that goes through. I have not tested this, but it's worth a shot. If you could try this out on apex.oracle.com and set me the credentials, I can take a closer look.

    Regards,
    Marc
  • 9. Re: Tabular form - Insert vs update
    VANJ Journeyer
    Currently Being Moderated
    Marc - Thanks for jumping in. Since Arie's observations are correct, his recommendation to set APEX$ROW_STATUS prior to the MRU seem sound. Any idea why it is not working, as my debug snippet shows? i.e. doesn't the server side process to set apex$row_status equivalent to your suggestion of altering the FCUD to C using client-side scripting?
  • 10. Re: Tabular form - Insert vs update
    Marc Sewtz Pro
    Currently Being Moderated
    I'll have to do some testing to see what happens when - I'll let you know what I can find out
  • 11. Re: Tabular form - Insert vs update
    Arie Geller Guru
    Currently Being Moderated
    Hello Vikas and Marc,

    >> Looks like APEX$ROW_STATUS is meant to be a read-only variable, we cannot modify its value.

    Unlike Marc, I don’t have access to the source code, but I was able to reproduce a similar scenario on my application, and deduced the following, which Marc can confirm:

    We can change the value of APEX$ROW_STATUS, however, changing this value doesn’t change the corresponding g_fcud array cell. Hence, we need to do it directly, but we still don’t have to fully resort to the old arrays' manipulation.

    According to my test, something like the following should work:
    if :attr is null then       -- new but empty row
      apex_application.g_fcud(:APEX$ROW_NUM) := 'D';
    else                        -- new row to be inserted
      apex_application.g_fcud(:APEX$ROW_NUM) := 'C';
    end if;
    @Vikas – From the debug script it seems that this process is running for rows 1-5. Are all these rows “new”? If not, you should check your process condition.

    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
  • 12. Re: Tabular form - Insert vs update
    VANJ Journeyer
    Currently Being Moderated
    Arie - You are a genius! This did the trick. Yes, the value of a page item is used in a query to determine whether the form shows all updatable rows or all "new" rows using something like
    AND (:P14_ACTION = 'Update' OR pk is null)
    AND (:P14_ACTION = 'New' OR pk is not null)
    Marc - Is this a bug? Or is apex$row_status meant to used as read-only and Arie's method g_fcud(apex$row_num) to change the value of row_status?

    Thanks
  • 13. Re: Tabular form - Insert vs update
    Marc Sewtz Pro
    Currently Being Moderated
    Thanks again Arie for providing the solution, in this case to set the FCUD value via PL/SQL instead of JavaScript on the client. So yes, you can also set the value of apex_application.g_fcud for each row. apex_application.g_fcud is an array, so when you set this for the rows that are "new", then that information will be available in our multi row update processes.

    APEX$ROW_STATUS on the other hand is designed to be used in a tabular form PL/SQL page process. These type of processes are executed for each row (depending on how you set the execution scope). So APEX$ROW_STATUS is available while you are performing some PL/SQL on the row currently processes. Once processing moves on to the next row, APEX$ROW_STATUS returns the value for that row, i.e. APEX$ROW_STATUS is not an array.

    The MRU process that raises the "Current version of data in database has changed since user initiated update process" is different in that it processes the entire tabular form record set. And that happens after your tabular form PL/SQL process above is already done looping through all rows. So at that point, you can't access APEX$ROW_STATUS and will have to use apex_application.g_fcud. Or to put this another way, you can catch all your pseudo-new-rows and process them in your own tabular form PL/SQL process before ever hitting the MRU process, but you need to make sure while doing that, that you set apex_application.g_fcud to 'C' so the row later gets excluded from the MRU processing.

    Hope this makes sense? Also, neither APEX$ROW_STATUS or apex_application.g_fcud are really meant to be manipulated, but if you do want to exclude rows from being included in the update, then the apex_application.g_fcud array is the way to do this.

    Regards,
    Marc
  • 14. Re: Tabular form - Insert vs update
    Arie Geller Guru
    Currently Being Moderated
    Hi Marc and Vikas,

    Marc - thanks for your insight regarding the new features of the Tabular Form. I can divulge that most of my observations rely on information I received from you in previous occasions.

    Unlike many, I’m a big fan of JavaScript, and not afraid to use it directly (for some reason I don’t feel comfortable with jQury, although I fully appreciate its strength), but in this case I planned to save it for last. I really like and appreciate the new capabilities of (almost declaratively) handling the Tabular Form data per row, and looking at the final solution, I believe it’s much simpler than client-side JavaScript manipulations.

    Vikas – I’m glad things worked out for you without the need of major surgery to the page. I also appreciate your compliments throughout the thread :) so thanks.

    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
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points