Re: Entering generated unique value.

> I have a table, and upon any INSERT to the table, I need to generate a
> value to be inserted with it. The value is generated separately from
> the INSERTed row, but must be unique to the table.
>
> Until now I've been gerating this outside the database, testing it's
> uniqueness (I.E. WHERE Generated_Id = <new_value> and looping until
> nothing is returned) and then inserting it. This has worked fine so
> far, but I am concerned as the table gets INSERTs more and more
> frequently, the time inbetween the generation and the insert may allow
> for another insert to use that value, causing this INSERT to fail.
>
> So, I've been thinking of creating a PL/SQL function to do the job,
> but how would I INSERT it? Through a stored procedure? A trigger? How
> do I guarantee it's uniqueness between the check and the INSERT? Do I
> need to just catch a failed INSERT and loop iuntil it gets doine
> correctly?
>
> Please note, that a sequential number is not useful here, I already
> have an a sequence generating sequential Ids for the row. This
> generated id, is of fixed length, and is used specifically for
> obfuscation.

Oracle provides a database object known as a SEQUENCE specifically to perform this function. It does it perfectly and faster than anything you could possibly write yourself. I strongly urge you to use the functionality already built into the database.

Daniel A. Morgan Received on Sun Feb 04 2001 - 21:09:23 CST