forms question

Posted on 1998-05-25
Last Modified: 2010-05-18
I'm working with 2 blocks on 2 separate screens (pages). Screen 2 is a pop-up that comes up when a certain field on screen 1 is crossed. The base table for each screen is different. When screen 2 pops up, the user enters data in 2 columns (job function & billable hours). When the user hits the commit key I jump back to the first screen, post the
entries from the user & sum them with a select statement. The result goes in the field I jumped on screen 1.

Here's the problem: if I use a query to bring up screen 1 & 2 and then modify screen 2 hours, when I go back to screen 1 I get the following error message:

FRM-40654: record changed by another user. requery to see change.

Why am I getting this error message? There are no other users.

Martin Meadows
Question by:mmeadows

Expert Comment

Comment Utility
Are you calling FORMS_DDL anwhere in the process of going back and forth?

Accepted Solution

poncejua earned 200 total points
Comment Utility
One of the common causes of FRM-40654 is truncation problem. This is usually caused by the difference between the format of a field in the form and a column in the database.  
For example:
A database a column C1 has definition NUMBER(4,2) which should contain at most 2 decimal places; its counterpart, a field in the form F1 has the definition NUMBER of width 6.  
If the user on successive tries to update the same record, Oracle Forms will compare C1 with F1 and determine they are different. Oracle Forms "thinks" that that record in the database has been changed by another user and issues FRM-40654.
A workaround is to make sure the format of the form field adheres to the format of the corresponding database column by specifying a format mask (e.g. 9999.99).


That error also could happen 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 that data in the form will be transferred to the database. This process is done in a block by block basis, and the order is determined by the sequence number of a block.
Forms Triggers:
FRM-40654 occurs when a record in previous block is changed either by DML or a PL/SQL assignment statement in a Commit-time forms trigger (Pre-Update, Post-insert,etc) of the current block so that that record is not in sync with its counterpart in the database. On successive commits, when the user tries to update that record, Oracle Forms will first compare it with its counterpart in the database. Since the forms record and database record are different, Oracle Forms will behave as if the record has been changed by another user.
The only workaround is to avoid changing any record in previous blocks in any  Commit-time triggers.  
Database triggers:
FRM-40654 can also occur if a record has been updated using a database trigger.
For example:
A base table block contains a date field, which is a base table item.  The record is updated and the commit is requested.  The update to the database causes a database trigger to fire, which updates the date field in the database record.  The commit fails and FRM-40654 occurs because the forms record and the database record are different.  To avoid the error, this item
could be made a non-base table item.
When a user updates a record, Oracle Forms issues a SELECT FOR UPDATE to determine if the record is currently locked.  If it is not, then Oracle Forms compares the values on the forms to the values currently in the database.  If they are not equal, FRM-40654 occurs.


On the item property sheet, setting the "Query Only" to True can cause FRM-40654.
For 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.

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.

Author Comment

Comment Utility

Thank you for the awesome response! You provided a great deal of information & I certainly appreciate it.

After studying your answer & re-examining my problem I made a few changes, re-tested my changes & am now prepared to do the following:

I think I need to force a requery on screen 1. I know the following works if I do it manually.
Clear Record
Enter Query
<enter fields>
Execute Query

I would like to be able to do the above 3 commands programmatically.
I know Clear_Record; works fine.
I also know that if I use Enter_Query; it sits & waits on input. I
don't want to force the operator to re-enter data. I want to get into
query mode & then Execute_Query; but I don't know how to make this
happen in pl/sql. Can you tell me how this is done?

Thanks again,
Martin Meadows

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now