Re: How to increased by exactly 1 ?

Den (dd_at_hotmail.net) wrote:
: How can I guarantee a column's value is increased by exactly 1 for every new
: row added ?

: I cannot use sequence and .nextval to achieve this as there is no guarantee
: the value is increased by exactly 1.

I am no sequence expert, but that isn't what I read from the oracle manual.

	"A sequence is a schema object that generates sequential numbers.
	When you create a sequence, you can specify its initial value and
	an increment."

It seams to me that the only caveats are the cache option (cached sequence numbers can be lost if the database goes down) and if you rollback after allocating a number (the number isn't reused).

The solution presumably is to not use the cache option, and to not generate the sequence number until you have checked everything else is valid. I suppose that last might be hard, but if so, then any code you write will have just as hard a time ensuring the numbers are correctly sequential.

$0.02

Otherwise, try writing the record without a number, commit, and then update the entire table to reset the sequential numbers. Obviously some care must be taken in that last step - already assigned numbers must stay the same, and you'll want to think how to make it efficient. However, since the update will in reality only touch a few rows, so there should be some way to make it more efficient than actually updating the entire table.

If your insert routine needs to know the number assigned to the row it just created, then use the primary key to find the row and get what number was assigned.

Other routines that just read the table should always leave out the rows that do not yet have the sequence number set.

....that's just a thought...

--

This space not for rent.