lcasamen (2/8/2011)
Thanks for the quick replies! I don't need to worry about gaps in sequences - Oracle loses values on rollback too, and I just need matching functionality. Long term we were planning to migrate to identities, just too much code to do by the delivery date - but good to know sequences are coming.I'm sure I'll be back.
I didn't even realize this was the continuation of an thread from back in 2009. Re-reading your post from yesterday, you also mention the following:
...there is a ton of bad code that fetches the next value and then uses it to create data - sometimes creating the child before the parent...
It sounds as if there are currently no foreign key constraints declared to prevent orphan rows from being inserted into child tables. If creating foreign keys isn't already on your to-do list for this database re-factoring project, then I'd definately suggest that as well. Of course this would also involve retrofitting all the procedures that are attempting to insert orphan rows.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho