Hi Mark,

The SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; will lock the table for the duration of the transaction. I do something similar in this "getserial number" where i select the next number and update it. This code may perhaps help if you customize it to your needs the point is...

CREATE OR REPLACE PROCEDURE GetSerial
(pModuleName IN VARCHAR2 , 
pFieldName IN VARCHAR2,
pKey IN VARCHAR2,
pSerialNumber OUT Number )
IS
BEGIN
 COMMIT;
 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 pSerialNumber:=-1;	 --indicates error condition


 select NEXTNUMBER into pSerialNumber from serials 
	where MODULNAME=pModuleName AND FIELDNAME=pFieldName AND KEY= pKey 
		AND rowid=(select MAX(rowid) FROM serials where MODULNAME=pModuleName AND FIELDNAME=pFieldName AND KEY= pKey ) FOR UPDATE;
 
	pSerialNumber:=pSerialNumber+1;
 	UPDATE SERIALS SET NEXTNUMBER=pSerialNumber
 		where MODULNAME=pModuleName AND FIELDNAME=pFieldName AND KEY= pKey;
 
  COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
	 --add record to table
	 INSERT INTO SERIALS (MODULNAME,FIELDNAME,KEY,NEXTNUMBER,RECNO)
	 		VALUES (pModuleName,pFieldName,pKey,1, serialsseq.nextval||to_char(sysdate,'DDDSSSSS') );
	 COMMIT;
	 pSerialNumber:=1;
END;
/