Home » Developer & Programmer » Forms » Locking problem
Locking problem [message #82586] Wed, 11 June 2003 05:55 Go to next message
Padmalaya Nanda
Messages: 7
Registered: June 2003
Junior Member
I've an application developed using developer 2000. The appl. needs to be accessed
by multiple users, as there is a post-query trigger on one block(having multiple records)
, which assigns some value to one of the items of the block, so the records are locked.
So whne the second user accesses the application, he gets
"FRM-40501:Oracle error:Unable to reserve record for update or delete" error for
each record of the block. How can I make the block read only when the second user
accesses the application, which trigger i should use, if the trigger is on-lock,
then what should I write in that trigger.

I can set the locking mode to immediate or manual, but it has to be immediate as the application is very big and this change has to be made for around 100 forms. We've a central library which handles the on-lock trgger, what should I write there, so that if the form is accessed by the first user the records should be locked and first use should be allowed to change, but all other users should see the records in read-only mode. Could u pls. help me the solution asap?
Re: Locking problem [message #82593 is a reply to message #82586] Thu, 12 June 2003 00:14 Go to previous message
Messages: 7064
Registered: December 2001
Senior Member
Let's say you have a block EMP and you'd like to raise the SAL by 2% , so in the POST-QUERY, the code is like this:
  :EMP.SAL := :EMP.SAL*1.02;
In a multi user environment, you want to avoid that 2 people perform the same action on the same time. Only the first user should to the increase in salary, for the rest there should be no change.

I think you should use a function (program unit) like this:
Function fun$lock(p_empno emp.empno%TYPE) Return Number
  record_locked Exception;
  PRAGMA EXCEPTION_INIT(record_locked,-54);

  Cursor c 
  Select 1 
    From emp
   Where empno = p_empno
  For Update Nowait;   
  Open  c;
  Close c;
  Return 0;
  When record_locked Then
    Return 1;
End fun$lock;
If the function returns 0, there's no error encountered, the record wasn't locked by any other person. If the function returns 1, the record is locked (ORA-00054: resource busy and acquire with NOWAIT specified encountered).

Now you include this function in the post-query:
  If fun$lock(:EMP.EMPNO) = 0 -- no locks
    :EMP.SAL := :EMP.SAL*1.02;
  Else -- record is currently locked, prevent updates.
    Set_Item_Instance_Property('EMP.SAL', CURRENT_RECORD, UPDATE_ALLOWED, Property_False);
    Set_Item_Instance_Property(... -- repeat for any other changeable database field to avoid the FRM message
  End If;
I didn't test it, but I know I've seen a similare mechanism somewhere.

Previous Topic: Error with DDE!
Next Topic: How to do this?
Goto Forum:

Current Time: Mon May 16 20:54:11 CDT 2022