Re: Distinct Random Number Generation

"Matthew D. Heilman" <matthew.d.heilman_at_ac.com> wrote:

>Here is the scenario. I want to generate a unique number so I can assign it
>as the indentifier of some attribute. I have a table that looks like:
>
>product_ID NUMBER(10) --Primiary key
>product_descr VARCHAR2(255)
>
>So, each time I have a new product_descr I want to assign it a unique
>product_ID. I was planning to have the font end generate a random number(or
>even have oracle do it) and then check if this number already exists in the
>table. But I do not want to have to query the table each time to see if a
>newly generated random number exists. I also can not use a counter, because
>I will be deleting products and I may run out of number eventually(I will be
>processing HUGE amounts of products).
>
>Any suggestions?
>
>
>

It would seem to be an ideal case for an Oracle Sequence

• after creating ( where you define its increment value, start value and upper limit if any - for your situation I would not set an upper limit) - then use a on_insert trigger to add the nextval as the Product ID - it will always be unique ( unless you choose to re-use numbers after max is reached - hence my advice to not set a upper limit ), so no checking is needed for existing use and Oracle handles these numbers very efficiently ( see the SqlPlus Reference manual for details and clearer info about the very useful Oracle feature ).

Hope it helps,
John Greco
Oracle DBA Received on Fri May 15 1998 - 11:46:35 CDT