Re: How to create a sequence with a dynamic start value

On Jun 13, 7:44 am, Ed Prochak <edproc..._at_gmail.com> wrote:
> On Jun 12, 2:17 am, Norbert Pürringer <thalio..._at_graffiti.net> wrote:
>
> > Hello,
>
> > what is the best way to create a sequence with a dynamic start value.
>
> > The following statement is not valid:
>
> > create sequence TEXT_SEQ start with select NVL(max(ID),0) + 1 from
> > TEXT;
>
> > Thank you,
> > Norbert
>
> The core question is why do you need to do that?
> also why do you need to do it in one statement?
>   Ed

Many developers over the years have tried to read the table for the last ID value issued only to learn that he or she had failed to take into account the Oracle read consistency model as part of the transaction design.

Readers do not wait on writers so it is possbile for more than one user session to select the same max value and hence have inserts fail on duplicate key errors if a unique index exists on the column otherwise duplicate values exist for the column in the absence of the unique index.

Just use a sequence. Who cares if there are missing values? Under relational design theory a numeric generated key is a meaningless identifier. It just identifies the row, nothing more.

HTH -- Mark D Powell -- Received on Fri Jun 13 2008 - 10:05:33 CDT