Home » Developer & Programmer » Forms » FRM-40501 and ORA-00054 (FORMS 6i)
FRM-40501 and ORA-00054 [message #678051] Tue, 05 November 2019 04:49 Go to next message
Boumati
Messages: 10
Registered: November 2019
Junior Member
Hi,

how to avoid this message "could not reserve record(2 tries),keep trying"
with preserving the integrity of data .

messages involved : FRM-40501 and ORA-00054

thanks a lot
Re: FRM-40501 and ORA-00054 [message #678053 is a reply to message #678051] Tue, 05 November 2019 05:07 Go to previous messageGo to next message
cookiemonster
Messages: 13759
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are you aware of the Locking Mode Property?
Read up on it in form builder help if not.

Do you understand locking strategies?

Do you know what locking strategy you want?
Re: FRM-40501 and ORA-00054 [message #678054 is a reply to message #678053] Tue, 05 November 2019 07:04 Go to previous messageGo to next message
Boumati
Messages: 10
Registered: November 2019
Junior Member

hi,

Locking Mode Property is at value default "automatique" (in french version Forms [32 bits] Version 6.0.8.8.0 (Production);
there is three values : automatique,immédiat,défféré.
i just need to avoid this message and preserve integrity of data.
i've no idea about locking strategy

thanks .

Re: FRM-40501 and ORA-00054 [message #678056 is a reply to message #678054] Tue, 05 November 2019 07:18 Go to previous messageGo to next message
cookiemonster
Messages: 13759
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well you need to understand locking strategies.
There are two (and this is in general, they are not oracle specific):
1) Pessimistic - a session explicitly locks rows in the DB. If another session tries to lock them while the lock is held they get an error. Lock is released by commit or rollback. Lock is obtained with select for update.
2) Optimistic - rows are not explicitly locked, but when the session does an update it checks no other session has modified the data in the meantime. If it has you get an error.

In oracle forms immediate is pessimistic, delayed (deffere) is optimistic and automatic is the same as immediate if you're using an oracle DB.

So forms is locking rows when users change them.
Then if another user tries to change one of those rows while the first user has them locked you will get the error you are seeing. That's forms telling you you can't lock them.

You could change to delayed but that would cause different issues:
User A queries a row, gets value 5.
User B queries same row, gets value 5
User A sets value to 3
User B sets value to 6
User A clicks save - 3 is written to the DB.
User B clicks save - they get an error saying someone else has changed the data and they need to re-query.

You should read up on optimistic and pessimistic locking - there's lots of articles online - and think about which you want.

But if you're getting that error a lot then there may be a fundamental design issue - why are users constantly trying to update data at the same time as other users?

Re: FRM-40501 and ORA-00054 [message #678057 is a reply to message #678056] Tue, 05 November 2019 07:44 Go to previous messageGo to next message
Boumati
Messages: 10
Registered: November 2019
Junior Member
to summarize i want :

catch the error code and the name of the user who is blocking the record ;
(because there is user who block record and keep the record locked) .
if it's possible to sen him message or to DBA to kill this session.

thanks a lot.

ex :

on_error trigger :
DECLARE 
  messcode     NUMBER        := MESSAGE_CODE; 
  messtext     varchar2 (200):= MESSAGE_TEXT; 
  i number;
BEGIN 
if   messcode in (40401,40405) -- NO CHANGES to SAVE 
  or messcode=40350  -- QUERY CAUSED NO RECORDS TO BE RETRIEVED 
  or messcode=40352  -- LAST RECORD 
  or messcode=40102  -- RECORD MUST BE ENTERED 
  or messcode in (41051,41803, 41049)  -- insert, duplicate et delete 
then null;
elsif messcode=40743  then -- REQUIRE ON LOCK TRIGGER 
  i:=Alerte('Alert_Erreur',' Vous ne pouvez modifier une clé');
  raise form_trigger_failure;
elsif messcode=40400 then -- transaction complete 
  message('******* Enregistrement effectué avec succès *******');
elsif messcode=40501 then -- transaction complete 
  message('******* ressources occupées *******');    !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
elsif messcode=00054 then -- transaction complete 
  message('******* ressources occupées *******');    !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
elsif messcode <> 0 then
  message (messcode|| '****  ' || messtext);message (messcode|| '****  ' || messtext);
end if;
END;


[EDITED by LF: applied [code] tags]

[Updated on: Fri, 08 November 2019 16:30] by Moderator

Report message to a moderator

Re: FRM-40501 and ORA-00054 [message #678058 is a reply to message #678057] Tue, 05 November 2019 07:58 Go to previous messageGo to next message
cookiemonster
Messages: 13759
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why didn't you say so in the first place?

A normal user doesn't have permission to see who's blocking them.

A DBA can run queries to see who is currently blocking who, but since forms gives up immediately when there's a lock that's not much use.

There's probably a way of determining who the culprit is but I don't know it, others on the forum might.
Re: FRM-40501 and ORA-00054 [message #678060 is a reply to message #678058] Tue, 05 November 2019 08:15 Go to previous messageGo to next message
Boumati
Messages: 10
Registered: November 2019
Junior Member


to catch the error code and send message to user who is trying to update the data blocked by an other user.
i want to know how i catch the error because i receive in place the message "could not reserve record(2 tries),keep trying"
without possibility to catch the error .

an example if possible.

thanks .



Re: FRM-40501 and ORA-00054 [message #678061 is a reply to message #678060] Tue, 05 November 2019 09:52 Go to previous messageGo to next message
cookiemonster
Messages: 13759
Registered: September 2008
Location: Rainy Manchester
Senior Member
Check if on-error is firing at all in that case with a message
Re: FRM-40501 and ORA-00054 [message #678070 is a reply to message #678061] Wed, 06 November 2019 03:50 Go to previous message
Boumati
Messages: 10
Registered: November 2019
Junior Member
Hi,

i'll test that .

thanks a lot.



Previous Topic: Menu not mapping in Oracle form 12c
Next Topic: Save Images in Database From Forms - Download it Here
Goto Forum:
  


Current Time: Sat Dec 14 01:06:42 CST 2019