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 #82611]

Thu, 12 June 2003 22:59

Padmalaya Nanda
Messages: 7
Registered: June 2003

Junior Member

Hi,

In reply of my question to the following question, I got the reply as follows

Query
--------
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?

Reply
--------
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 NumberIs 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: reso! urce 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

But, forms first shows an alert "Cannot not reserve record (2 tries) keep tryng ?, , if user clicks on 'No' , then it shows the error message "FRM-40501- Could not reserve record for update or delete", if user clicks Yes the no.of tries increases to 4,6,8..... so pls. help me how to trap this alert and as more than 100 forms access a common library, I can't use the logic given in your reply.

I've tried with "When others" exception, Form_Faliure, Form_Fatal, Form_success, but I couldn't trap the error.

So pls. help me asap.

Report message to a moderator

Re: Locking Problem [message #82613 is a reply to message #82611]

Fri, 13 June 2003 00:46

Maaher
Messages: 7065
Registered: December 2001

Senior Member

So, what you are saying is that you have tried to include the function as explained, and it still poses the same problem .
I just created my example just like I explained and it poses no problem.
1. Create the function
2. Use the function in the post-query.

What the function does is trying to lock the record. If the lock fails the function will return 1. If you call this function before any changes have been made to the record, and if you use the logic mentioned in the previous reply, the error shouldn't occur. Are you sure you trapped the message for each block and item? Perhaps you're changing an item before you call the function and set the items read-only. You can test that by adding a message in the function's exception
handling. Your exception block could look like:

...
...
Exception  
  When record_locked Then    
    Message('lock has been placed by other session');pause;
    Return 1;
End fun$lock;

If the message you added here appears after the 'could not....' message, this means that you're doing modifications before the call to the function. If you see the function's message and after that you see the message 'Could not...', this means that you have forgotten to include an item to make it read only.

You don't have to trap the FRM error you know, if you trap the cause of this error (namely the ORA-00054 message), the FRM message shouldn't occur.

Let me know if the problem persists. But if you are unsure, try recreating the exact example I gave you.

If it works, see what you've done differently.
If it doesn't work, let me know.

It's hard to tell from a distance what is wrong.

MHE

Report message to a moderator

Previous Topic:	Diff. between object groups and object library
Next Topic:	post-query/diplay item error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 06:59:55 CDT 2024