Checked relevance 13-January-2009
Problem Description  
=================== 
 
When you try to update a record in Runform, the following error occurs: 
FRM-40654:  Record has been updated. Re-query block  to see change. 
 
 
Problem Explanation 
=================== 
 
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 nly = 
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.


References
==========

Note:444582.1 FRM-40654 If Block Property Dml Returning Values Set To Yes & 
                Item Size > 1333