OraFAQ Forum: Forms » Locking problem

Home » Developer & Programmer » Forms » Locking problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Locking problem [message #82586]

Wed, 11 June 2003 05:55

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?

Report message to a moderator

Re: Locking problem [message #82593 is a reply to message #82586]

Thu, 12 June 2003 00:14

Maaher
Messages: 7065
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:

Begin
  :EMP.SAL := :EMP.SAL*1.02;
End;

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
Is
  record_locked Exception;
  PRAGMA EXCEPTION_INIT(record_locked,-54);

  Cursor c 
      Is
  Select 1 
    From emp
   Where empno = p_empno
  For Update Nowait;   
Begin
  Open  c;
  Close c;
  Return 0;
Exception
  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:

Begin
  If fun$lock(:EMP.EMPNO) = 0 -- no locks
  Then
    :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;
End;

I didn't test it, but I know I've seen a similare mechanism somewhere.

MHE

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 19:34:50 CDT 2024