Calling stored procedure from multiple clients? [message #300871] |
Mon, 18 February 2008 07:03  |
Gazda#1
Messages: 2 Registered: February 2008
|
Junior Member |
|
|
I have stored procedure which creates an unique number (sample number) for my record. I am calling that procedure with my Delphi client and i want to know, if 2 users use this client same time and call that procedure same time, can hapen that i have 2 same sample numbers or oracle waits until one procedure is over and then proceed with another request?
Sorry for my english but i hope you understand what i want to ask. I dont wanna have 2 same numbers.
|
|
|
|
Re: Calling stored procedure from multiple clients? [message #301072 is a reply to message #300871] |
Tue, 19 February 2008 02:29   |
Gazda#1
Messages: 2 Registered: February 2008
|
Junior Member |
|
|
Ok, my only conserne in my function is this sql
SELECT max(do2.lab_st) + 1
into r$lab_st
from l_table do2
UPDATE l_table set lab_st=r$lab_st where...bla bla
Like you see, i search for max and increment that max. What hapens if two clients call this same time? Would i get same number or oracle waits until function is over and then servs second client?
And i cant use sequence because every month i must start this numbering from zero and i have numbers 1/1, 2/1, 3/1, first is this number, second is month.
[Updated on: Tue, 19 February 2008 02:31] Report message to a moderator
|
|
|
Re: Calling stored procedure from multiple clients? [message #301076 is a reply to message #301072] |
Tue, 19 February 2008 02:40   |
 |
MarcS
Messages: 312 Registered: March 2007 Location: Antwerp
|
Senior Member |
|
|
If the SELECT and UPDATE are in the same function, what would happen if two clients call this function?
The second caller will wait for the first caller to complete. Why?
First client will lock the record due to the update.
Second client will try to lock the very same record and have to wait for the first client to COMMIT or ROLLBACK.
But this only applies on the situation/problem you just described to us!
By the way: you could even do "it" with only the update as well:
CREATE OR REPLACE FUNCTION GenerateNextValue ( value IN char) RETURN INTEGER
AS
nextValue INTEGER := 0 ;
BEGIN
UPDATE l_table do2
SET do2.lab_st = do2.lab_st + 1
WHERE ...
RETURNING do2.lab_st INTO nextValue ;
RETURN nextValue;
END;
/
HTH
Marc
[Updated on: Tue, 19 February 2008 02:42] Report message to a moderator
|
|
|
|
Re: Calling stored procedure from multiple clients? [message #301079 is a reply to message #301072] |
Tue, 19 February 2008 02:50  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | And i cant use sequence because every month i must start this numbering from zero and i have numbers 1/1, 2/1, 3/1, first is this number, second is month.
|
You can restart the sequence at 1 each month.
Or you can include the month inside the sequence.
Your way implies each transaction will wait the previous one before working. You serialize everybody, only one work at each moment.
Regards
Michel
|
|
|