Home » Developer & Programmer » Forms » Lock on record while Updating
Lock on record while Updating [message #405016] Tue, 26 May 2009 01:29 Go to next message
Stuti_A
Messages: 4
Registered: May 2009
Junior Member
Hm.. here goes Ill try and be as specific as possible.

I have a form which has a search criteria based on which certain values are picked up from a TRACKER table. The user can change the displayed values (which is another data block)

The Problem is I cannot update the values,all the parameter values being passed are correct, after I have updated if I try to chk the table manually(it does not show the updated value, and if I try to change the column value I have updated) it gives an ORA-00054: resource busy and acquire with NOWAIT specified, which means that Forms is communicating with the table and there is a lock on that particular record, but it never updates. What can be the problem?

FYI I have a commit


The code looks like this :-

PROCEDURE update_button_TRIGGER IS
L_COUNT VARCHAR2(10);
BEGIN
:SYSTEM.MESSAGE_LEVEL:='5';
GO_BLOCK('UPDATE_TR');
LAST_RECORD;
L_COUNT:= :System.Cursor_Record ;
FIRST_RECORD;
FOR I IN 1.. L_COUNT
LOOP
UPDATE TRACKER TR
SET COL1=:UPDATE_CR.COL1
WHERE TR_PK=:UPDATE_CR.TR_PK;
COMMIT;
NEXT_RECORD;
END LOOP;
END;
Re: Lock on record while Updating [message #405025 is a reply to message #405016] Tue, 26 May 2009 02:04 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why do you do it this way?

In my opinion, block that contains records which are changed should be a DATA BLOCK (i.e. based on a database table). Once user fetches desired records, he/she can update values and simply press COMMIT.

No PL/SQL code is necessary.

If, on the other hand, block is a control block, the question is: why would it be a control block? It only makes things complicated.
Re: Lock on record while Updating [message #405031 is a reply to message #405025] Tue, 26 May 2009 02:23 Go to previous messageGo to next message
Stuti_A
Messages: 4
Registered: May 2009
Junior Member
have over-riden the default forms screen , the place where the user is updating is a data block, its based on values frm the TRACKER table.

So I have a button to explicitly update the fields that are changed.

:-/ am only picking up Forms based on trial and error, and I am not sure what the correct approach shud be.

I might as well mention that if I just make a simple form based on the same table and a button with the code I mentioned it works ok.

In the other form, I have a pop-up text editor( to display and update fields that are too long to be displayed on the screen and give the user more room to right), a calendar ( for between date values for the tracker), all in all complicated, does this give a clue?

DO let me know if the code wud give a better perspective.
Re: Lock on record while Updating [message #405043 is a reply to message #405031] Tue, 26 May 2009 03:12 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As it seems that you are not that experienced Forms developer, perhaps you should create a form using all help Forms Developer provides, i.e. its wizard(s). Form, created by wizard, will work within its "default" limits. All basic functionalities (searching, updating, inserting, deleting) will work properly.

Once you make a form work, you may try to make it fancy (i.e. add buttons, additional coding etc.).

In my opinion, your "button-based" approach is wrong.
Re: Lock on record while Updating [message #405053 is a reply to message #405043] Tue, 26 May 2009 03:36 Go to previous messageGo to next message
Stuti_A
Messages: 4
Registered: May 2009
Junior Member
appreciate your time, but I really dont have an option I have to make it with the advanced fectures in place, I plan to break it down into pieces and debug. Will update the thread when I get to the bottom of it,

Thanks Regards, and hope you have a nice day !
Re: Lock on record while Updating [message #405065 is a reply to message #405053] Tue, 26 May 2009 04:06 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's a hint (if it helps): as you've said that this block is a data block (based on a table), perhaps the fact that user has changed items collides with your button's UPDATE statement (i.e. one update (directly in a block) locks another one (UPDATE in a loop)).
Re: Lock on record while Updating [message #405074 is a reply to message #405016] Tue, 26 May 2009 04:31 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
There's something else going on here.

The code listed in the first post can't give the error specified because it doesn't have a NOWAIT.

Also forms default locking won't give that error either - it does use nowait but it handles it to display a different message.

So there's some other code that contains a NOWAIT being used.

Also a form can't lock itself out - all locks are held by the same session. So there needs to be two different users locking the same set of data.

All that said, if you use the default oracle behaviour it handles locking for you automatically.
Re: Lock on record while Updating [message #405078 is a reply to message #405043] Tue, 26 May 2009 04:37 Go to previous message
Stuti_A
Messages: 4
Registered: May 2009
Junior Member
it works fine, where i had gone wrong I had deleted the PK column in the data block by mistake and re-created it manually.

So maybe I missed out some property or something that got set at the block level, which is why the comparison while setting the value on update was probably failing.

I really appreciate your suggestions Littlefoot, but I am halfway in between completing something somebody else started.

But maybe next time when I build something from scratch, will try and keep things simple.

Thx and bye, thx also due to cookiemonster, funny that simple things play big havoc

[Updated on: Tue, 26 May 2009 04:39]

Report message to a moderator

Previous Topic: HOW TO CALL PROCEDURE IN ORACLE FORMS TREE
Next Topic: frm-40737 illegal restricted procedure first_record next_record
Goto Forum:
  


Current Time: Sun Dec 04 14:26:27 CST 2016

Total time taken to generate the page: 0.11047 seconds