Re: Incremental value in a field [forms 6i]

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:<tsqpqfiid3l450_at_corp.supernews.com>...
> You can create the sequence with the nocache option.
> Other than that the existence of gaps in a sequence can IMO not be
> considered as a disaster, as sequence numbers are used as *meaningless* id's
> anyway
>
> Duplicate values shouldn't occur if you select the max value for update of
> the id, so the table is locked when you calculate the new max.
> Compared to sequences though this is a very inefficient solution.
>
> Regards,
>
> Sybrand Bakker
> Senior Oracle DBA
>

Thank you for your response.

My problem relate to the sequence solution is that, in case of rollback during the insert, remains a "hole" in the ids succesion, and it's not acceptable.

The other solution (select max()) is not good, because the application post the changes to db during the navigation process, whitout do a COMMIT immediatly (a COMMIT will do on explicit user request). Between the POST and the COMMIT commands the record is locked, and any other user trying to insert must wait until the first user do an explicit COMMIT; this is NOT acceptable.

I need to:
1. Assign a temporary id at the beginning of the insert process. 2. Do one or more POST to database (so the temporary id must be UNIQUE).
3. Before the COMMIT, "adjust" the id to avoid duplicates and holes.

My ideas are:
1. Create a sequence to assign the initial id, and assign the real id before the COMMIT using a SELECT max(id).

2. Use temporary tables for insert, and copy all data into real tables before the COMMIT, assigning the id on the usual way.

Which is the best way?
Other suggestions?

Thank yuo very much.

Bye
Diego Received on Thu Oct 18 2001 - 09:45:31 CEST