Re: Oracle Sequences

Daniel

Thanks for your suggestion. Usually a skip of numbers happen if there is a rollback. we validate all our data before commiting and then we use a trigger to get the next sequenxt into the table fires before insert. I could keep track of skipped sequences if needed. The reson I use a sequence as a Key is because the other values that I have for example SiteID and PatientNum are unique but sometimes they are entered incorrecly by a person using an web iterface and the we need to go and change the value. if the SiteID or PatientNum are primary keys that are references in other child tables then it s a royal pain in the ***** to change the vlaue. By using A sequence as the Primary key I can then make corrections other table values ablit still painfull but easier than changing a primary key

Thanks again all

Jawahar

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1074292005.118207_at_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 Sat Jan 17 2004 - 13:56:13 CST