uTab
asked on
FRM-40654 error unable to update
I have a form that I am able to update a record once but I am unable to update it a second time (error FRM_40654 Record has been updated by another user) Re-query no other users are int he system. I ahve saved, exited and requeried the same record and I am still unable to update it. Does anyone know what would cause this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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
on module level : set interaction mode to non blocking
on module level : isolation mode to serializable
on block level : set locking mode to automatic
ASKER
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(:SYSTE
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.
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(:SYSTE
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.
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.
ASKER
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.
ASKER
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(:syste
SET_RECORD_PROPERTY(:syste
ASKER
Tried but that did not help, thanks
ASKER