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.

Code:
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));

		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