Re: Entering generated unique value.

> >From a functional stand point I do not understand what you are trying to do.
> >But if you want a unique value for an index sequences are the answer. If you
> >want unique values that are not numeric ... either apply TO_CHAR() or BITAND.
> >
> >Dan Morgan
>
> I am trying to have an id for rows, where the id is not recognizable
> as having any meaning.
>
> Imagine a session id on a website that let's you read email or has a
> shopping cart. If the session ids were in any way sequential, someone
> would be able to mess up someone else's session. Therefore the number
> has to be completely random.
>
> In a similiar fasion, I need a completely unique id for each record,
> besides the usual sequence based id. I happen to have such a function,
> and have been using it for quite a while. I am just concerned that as
> the databse gets hit my more and more people, the time-lapse inbetween
> checking if an id is unique and entering it may allow for another
> process to add the exact same id causing the insert to fail.
>
> Maybe this will help explain the process.
>
> 1) Process-A: Generates id 'opIJ097hnyL'.
> 2) Process-B: Generates id 'opIJ097hnyL'.
> 3) Process-A: Checks if id 'opIJ097hnyL' is unique.
> 4) Oracle: tells Process-A that is is unique.
> 5) Process-B: Checks if id 'opIJ097hnyL' is unique.
> 6) Oracle: tells Process-B that is is unique.
> 7) Process-A: Inserts the record with the unique id.
> 8) Process-B: Inserts the record with the now non-unique id.
> 9) Process-B has an error that it doesn't know how to deal with.
>
> I am just looking for ideas on how to guarantee unique ids, or at
> least be able to handle the possible insert error.

It definitely helps. Though as a DBA my first thought was "someone else mess up a session?" Try instituting some security. The fact that your numbering is randomized hardly prevents a malicious person from doing damage.

But assuming that you have your reasons here's what I would do.

Institute the random number feature as suggested by Niall. Generate them daily, weekly, or monthly and store them in a table. Make part of the generation process validating that they are unique. This is a great thing to do at 2:00am. Then each time a number is selected from the table ... delete it so it can't be used again.

Daniel A. Morgan Received on Tue Feb 06 2001 - 17:11:20 CST