• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5349
  • Last Modified:

FRM-40655: SQL Error forced Rollback

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
HuzaifaMerchant
Asked:
HuzaifaMerchant
  • 3
  • 2
1 Solution
 
renurajCommented:
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
 
HuzaifaMerchantAuthor Commented:
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
 
HuzaifaMerchantAuthor Commented:
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
 
renurajCommented:
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
 
HuzaifaMerchantAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now