Re: HowTo - create user defined sequence with proper serialization

p.santos..._at_gmail.com wrote:
> 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

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/functions102.htm#i78493

Peter,

If what you are indeed after is a sequence of numbers which are 2^^n, where n = 0,1,2, ... m
One could just create a sequence (e.g. myseq) with a starting value of 0, increment 1, nocache.
Create a function to return POWER(2,myseq.nextval) and use that in your insert statement. Return myseq.currval if needed.

-bdbafh Received on Tue Aug 29 2006 - 11:06:07 CDT