Re: Unique sequence generator with reclaim
Date: Sat, 18 Dec 1999 12:56:29 GMT
Message-ID: <83g09u$fdj$1_at_nnrp1.deja.com>
Rami,
The only way I know of to do what you want is your basic brute force solution. First, create a table with two columns, a NUMBER big enough to hold the maximum value your sequence will ever reach, and a second column to act as an 'in use' flag. This can be CHAR, VARCHAR2 or NUMBER - whatever you think is best. Next, create a new Oracle sequence, start with the minimum value you wish your ID number to be and increment by 1. You can then create a PL/SQL loop to load the table from the sequence, set all IN_USE to whatever value you choose to represent 'NO' (NULL, 0, 'N', or whatever). You can then UPDATE the table from your main table, setting IN_USE to 'YES' where a matching record exists in your main table. After that you have the nightmare of maintaining the table. When you need an ID, you SELECT MIN(id_num) FROM id_table where in_use = 'N';. Probably the simplest/safest way to do this is in a row level BEFORE INSERT trigger. Home-brew sequence numbers can get hairy on a large multi-user system. If there is any other way to accomplish your purpose, consider it. For example, if your situation allows it, adding an extra column to your main table for an Oracle sequence, inserting all new records with an id of 9999999999, the updating those records, one at a time in a loop, with valid ID's in a batch job at night. I realize with the 24X7 nature of many businesses today this may not be feasible, but have to mention it.
Hope this helps,
Paul
In article <385AC8C6.D2DB81D8_at_americasm01.nt.com>,
Rami Zaatari <rzaatari_at_americasm01.nt.com> wrote:
> Hello,
>
> I have this issue that I believe somebdy else might have dealt with.
> Please take a look
>
> We need to have a unique ID sequence number generator for a certain
> table, but the one Oracle provides (SEQNAME.NEXTVAL) will not work
well
> for us, for the following reasons:
>
> 1. we need to be able to reclaim back lost sequence numbers/ ids (in
> case records with ids were deleted)
> 2. we need to let the user skips and enters a unique sequence
number/id,
> and the sequence generator should take care of it in case it reached
> that id later.
>
> I appreciate any hints/lead/thoughts about that. Does Oracle provide a
> more intelligent sequence.
>
> Thank you for your attention,
>
> Rami
>
> -------------------------------------------------------------
> Rami M. Zaatari N O R T E L N E T W O R K S
> rzaatari_at_nortelnetworks.com Carrier Packet Solutions
> (919) 905-3534/ESN 355-3534 NCRTP, Dept. B932
> (919) 991-4088 (Fax) RTP, NC 27709
>
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Sat Dec 18 1999 - 13:56:29 CET