Re: Oracle Sequences

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Fri, 16 Jan 2004 14:27:59 -0800
Message-ID: <1074292005.118207@yasure>

Robert A.M. van Lopik wrote:

> "Ed prochak" <ed.prochak_at_magicinterface.com> wrote in message
> news:4b5394b2.0401161145.45c5b364_at_posting.google.com...
>

>>Galen Boyer <galenboyer_at_hotpop.com> wrote in message

>
> news:<u8yk8t0mk.fsf_at_standardandpoors.com>...
>

>>>On Fri, 16 Jan 2004, jrajan_at_nc.rr.com wrote:
>>>
>>>
>>>>Due to the nature of our work skipping sequences is not a good
>>>>practice.
>>>
>>>There has never been a sound argument yet for this.  If you
>>>actually understand some trait of your ids and use it in your
>>>efforts you have intelligence in your keys.  That is a big
>>>no-no.
>>
>>Well, never say "never".
>>
>>Some financial applications require no numeric gaps, for example
>>invoice numbering. Auditors hate gaps.
>>
>>The downside is loss of scalability since you are essentially forced
>>to have a gating process to "single source" the numbers, whether they
>>are generated from a SEQUENCE or by SELECT MAX(thenumberkey). It's a
>>pain but sometimes has to be done.

>
>
> You are right, Ed, these are normal requirements from accountants. On the
> one hand you can correctly argue that these requirements are wrong, because
> non-missing numbers don't prove anything. On the other hand, these
> requirements usually stem from manual procedures where it is easy to correct
> errors (without a trace usually) so as to keep the numbering sequential. If
> that is the case (i.e. automating a manual procedure), performance probably
> will not be an issue; automating the process will not cause the number of
> invoices sent to increase from one per hour to one per millisecond :-). That
> would be expecting to much from IT!
>
> rob van lopik
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.558 / Virus Database: 350 - Release Date: 3-1-2004

It the concern is just one of missing numbers one can easily account for missing numbers by using a table to hold them along with an explanation column. Just write numbers to that table in your error handler. Same logic can be written to account for any sequence numbers that might evaporate due to a shutdown by using a BEFORE SHUTDOWN and AFTER STARTUP triggers.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Fri Jan 16 2004 - 16:27:59 CST