HowTo - create user defined sequence with proper serialization

Folks,
  Here is a difficult question that I'm hoping you can help me solve.

  Requirements:

• Create a user defined like "sequence" that increments by power of 2.
• This defined "sequence" must distribute values like 1,2,4,16,256 etc ..
• If a value is deleted, then the sequence must re-use that value.
• acess to this "sequence" must be serialized so that no two users can grab the same value

 Example: (GROUPS table)

ACCOUNT_ID NAME SEQ
======= ===== =====
 347164311 GROUP1 1
 347164311 GROUP2 2
 347164311 GROUP2 4

• So if account id " 347164311" wanted to create a new group in the GROUPS table, it would obtain a new sequence with the value of 16 and it would insert it.
• If at some point GROUP2 was removed from the table, the custom sequence would distribute 2 as the "NEXTVAL" ..

The key thing here is serialization and I'm not sure how to achive this.
Here is the sample function that delivers the next available sequence, but
doesn't yet serialize access to it.

CREATE OR REPLACE FUNCTION GET_SEQ (ACCTID NUMBER) RETURN NUMBER IS
i NUMBER := 0;
two_power_i NUMBER := power(2,i);
curr_mask groups.seq%TYPE;

CURSOR masksCursor IS SELECT seq FROM groups WHERE account_id = acctid ORDER BY seq;

BEGIN
OPEN masksCursor;
LOOP
FETCH masksCursor INTO curr_mask;
EXIT WHEN curr_mask != two_power_i OR masksCursor %NOTFOUND; i := i + 1;
two_power_i := power(2,i);
END LOOP;
CLOSE masksCursor;
RETURN two_power_i;
END;
/

Any idea on how to make the above function serial so that if 2 concurrent users execute the function, they don't both get the same value?

-peter Received on Tue Aug 29 2006 - 10:57:07 CDT