+1- Is this type of locking is suitable to this situation ?+It is ok as long as you commit right away.
+2- How can I Handle the exception if that row was locked by another user ? by a message with 2 options (wait or exit the trigger).+I used a loop with a 1-second delay, and failing after 5 seconds. I suppose you could instead of waiting and retrying, you could issue an alert within the loop asking the user whether to retry or exit the commit.
+3- How can I lock the row for a limited time?+You can't. Oracle doesn't provide this capability. Just do the lock and update immediately before a commit. Then, either the row will be updated and committed, or it the lock will fail so there is no lock in effect.
Mostafa Abolaynain wrote:I missed the significance of the "wait 5" in your first post. Sorry. The Oracle Forms developer has always been a step or two behind the Oracle database level with new features. Not sure why it needs to inspect and validate SQL calls within the Developer -- seems like it should just throw the code at the database and let the database validate. But that is what it does -- PRE-validate your SQL.
I agree with you that explicit locking is better and more safe, as a told you in the first post I tried to use the "for update" clause with "wait n"
but forms 10g cosidered it as syntax error.