FRM-40654 error unable to update

The form is in testing.  Only one person may be testing it when it happens they can log out I have checked and on other sessions are open because I thought for some reason something was hanging and it still will not work for the record.  We can even reboot the app server and the problem will still be there.  

This is a database problem not the app problem. It can occur when testing and something happens (form crashes ...) and record remains locked because a session is still there. It does not matter if it is one person or more testing the application there. We have had the similar situation.

But we have checked the sessions and this is not the problem. Any other ideas?

Maybe it is a problem of setting a form-level property interaction mode to non-blockig. You can try this solution from Metalink (bug 609348):

 on module level : set interaction mode  to non blocking
 on module level : isolation mode to  serializable
 on block level  : set locking mode to automatic

How do you set these properties?

I use always default settings. You can leave an interaction mode as default - blocking or try above solution from MetaLink.

There are may reason for that. Check this list:

Oracle Forms tried to lock a record that has already been changed or deleted.
The record you are viewing on the screen differs from the record stored in the
database.  
 
 
Solution 1  
==========
 
One of the common causes of FRM-40654 is a truncation problem.  This is  
usually caused by the difference between the format of a field in the form and  
a column in the database.    
   
Example  
-------  
A database column C1 has the definition NUMBER(4,2), so it contains at most  
2 decimal places; its corresponding field in the form F1 has the definition  
NUMBER of width 6.  
 
When you successively try to update the same record, Oracle Forms compares C1  
with F1 and determines they are different.  Oracle Forms "thinks" that that  
record in the database has been changed.  
 
Workaround  
----------  
Specify a format mask (e.g. 9999.99) to ensure the format of the Forms field  
adheres to the format of the corresponding database column.  
 
 
Solution 2    
==========  
   
During a commit process, Oracle Forms scans through each block and issues the  
proper DML (insert, delete, or update SQL statement) statement for each record  
modified, so data in the form is transferred to the database.  This process is  
done on a block-by-block basis, and the order is determined by the sequence  
number of a block.  
FRM-40654 occurs when a record in previous block is changed either by DML or a  
PL/SQL assignment statement in a Commit-time Oracle Forms trigger  
(e.g. Pre-Update, Post-Insert) of the current block, so that record is not in  
synch with its counterpart in the database.  
 
FRM-40654 can occur if you use a database trigger to update a record  
in a base table block.  
 
 
When you successively try to update that record, Oracle Forms first compares  
it with its counterpart in the database.  Since the Forms record and database  
record are different, Oracle Forms behaves as if the record has been changed  
by another user and issues FRM-40654.  
 
Workaround 1
------------
 
Avoid changing any record in previous blocks in Commit-time triggers.  
 
Workaround 2
------------
 
Use non-base table items for those values that are populated by  
database triggers, not the Forms application itself.  
 
 
Solution 3  
==========  
 
The ORACLE7 server CHAR datatype can cause another form of truncation which  
can result in FRM-40654.  
   
If a Forms base table block is based on a database table containing columns of  
type CHAR in ORACLE7, values in the form and the database can be out of synch  
after a commit.    
   
The CHAR datatype in ORACLE7 always pads spaces to values which do not fill  
the column.  Values of CHAR datatype in ORACLE7 are fixed length character  
strings.  In ORACLE version 6, values of CHAR datatype are variable length  
character strings and are not padded.    
 
However, Oracle Forms always strips off trailing spaces.    
 
 
Workaround
----------
 
Use Varchar2 datatype and don't use trailing blanks in conjunction with Forms.
 
 
Solution 4  
==========  
   
Oracle Forms V3.0 treats DATE and DATETIME the same. DATE and DATETIME both
have the time component, and the format mask is the only part different.    
 
In SQL*Forms V3.0, you can add the time component to the DATE column in the  
database in a Post-Insert or Post-Update trigger using an UPDATE statement.  
It does not complain about it even though the time component now differs  
between the form and the database.  
 
However, Oracle Forms checks the time component even though the data type is  
DATE, which leads to FRM-40654.  For more information, refer to bug:207876.  
 
 
Solution 5  
==========  
   
If database triggers are used, make sure they will not cause data  
inconsistency between the form and the database.  
   
