Transaction Control Oracle 9i Functiosn etc..

[AndyHollywood]

Hello

I'm having a wee problem with Oracle Stored Procedure/Functions

I was using SQL Server and Transaction Control in that was relativly easy. However, i have now had to move the DB over to Oracle 9i.

Basically i have a postcode table, which should have unique postcodes in it, and an ID number. If it dosen't exist then it should be inserted.

The ID number of the postcode should be returned.

CREATE OR REPLACE FUNCTION sp_insertpostcode(postcodeIN IN postcode.postcode%TYPE) RETURN NUMBER
AS
	RET_VAL  NUMBER;
	BEGIN
		RET_VAL := 0;

		SELECT 1 INTO RET_VAL FROM DUAL WHERE  EXISTS 
		(SELECT  ID FROM postcode WHERE (postcode.postcode = postcodeIN));



		SELECT COUNT(ID) INTO RET_VAL FROM postcode WHERE postcode.postcode = postcodeIN;
		IF (RET_VAL = 0) THEN
			INSERT INTO postcode(postcode) VALUES(postcodeIN);
			COMMIT;
		END IF;
		SELECT ID
			INTO RET_VAL
			FROM postcode
			WHERE postcode.postcode = postcodeIN;
		RETURN RET_VAL;
	END;
/

This function seems to work, however, I have written a program in Java that is multi threaded, abdout 10 threads each with there own connection could potentially be using this function, therefore i need some kind of transaction control on it.

I have no idea how to achieve this and make this function safe to be used by multiple users. Could someone give me some guidance as i am very very new to Oracle.

Cheers


Andy

 

[sem]

CREATE OR REPLACE FUNCTION sp_insertpostcode(postcodeIN IN postcode.postcode%TYPE) RETURN NUMBER
AS
RET_VAL NUMBER;
BEGIN

SELECT ID INTO RET_VAL FROM postcode
WHERE postcode.postcode = postcodeIN;
return ret_val;

exception
when no_data_found then
INSERT INTO postcode(postcode) VALUES(postcodeIN)
RETURNING ID INTO ret_val;
commit;
return ret_val;

when others then
raise_application_error(-20100, 'Internal error');
end;

The only problem may occur when somebody tries to insert a new record at the same time as the other already inserted but not commited it. You may implement some more robust logic, but my experience says that even for highly loaded OLTP systems it may be possible to process it this way though NEVER get this error.

Regards, Dima

 

[AndyHollywood]

Oh wow, worked a treat, makes my function look a little stupid.

Thank you very much.


So will this be done as transaction? what if it was caleld at the same time and 2 of the same postcodes were enterede at the same time!? or is that somethigni should combat in my application?

Andy

 

[sem]

I suppose you have unique constraint on postcode field. Thus Oracle generates an error for all subsequent sessions (dup_val_on_index exception). Uniquenes is checked regardless on commiting transaction, so in any case you will not obtain deadlock. Just improbable error. But I insist: this may occur if trying to insert the same value from other session only between issuing insert and commit in the first one.

Regards, Dima