Re: Generating Sequence Numbers

A copy of this was sent to Kevin Mackie <mackster_at_my-deja.com> (if that email address didn't require changing) On Tue, 21 Sep 1999 22:49:33 GMT, you wrote:

>Is there a non-platform specific way to generate sequence numbers using
>only SQL?
>

Yes, but the ALL serialize. Viturally every commercial database has a non-blocking way to do this (serial type in informix, sequences in oracle and so on). You'd be best off building a little 'database specific' code into your apps to handle this.

The way to do it for all databases would be:

have a table such as:

create table sequence_Table( seq_name varchar(x), current_value int ); insert into sequence_table values ( 'MySeq', 0 );

do a transaction like:

  begin tran (if needed)
  update sequence_table

     set current_value = current_value+1    where seq_name = 'MySeq';

  select current_value from sequence_table where seq_name = 'MySeq';    

  ....
  commit (as soon as feasible to let others get a sequence)

The update will block others -- serialize the requests.

>Apologies for the non-Oracle specific question in this forum, but it
>seemed like the best place for it.
>
>Thanks,
>
>Kevin
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Sep 22 1999 - 10:03:53 CDT