Home » SQL & PL/SQL » SQL & PL/SQL » Calling stored procedure from multiple clients?
Calling stored procedure from multiple clients? [message #300871] Mon, 18 February 2008 07:03 Go to next message
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 #300873 is a reply to message #300871] Mon, 18 February 2008 07:14 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Your english is not so bad Wink

Could you post the code of your stored procedure, so we can figure it if it could be a problem or not.

If you don't want the same number be used twice, an Oracle SEQUENCE would be advisable.
If you don't want the same number be used twice, BUT you don't want to have gaps between numbers as well, then another solution should be developed.
Re: Calling stored procedure from multiple clients? [message #301072 is a reply to message #300871] Tue, 19 February 2008 02:29 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #301077 is a reply to message #301072] Tue, 19 February 2008 02:44 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Gazda#1 wrote on Tue, 19 February 2008 09:29

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



Just noticed: why doesn't the SELECT have the WHERE-clause you use with the UPDATE? Any reason for doing so?
Re: Calling stored procedure from multiple clients? [message #301079 is a reply to message #301072] Tue, 19 February 2008 02:50 Go to previous message
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
Previous Topic: Combining update statement.
Next Topic: Comparing two tables
Goto Forum:
  


Current Time: Tue Feb 11 02:43:25 CST 2025