Now open two sessions, query the same data in both sessions. In session A, update a record and save the data.Now go to session B (we can see old data in screen), update the same record which we have updated in session A, record is updated successfully.>
I have written lock procedure which is working fine.The lock appears to be doing it's job. The record in Session A is locked for the duration of the update and then the lock is released. If you were to attempt to modify the record in Session B before you commit changes in Session A then you should see a Lock related error, but since the update in session A has been commited, session B has no way of knowing the record was updated since the time it was fetched so it displays the FRM-40654 - Record updated by another user error. This is expected.
So the important thing is "in table based block, form recognizes that data is changed after query, but in procedure based block, its not working".Again, this is expected behavior because when a data block is based on a procedure, you are overriding the standard Forms interaction with the table. Therefore, the only time the procedure based block will know there have been updates to the underlying data is when you attempt to write changes using your update procedure listed in the Block's On-Update trigger.