Re: sequence omits numbers?

Hi Stefan Röhle, thanks for writing this:
> Ok, the database does not care...
> But traditionally (and I cannot change that) we use numbers composed of
> a special scheme and certain number ranges indicate some properties
> connected with it (e.g. numbers between 1 and 199 indicate that those
> invoices return every year, numbers between 200 and 2999 are for general
> purposes etc.).
> So I have to make sure that they do not leave the intervals which can
> happen if there are many missing numbers.
>
> Alan wrote:
> > What is wrong with having missing numbers? Generally, the idea is to have
> > unique numbers, which you have. The database does not care if numbers are
> > missing.
> >
> >
> > "Stefan Röhle" <roehle_at_uni-mainz.de> wrote in message
> > news:bgo4g6$35c$1_at_bambi.zdv.Uni-Mainz.DE...
> >
> >>Hi there,
> >>
> >>I have encountered a strange problem: a sequence seems to leave out
> >>numbers - how can that happen?
> >>
> >>But let me describe the setup briefly:
> >>For out internal online ordering system I use a function (stored in db,
> >>lets call it new_number()) that creates new invoice numbers. These have
> >>to be unique so new_number() fetches a new number from a sequence for
> >>each invoice number.
> >>The whole application consists of a few webpages based on php using the
> >>pear db.php to communicate with our oracle 8i server.
> >>Each time a user creates a new invoice new_number() is called in order
> >>to supply a new number. (it is only possible to get a new number once,
> >>so there is no problem with the 'reload' button of the browser)
> >>Everything is logged so that I can review which user has created which
> >>new invoice-number etc.
> >>When everything is ok, the numbers have no gaps inbetween, but sometimes
> >>there are small gaps (3 or 4 numbers missing) and even large gaps (30-50
> >>numbers missing).
> >>
> >>I have no idea how that can happen especially because I log each call of
> >>the new_number() function in my php-scripts...
> >>In order to troubleshoot that I first had a trigger in mind that fires
> >>when the function is called but since that is not possible I have no
> >>further idea how to find the error. Any ideas?
> >>
> >>Stefan
> >>
> >>--
> >>*******************************
> >>Stefan Röhle
> >>Zentrum für Datenverarbeitung
> >>Johannes Gutenberg-Universität
> >>D-55099 Mainz
> >>Germany
> >>
> >>Tel. +49-(0)6131/39-26303
> >>Fax. +49-(0)6131/39-26407
> >>Email: roehle_at_uni-mainz.de
> >>*******************************
> >>
> >
> >
> >
>
>

Maybe this technique will work in your situation (though I'd test it to make sure you aren't creating a huge bottleneck):

Create (insert) a bunch of "empty" invoices with a status code indicating that it's available. When you want a new invoice, grab the next available empty one (select for update), then modify (update) the invoice with new data, and set the status code to not available. If someone rolls back, then the status code will roll back to available again.

-- 
[:%s/Karsten Farrell/Oracle DBA/g]