FRM-40654: Record has been updated by another user. Re-query to see change ..

A common problem which you might observe is  

"FRM-40654: Record has been updated by another user. Re-query to see change ...”

Cause: Another user has updated this record since you performed a query and has changed at least one field in the record. Your actions have not changed the record in memory.

Action: You can update or delete this record now only if another user has restored the field values back to the way they were when you performed the query. Otherwise, you must re-query to fetch and display the new record into the form before you can update or delete it.

This is very common issue after migration or with any external interfaces, If you have taken care by TRIM function, then there is nothing to bother, else your end user will report this, which need a proper investigation.

The reason and resolutions

This error is caused because of any of the following

1. FRM-40654 caused by unnecessary foreign key fields
2. Record locked i.e. another user changed the record and saved after you queried the record
3. Some of the columns have trailing spaces, and Forms by default truncates the trailing spaces and hence the database value and form value will not match and you get the error.
4. Date field stored in database is with time stamp but form showing only Date component and hence Form value and database value will not match and hence you get the error. (you need to remove the time component in DB or show date time in Form)

Bottom line is the value in Database and value in form are not matching, so you have to check for each column and see where the difference is. I know this is going to be painful checking out every field for changes, but there are no other options left.

What you have to do is to investigate the column first and then make a update with TRIM function as

update <table name>
set <column name> = trim(<column name>);

Resolutions Example

Example

User reported the problem at suppliers site,

·         Identify which table holds the supplier data.

·         Check a column value for a VARCHAR2 column. In this case VENDOR_SITE_CODE Or Address_line1

SELECT '['||vendor_site_code||']'

FROM po_vendor_sites_all
WHERE vendor_id=<VENDOR ID>;

If the column has trailing spaces the output will looks very similar to

[SITE ]

·         Once you get the column , then use update script, with TRIM function, like

        UPDATE po_vendor_sites

       SET vendor_site_code = TRIM(vendor_site_code)

      WHERE vendor_id = <VENDOR ID>;

What if , not have "trailing space" Problem

Confirm with your DBA whether the table is having any lock or not? If there is no lock then you need to ask Oracle support.