FRM-40654 “Record has been updated by another user”
There are several reasons you might get this error in Oracle Forms, e.g. another user has modified the record before you saved it (as the error message suggests), or a table trigger has modified the record and your form’s DML Returning Value is set to No.
Another cause to chalk up is what one of my colleagues got today. He made a form with a simple table-based block, he would insert a record and save successfully, then try to modify it and consistently got FRM-40654 when he tried to save. No other user was trying to update the row, and there were no triggers on the table.
The answer? The table is an index-organised table, but the block on the form had Key Mode set to Automatic. I suspect the form is comparing the ROWID of the before-and-after change, but because the table is index-organised the ROWID is not necessarily constant. Whatever the reason, changing the Key Mode to Updateable or Non-Updateable solves the problem.
About Jeffrey Kemp
Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
Ammar
22 July 2010 - 10:28 pm
To Solve the issue you have to do the following
1- Add you updated quer in the form trigger under KEY_COMMIT
2- Before end in the KEY_COMMIT set ” Commit_Form; ”
This it will allowed you to save the records without any defectes or error message.
Ammar
Jeffrey Kemp
22 July 2010 - 11:46 pm
Hi Ammar,
When FRM-40654 is raised, it’s important to first understand why it is being raised. There are a number of reasons why it occurs (as I enumerated in my post), and they require different solutions.
It is during commit processing that the error is raised; so I don’t see how your KEY-COMMIT trigger is supposed to solve this issue. Perhaps you could expand on what problem your solution solves, and how?
Thanks for your input,
Jeff
Steve
3 June 2011 - 5:09 am
I have set the key mode to updateable and DML Returning Value is set to YES, now. I am still getting the FRM – 40654 error. What do I need to do?
Thank you,
Steve
Sami
29 June 2011 - 10:55 pm
If you committed the data, you could do an execute_query after your commit.
Sami
Jeffrey Kemp
30 June 2011 - 6:46 am
Hi Steve,
Have you determined what the cause of the FRM-40654 error is? For example, are you certain no other user or session is modifying the same record? If not, then you need to look for a reason why the form’s version of the row data is getting out-of-sync with what’s in the database when it tries to perform the update. Perhaps there’s some code in your form doing something unusual.
Jeff
minnie
28 November 2012 - 3:13 pm
Hi,
I am new in oracle forms. I have the same problem. I create a block. i set the “number of records displayed” = 7.I also set the key mode to updateable and DML Returning Value is set to YES. I am trying to modify one of the column in my block. The first row allows me to edit the said column. However, the succeeding row does not allow me to modify and i am getting the same error above (FRM – 40654). I hope somebody could help me. Thanks!
Jeffrey Kemp
29 November 2012 - 8:26 pm
Hi minnie, this blog post was about a block that was based on an Index-Organized table. Is your block based on an IOT too? Otherwise, changing your key mode and other block properties just because of this article without considering their effects in your instance is not advisable. For example, normally I’d advise setting key mode to Automatic.
With regards to your form, things I’d look at are triggers (perhaps a trigger is firing on the first record, but not the second, or vice versa, changing the data in the block in some way), and I’d look at the data source (is it an ordinary table, a view, or something else) and consider its characteristics.
I’d also double-triple-check that there isn’t another session running on your database which is trying to update the same records. For example, does your form run any PL/SQL code that touches the record? Even worse, is there some code that runs in an autonomous transaction that might be trying to update the record?
These are just some ideas, you need to narrow down the exact cause of your error – i.e. don’t just guess and change settings in the hope that they will magically solve your problem.
Good luck!
vpa
20 April 2018 - 5:34 pm
Do the two things
1- Make base block item primary key as per the Table (one or more than that as per the table schema)
2-In Base block property make “Enforce Primary Key ” to Yes ,
Problem will be resolved.
Jeffrey Kemp
20 April 2018 - 7:27 pm
Hi vpa – thanks for stopping by, but I’m not quite sure what problem you are referring to here.
vpa
20 April 2018 - 9:27 pm
it is regarding FRM-40654 error in Oracle Forms. I was facing this problem take some hint from your posts and and solved it.
libra_87
14 May 2018 - 2:20 pm
i tried this, and Issu resolved. thanks @vpa
Do the two things
1- Make base block item primary key as per the Table (one or more than that as per the table schema)
2-In Base block property make “Enforce Primary Key ” to Yes ,
Problem will be resolved.