forms question

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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Are you calling FORMS_DDL anwhere in the process of going back and forth?
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mmeadowsAuthor Commented:

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.