ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

If you get an ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc. from your DML process when you submit the changes in an Oracle APEX application, then this is because Oracle APEX tries to lock the effected record before it updates/deletes it. This behavior was introduced in Oracle APEX 3.0, but with the side effect that complex updateable views with an instead of trigger get the above error.

BTW, why does Oracle not have a “locking” branch in an instead of trigger???

Back to the topic, what can we do?

In Oracle APEX 3.0.1.00.07/08 a new switch was added to disable that behavior, it’s documented in the readme for the patch, but not in the online help (bug has been filed). The switch is called FSP_DML_LOCK_ROW and is a substitution value or an application/page item. If you set the value to FALSE, the locking will not be done by the DML process.

But is it a good idea to disable locking for the entire application?

I think No! It should only be done for those pages where it’s necessary.

  1. Create an application item named FSP_DML_LOCK_ROW
  2. Create an application computation with the following settings
    • Sequence: 0
    • Computation Point: After Submit
    • Computation Type: Static Assignment
    • Computation: TRUE
  3. Create a computation on the pages which have the error
    • Sequence: 10
    • Computation Point: After Submit
    • Computation Type: Static Assignment
    • Computation: FALSE

BTW, there is another switch which effects the locking, it’s called APEX_DML_LOCK_WAIT_TIME. For details have a look at the documentation.