Home » Developer & Programmer » Forms » FRM-40654: Record has been updated by another user. Re-query to see change
FRM-40654: Record has been updated by another user. Re-query to see change [message #438586] Mon, 11 January 2010 02:02 Go to next message
babar82
Messages: 108
Registered: March 2009
Location: Karachi
Senior Member
Hi,
My form is based on a complex view based on two tables. There is an update code and an insert code written in the ON-INSERT and ON-UPDATE triggers respectively. After I commit the form and try to update any field, the system gives the error " FRM-40654 Record has been updated by another user. Re query to see change."
I am using Forms6i.

Kindly help..
Re: FRM-40654: Record has been updated by another user. Re-query to see change [message #438601 is a reply to message #438586] Mon, 11 January 2010 02:53 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Check what has been said here. Does any of the suggestions help?
Re: FRM-40654: Record has been updated by another user. Re-query to see change [message #438606 is a reply to message #438601] Mon, 11 January 2010 03:06 Go to previous messageGo to next message
babar82
Messages: 108
Registered: March 2009
Location: Karachi
Senior Member
No, nothing worked
Re: FRM-40654: Record has been updated by another user. Re-query to see change [message #438629 is a reply to message #438606] Mon, 11 January 2010 05:05 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you, please, list all that you did so far (but, apparently, didn't succeed) so that people would know it and, possibly, suggest something different?
Re: FRM-40654: Record has been updated by another user. Re-query to see change [message #438633 is a reply to message #438586] Mon, 11 January 2010 05:31 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You get record has been updated by another user when the contents of your datablock differs from the contents of the record(s) it is populated from in the database but the datablock is unchanged as far as forms is concerned (record_status = QUERY).

The 2 most common ways of getting it are:
1) Your table has database triggers on it that change the value of columns on the table on update or insert.
If you don't have DML Return set to TRUE this will give that error.
2) A manual update statement that sets one or more columns to different values than what is currently in the datablock.

Considering you are using on-insert and on-update triggers I strongly suspect you are suffering from number 2.
Re: FRM-40654: Record has been updated by another user. Re-query to see change [message #438722 is a reply to message #438633] Mon, 11 January 2010 23:44 Go to previous messageGo to next message
babar82
Messages: 108
Registered: March 2009
Location: Karachi
Senior Member
How can I identify those values which are different?

There is a code also written in POST-QUERY.

set_record_property(get_block_property('block', current_record), 'block',status,query_status);

Re: FRM-40654: Record has been updated by another user. Re-query to see change [message #438847 is a reply to message #438586] Tue, 12 January 2010 10:24 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Query the record in a seperate sqlplus session (assuming the changes are commited), if not you can use a cursor to select the current values - stick a button in the form, code it to select the current record and display the values.
Or just check your on-insert/update triggers for any code that would set a db column to a different value to the corresponding datablock item.
If you post the code of those triggers we can take a look and see if we can spot anything.

The code in the post-query is not going to make any difference to this.
Re: FRM-40654: Record has been updated by another user. Re-query to see change [message #438899 is a reply to message #438847] Tue, 12 January 2010 22:34 Go to previous messageGo to next message
babar82
Messages: 108
Registered: March 2009
Location: Karachi
Senior Member
The code on ON-UPDATE trigger is

update emp_all_ded
set
ALL_DED_ID = :emp_all_ded.all_ded_id,
AMOUNT = :emp_all_ded.amount,
FROM_DATE = :emp_all_ded.from_date,
TO_DATE = :emp_all_ded.to_Date,
OFF_ORDER_NO = :emp_all_ded.off_order_no,
OFF_ORDER_DATE = :emp_all_ded.off_order_date,
updated_by = :global.payroll_user,
updation_DATE = sysdate

where
emp_all_ded_id = :emp_all_ded.emp_all_ded_id;



ON-INSERT Trigger Code is

insert into emp_all_ded
(EMP_ALL_DED_ID,
EMPID ,
ALL_DED_ID,
AMOUNT ,
FROM_DATE,
TO_DATE,
OFF_ORDER_NO,
OFF_ORDER_DATE,
CREATED_BY ,
CREATION_DATE_TIME)

values
(:emp_all_ded.emp_all_ded_id,
:emp_all_ded.empid,
:emp_all_ded.all_ded_id,
:emp_all_ded.amount,
:emp_all_ded.from_date,
:emp_all_ded.to_Date,
:emp_all_ded.off_order_no,
:emp_all_ded.off_order_date,
:emp_all_ded.created_by,
:emp_all_ded.creation_date_time);


exception when others then

message(dbms_error_text);
Re: FRM-40654: Record has been updated by another user. Re-query to see change [message #438988 is a reply to message #438586] Wed, 13 January 2010 07:11 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Is either updated_by or updation_DATE an item in the datablock?
Re: FRM-40654: Record has been updated by another user. Re-query to see change [message #438989 is a reply to message #438586] Wed, 13 January 2010 07:24 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Also does this error happen after you insert or update or both?
And do you have a instead-of trigger on the view?
Re: FRM-40654: Record has been updated by another user. Re-query to see change [message #439063 is a reply to message #438989] Wed, 13 January 2010 22:46 Go to previous messageGo to next message
babar82
Messages: 108
Registered: March 2009
Location: Karachi
Senior Member
Yes, updated_by and updation_date are both datablock items and this error happens on both insertion and updation of records. For instance if I insert a new record and then try to delete it the error occurs. Secondly there is no trigger on the view.
Re: FRM-40654: Record has been updated by another user. Re-query to see change [message #439066 is a reply to message #439063] Wed, 13 January 2010 22:58 Go to previous messageGo to next message
babar82
Messages: 108
Registered: March 2009
Location: Karachi
Senior Member
Well I have solved the problem for ON-UPDATE trigger. Actually the updated_by and updation_time datablock fields contain old values while the values stored in the database contain were new. So I changed the ON-UPDATE Trigger like this..

update emp_all_ded
set
ALL_DED_ID = :emp_all_ded.all_ded_id,
AMOUNT = :emp_all_ded.amount,
FROM_DATE = :emp_all_ded.from_date,
TO_DATE = :emp_all_ded.to_Date,
OFF_ORDER_NO = :emp_all_ded.off_order_no,
OFF_ORDER_DATE = :emp_all_ded.off_order_date,
updated_by = :updated_by, (instead of :global.payroll_user)
updation_date = :updation_date (instead of sysdate)

where
emp_all_ded_id = :emp_all_ded.emp_all_ded_id;


But ON-INSERT Trigger is still causing problems.
Re: FRM-40654: Record has been updated by another user. Re-query to see change [message #439091 is a reply to message #438586] Thu, 14 January 2010 03:31 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I thought that might be the issue with the on-update.
As for the insert - the on-insert trigger only references items from the datablock, so can't have the same cause as the on-update. The only thing I can think of is you've got a database insert trigger on one or more of the tables the view is based on and this trigger is changing one or more of the columns.
If that's the case you're going to have to add a RETURNING clause to the insert statement to get the modified value(s).

[Updated on: Thu, 14 January 2010 03:34]

Report message to a moderator

Re: FRM-40654: Record has been updated by another user. Re-query to see change [message #439207 is a reply to message #439091] Thu, 14 January 2010 22:35 Go to previous messageGo to next message
babar82
Messages: 108
Registered: March 2009
Location: Karachi
Senior Member
can u tell me how ?
Re: FRM-40654: Record has been updated by another user. Re-query to see change [message #439234 is a reply to message #438586] Fri, 15 January 2010 03:24 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
how what?
How do you add a returning clause?
You can find the syntax in the docs along with examples.
Re: FRM-40654: Record has been updated by another user. Re-query to see change [message #439370 is a reply to message #439234] Fri, 15 January 2010 22:48 Go to previous messageGo to next message
babar82
Messages: 108
Registered: March 2009
Location: Karachi
Senior Member
I have written the following in ON-INSERT trigger using Returning clause

insert into emp_all_ded
(EMP_ALL_DED_ID,
EMPID ,
ALL_DED_ID,
AMOUNT ,
FROM_DATE,
TO_DATE,
OFF_ORDER_NO,
OFF_ORDER_DATE,
CREATED_BY ,
CREATION_DATE_TIME)

values
(:emp_all_ded.emp_all_ded_id,
:emp_all_ded.empid,
:emp_all_ded.all_ded_id,
:emp_all_ded.amount,
:emp_all_ded.from_date,
:emp_all_ded.to_Date,
:emp_all_ded.off_order_no,
:emp_all_ded.off_order_date,
:emp_all_Ded.created_by ,
:emp_all_ded.creation_date_time)

returning
emp_all_Ded_id,
empid,
all_Ded_id,
amount,
from_date,
to_date,
off_order_no,
off_order_date,
created_by,
creation_date_time

into
:emp_all_ded.emp_all_ded_id,
:emp_all_ded.empid,
:emp_all_ded.all_deD_id,
:emp_All_ded.amount,
:emp_all_ded.from_date,
:emp_all_ded.to_date,
:emp_all_ded.off_order_no,
:emp_All_ded.off_order_date,
:emp_all_ded.created_by,
:emp_all_ded.creation_date_time
;


But when I save a new record and then try to update/delete it the system gives the error...

ORA-00439: feature no enabled :RETURNING clause from this client type.






Re: FRM-40654: Record has been updated by another user. Re-query to see change [message #439372 is a reply to message #439234] Fri, 15 January 2010 23:26 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
did you have any property set like "BLOCK 'DML returning value :YES/NO'"

Quote:
ORA-00439: feature not enabled: string
Cause: The specified feature is not enabled.
Action: Do not attempt to use this feature.


Whar`s your Forms 6i version (4 digits)?

sriram Smile
Re: FRM-40654: Record has been updated by another user. Re-query to see change [message #439379 is a reply to message #439372] Sat, 16 January 2010 01:52 Go to previous messageGo to next message
babar82
Messages: 108
Registered: March 2009
Location: Karachi
Senior Member
The Block Property DML returning value was previously No. I changed it to Yes but the problem still exits.

I am using Form version 6.0.8.8.0

Re: FRM-40654: Record has been updated by another user. Re-query to see change [message #439406 is a reply to message #438586] Sat, 16 January 2010 04:20 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
DML Returning value is not going to help in this case.
It affects the default insert/updates forms generates for the datablocks.
Since on-update/insert overrides the default insert/updates it has no effect here.
However, what it does is add a returning clause to the default insert/update so I'm baffled as to why you are getting this error.

The only thing I think to suggest at this point is that you abandon using the on-update/insert triggers and instead base the datablock on a procedure that'll do the same job.
Hopefully this'll resolve the record has been updated issue.
Re: FRM-40654: Record has been updated by another user. Re-query to see change [message #671542 is a reply to message #438586] Tue, 04 September 2018 14:32 Go to previous message
rmantoani
Messages: 1
Registered: August 2018
Location: Brasil
Junior Member
I solved the problem by changing the "LOCKING MODE" parameter of Forms to "IMMEDIATE"
Previous Topic: Printing to Thermal Printer (Using Forms 6i)
Next Topic: Question with Pre-Insert trigger
Goto Forum:
  


Current Time: Thu Mar 28 13:46:29 CDT 2024