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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

800 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