Unique sequence generator with reclaim

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