(e.g.: If a block has a primary key that is set via a SEQUENCE in a database  
       INSERT TRIGGER then FRM-40654 error can occur.)
 
Workaround  
----------  
 
Change the database trigger so that if the :new.id is null or zero for  
instance, it will be initialized from sequence.NEXTVAL.  If it is not null  
then that means Oracle Forms has inserted the record with a sequence number.  
 
In Oracle Forms, create a PRE-INSERT trigger to get the sequence.NEXTVAL.  
 
This will let your Forms application retain knowledge of the new key value  
allocated from the sequence in the PRE-INSERT trigger, while still providing  
sequence #'s in the database trigger for other (non-Forms) applications.  
 
 
Solution 6  
==========
 
You populated the block with a SELECT statement (using either a cursor or a  
SELECT..INTO statement), but did not populate every database item in the block.  
 
When it tries to lock the record in the database during a commit, Oracle Forms  
compares the form record to the database record and determines they are  
different; the form record has NULL values (because the SELECT did not  
populate all database items in the block) while the database record has no  
NULL values.  Oracle Forms "thinks" that another user has changed the record  
in the database and issues FRM-40654.  
 
Example  
-------  
Your block contains 4 items.  
 
Your SELECT statement populates 3 items in the block and changes the record  
status to QUERY_STATUS.  
 
Your code resembles the following:  
   SELECT rowid, deptno, dname  
         INTO :dept.rowid, :dept.deptno, :dept.dname  
         FROM dept  
         WHERE deptno = 10;  
   SET_RECORD_PROPERTY(:SYSTEM.CURSOR_RECORD, 'dept', STATUS, QUERY_STATUS);  
 
The block has a 4th item (e.g. loc), but the SELECT statement does not  
populate it in the form.  The form record contains 3 non-null values and 1  
NULL value, but the database record contains 4 non-null values; hence,  
FRM-40654 occurs.  
 
 
Workaround 1  
------------  
 
Select the whole record from the database when you populate the block.  
 
 
Workaround 2  
------------  
 
Remove the unpopulated item from the block.  
 
 
Solution 7  
==========  
 
On the item property sheet, setting the "Query Only" to True can cause  
FRM-40654.  
 
 
Example  
-------  
 
A block is based on the dept table and the dname is set to "Query Only =  
True".  At runtime, if the operator updates the dname and saves/commits and  
then makes another change to the same record and tries to do another  
save/commit, Oracle Forms gives error:  
 
      FRM-40654: Record has been updated.  Re-query block to see change.  
 
 
Workaround  
----------  
 
Set the "Insert Allowed" and "Update Allowed" properties to False.  
 
At runtime, when the operator tries to enter a value in the item, Oracle Forms  
will give error:  
 
      FRM-40200: Field is protected against update.  
   
 
Solution Explanation  
====================  
 
The Query Only Property specifies that an item can be queried but that it  
should not be included in any INSERT or UPDATE statement that Oracle Forms  
issues for the block at runtime.  
 
If the operator changes the "Query Only" item, Oracle Forms recognizes that  
there is a difference in the item in the form against what is in the database.  
 To ensure that there are no differences, do not allow the operator to insert  
or update that item.
 
 
Solution 8    
==========  
 
Block based on table has properties DML RETURNING VALUES set to YES.  
At the backend, a procedure updates the table. On trying to update the queried
records in the form, get FRM-40654  
The DML returning values will work when an update/insert on the backend is done by a trigger on
the table, and not a procedure.
 
A database update or insert action may initiate server-side TRIGGERS that cause
alterations or additional changes in the data. In Release 6, when using an  
Oracle8 database server, Forms uses the DML Returning clause to immediately  
bring back any such changes. When this property is set to Yes, Forms will  
automatically update the client-side version of the data, and the user will not
need to re-query the database to obtain the changed values.  
 
 
Workaround
----------
 
Move the code in the procedure to a backend trigger, like BEFORE UPDATE etc.
 
 
Solution 9  
==========  
 
FRM-40654 is raised when trying to use a view as Query Data Source and table  
as DML Data Target.
 
 
Technical background of the problem:
How does Forms process record locks ?
-------------------------------------
 
