Re: Stupidity or sequences?

From: Tim Gorman <tim_at_evdbt.com>
Date: Sat, 13 Apr 2013 17:41:36 -0600
Message-ID: <5169ED30.7010809_at_evdbt.com>



Nuno,

Agreed on the basic suggestion to save pre-provisioned numbers (and related information) in a table, but....

 >> One way I've seen is to generate them in a single INSERT into a
 >> table_of_pending_cheques (topc) statement, using a sequence.
 >> This guarantees the serial numbering and by committing after the INSERT

This guarantees neither serial nor gapless numbering. An Oracle sequence is inherently multi-user and nothing will stop that; another session with a user thinking "hmmm, what's the present value in this sequence?" could perform a SELECT xxx.NEXTVAL FROM DUAL at any time while the pre-provisioning INSERT ... SELECT is in progress and create a gap in the set of inserted values.

Solution: have the pre-provisioning session generate its own values, using a loop, for example. Stay completely away from Oracle sequences when attempting to fulfill requirements for which Oracle sequences were not designed. As long as the default setting of NOCYCLE is retained, Oracle sequences will generate unique values using monotonically ascending numerics, with gaps always possible and (in the case of RAC) possibly out of chronological order. If chronologicaling order is a requirement, use SYSDATE or SYSTIMESTAMP, not a sequence; dates and timestamps can always easily be made into numerics if needed, if that matters. If no gaps are a requirement, pre-provision values as Nuno has described -- without the use of a sequence.

Thanks!

-Tim

On 4/13/2013 8:03 AM, Nuno Souto wrote:
> Bingo! Basically, the idea is to use the INSERT to grab a gapless
> series and stash it away for later use, either by the original session
> or another that "cleans up" later if original fails for whatever
> reason. As in: grab a sequence of numbers to be used in serialising
> documents/cheques/invoices/whatever physical representation is needed.
> In a fashion that avoids serial loss if serializing session fails.
> Of course, just like you said: it's all about what the needs are. In
> most cases where gapless is not needed, a simple Oracle sequence will do
> the job nicely and without major convolutions.
>

--
http://www.freelists.org/webpage/oracle-l
Received on Sun Apr 14 2013 - 01:41:36 CEST

Original text of this message