Re: SQL Questions.

> > > What are the pitfalls of using sequences in a multi - user environment?
> The use of sequences and the fact of a multi-user environment really do
> not have much to do with each other. Sequences are Oracle objects which
> generate numbers sequentially on request, via the NEXTVAL method; it is
> also possible to find the value of the last sequence number generated
> via the CURRVAL method. As the name implies, sequences generate
> sequential integers - 1, 2, 3, 4, 5 ... - subject to some constraints
> specified when the sequence is created - things like starting value and
> maximum value and whether values can or cannot be generated more than
> once. Sequences frequently, perhaps even usually, are used to generate
> guaranteed-to-be-unique values for one or more tables' primary key
> values. Sequences are user-independent objects, and if several users are
> running applications that draw values from the same sequence, Oracle
> will ensure that each user gets unique values. Oracle is designed as a
> multi-user system; there should be no pitfalls.

But what if the sequence was beging used to generate unique numbers for, say, a personnel / staff number? If more than one user used the sequence wouldn't there be the risk of:

1. New employees being assigned the same staff number (though I understand a primary key or unique constraint would prevent this).
2. A new employee's staff number not being in the correct sequence?

Just a thought. If, as you say, sequences are user-independent, wouldn't only one user be allowed to use the sequence to update a table used to store this kind of data? Otherwise, one user might be on currval 1020 whilst the other user is on currval 872 (or etc) which would already have been assigned to a new employee. Received on Sun Mar 08 1998 - 00:00:00 CST