Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


forms question

Posted on 1998-05-25
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 1081600
Are you calling FORMS_DDL anwhere in the process of going back and forth?

Accepted Solution

poncejua earned 800 total points
ID: 1081601
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

ID: 1081602

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

636 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