Re: Getting NEXTVAL just prior to a COMMIT

Craig Harper <caharper_at_hooked.net> wrote:

>Scott,
 

>Thanks for the tip, unfortunatly we do care about losing the numbers. We
>are going to use them both internally and externally.
 

>ie. Your account number is 12345676,
 

>That is why we would rather keep as many numbers as possible. barring
>some one closing their account.

I thought of the sequence problem a lot, and sometimes the users even want to see the new number as they input their data. While they still can change their mind about the whole record and discard it, you have a hole in you numbering scheme. Two solutions:

keep two tables for the numbers. One for the current number and one for the discarded numbers. When you have to issue a new number, first query the 'old' table and when that doesn't produce a result (ie there are no discarded numbers) you update the current number table and there you are. Simple and safe, when you do the update (or delete from the old table when applicable) at the time you need the number so you don't lock the table unnecessary or too long. Tried this quite a few times and it works.

The second solution I've never tried but it should work. When you insert a new record you can do a nextval on the sequence in a post commit trigger. When you need a new number you only have to query the sequence with a curval which doesn't affect the sequence. So the user can see the number and when they change their mind there's no problem. One possible problem arises when two user do a insert at approx the same time. Then you would have two of the same number. You can catch this out by doing a check prior to the insert (in a pre-insert) or code the on-insert yourself and trap the unique key violation. When that occurs you just do a nextval and you're allright. I admit that it doesn't sound too elegant, but I think it should work. Thoughts anyone ?

+------------------------------------------------------------+
|   Mike Philippens - Gorinchem - The Netherlands, Europe    |
|                 Vijfhart Automatisering bv                 |
|       Oracle Specialists Training and Consultancy          |
|                 Utrecht    The Netherlands                 |
+------------------------------------------------------------+