Solved

FRM-40655: SQL Error forced Rollback

Posted on 2001-06-15
5
4,134 Views
Last Modified: 2007-11-27
Im getting the following error message

FRM-40655: SQL Error forced Rollback: clear form and re-enter transaction.

I cant understand what is causing it. Can anybody help?

Using Forms Version 6.0.5.0.2 on Win98 connection to Oracle8.1.5

Thanks
0
Comment
Question by:HuzaifaMerchant
  • 3
  • 2
5 Comments
 
LVL 2

Accepted Solution

by:
renuraj earned 100 total points
ID: 6195790
This happens due to dead lock situation.

To avoid this problem, add NOWAIT option to the UPDATE and DELETE statements, which returns ORA-00054 error, trap this error and display user defined message like 'The record is being accessed and try after some time'.

This even improves the performance because no process waits for the locks preventing dead locks.

If it is due to the UPDATE's or DELETE's with base table blocks in the form, in the PRE-UPDATE and PRE-DELETE triggers write SELECT statements to select the particular row with FOR UPDATE NO WAIT option.  Handle by trapping the ORA-00054 error.

Regards,
0
 

Author Comment

by:HuzaifaMerchant
ID: 6196054
I was hoping that this would solve the problem but it doesnt.

The pre-update trigger
DECLARE
     table_row table_name%ROWTYPE;
     unable_to_lock EXCEPTION;
     PRAGMA EXCEPTION_INIT(unable_to_lock, -00054);
BEGIN
     SELECT *
     INTO   table_row
     FROM   table_name
     WHERE  pk_column = :block.pk_column
     FOR UPDATE NOWAIT;
     
EXCEPTION
     WHEN unable_to_lock THEN
          message('unable to lock');
END;
     
goes through ok, but still does not commit.

Any ideas?
Thanks
0
 

Author Comment

by:HuzaifaMerchant
ID: 6196055
I was hoping that this would solve the problem but it doesnt.

The pre-update trigger
DECLARE
     table_row table_name%ROWTYPE;
     unable_to_lock EXCEPTION;
     PRAGMA EXCEPTION_INIT(unable_to_lock, -00054);
BEGIN
     SELECT *
     INTO   table_row
     FROM   table_name
     WHERE  pk_column = :block.pk_column
     FOR UPDATE NOWAIT;
     
EXCEPTION
     WHEN unable_to_lock THEN
          message('unable to lock');
END;
     
goes through ok, but still does not commit.

Any ideas?
Thanks
0
 
LVL 2

Expert Comment

by:renuraj
ID: 6197791
Hi,

By handling this error, oracle will not allow you to commit the records.

In fact, it avoids the dead lock situation and stops the user to wait for a long time.

When the system displays such a message "Unable to lock", that means oracle cannot acquire lock in that moment because that record is being used by another session.

When it can acquire lock, system allows to perform the manipulation and commits the changes.

Only use of handling this oracle error message is to avoid dead locks.  As soon as the resources are released, the same operation can be performed successfully.

Regards,
0
 

Author Comment

by:HuzaifaMerchant
ID: 6228978
I really donot know what was causing this problem. I was working on an existing form and could not make too many changes to it. I was able to make a new form with the same functionality and it works.

Thanks for your help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now