When the user tries the first to change an item value of a record  
fetched from the database Forms performs these steps:
 
- Issueing a SELECT ... FOR UPDATE for all database items in the
  block that are not Query Only. In our case this is the statement:
  SELECT ROWID,DEPTNO,DNAME,LOC FROM dept WHERE DEPTNO=:1
     FOR UPDATE OF DEPTNO NOWAIT
 
- If the record is already locked, the database raises a
  'ORA-00054: resource busy and aquire with NOWAIT specified'.
  Forms raises a
  'FRM-40501: ORACLE error: unable to reserve record for update
   or delete'
 
- If the lock succeeds Forms compares the record fetched by the
  SELECT ... FOR UPDATE with the record in the form.  
  If the item values don't match Forms raises a
  'FRM-40654: Record has been updated by another user. Re-query
   block to see change'
 
 
Workaround
----------
 
Set the item property Query Only to true for item DNAME. Now this  
is our SELECT ... FOR UPDATE statement:
SELECT ROWID,DEPTNO,LOC FROM dept WHERE DEPTNO=:1
   FOR UPDATE OF DEPTNO NOWAIT
 
 
Solution 10  
===========
 
FRM-40654 Connecting to ORACLE LITE at query time
 
 
Workaround
----------
 
Make sure the primary key is set for all tables. Use the Alter Table
command to set the primary key.
 
Because Personal Oracle Lite is different from Oracle Database, you  
must specify a primary key for all tables used by Forms.
 
The DATE datatype in Oracle Lite does not include a time component, but it does
in Oracle Server. Thus, the FRM-40654 error occurred because the form and  
database were out of sync: date fields in the form had a time component, but  
the corresponding database fields did not.  When I changed the datatype of the  
DATE fields to TIMESTAMP in Oracle Lite, the error disappeared.  
 
To include a time component, you must use the TIMESTAMP datatype in Oracle Lite.
The Oracle Lite SQL Language Help states that TIMESTAMP in Oracle Lite is the  
equivalent of DATE in Oracle Server.  
 
Furthermore, in Oracle Lite, the SQL*Plus DESCRIBE command displays a TIMESTAMP  
column as DATE. Thus, the only way to be absolutely sure that the column is  
TIMESTAMP is to drop and re-create it explicitly as TIMESTAMP.  
 
The "Developing Applications with Oracle Lite" manual contains in Chapter 1 a  
section titled "Differences Between Oracle Lite and Oracle Databases." However,  
this different behavior of DATE datatypes is not mentioned.
 
 
Solution 11  
===========
 
FRM-40654 when updating a record after dynamically changing it's base table
 
You need to create a form that accesses different database tables
depending on certain criteria. The tables' DDL is the same, they just
have different names.
 
So you create a form and set the "Query Data Source Name" property
to be one of the 2 tables
 
You are finding that when you run the form and try to insert or delete
a record, you have no problems. But when you attempt to update an existing
record you are getting the following error:
 
FRM-40654 .
 
 
Workaround
----------
 
You need to set the DML_DATA_TARGET_NAME property of your block as well
as the QUERY_DATA_SOURCE_NAME in the when-new-form-instance trigger,
in order to be able to update existing records.

The most common two causes of this are:

1. a database trigger that adjust the values of records being inserted
2. a Forms trigger (or program unit called from a Forms trigger) in a different block of the Form, that updates the record in the block you are changing.

If neither of these are true, then check all of the possibilities in the (very thorough) list from schwertner.  

use forms_ddl('commit') to commit the record. And always refersh the bolck after comitting.

I disagree with the suggestion to always refresh the block after committing.  That is a work-around that should eliminate the problem, but it adds a performance penalty in the client or application server, plus extra network traffic, plus extra database overhead.

I think the most important thing is that you can close out and bring the same record up three days later and you are unable to make changes to the record.  That is what is so weird.  Is there anything on the database level that could cause this problem? I have gotten rid of all database triggers and I still have the problem.

I also forgot one more thing. It will not even let you type information in the fields of these records.

At first I will try to use in a Post-Query trigger (at the end):

SET_RECORD_PROPERTY(:system.trigger_record, :system.trigger_block,STATUS, QUERY_STATUS);