Re: automatic unique key genration

Charles Jardine (cj10_at_cam.ac.uk) wrote:
: Ramesh K Meda <74053.36_at_CompuServe.COM> wrote:
: >Yes, unique key could be generated for primary key. There are two
: >different ways.
: >
: >1. Use sequence and set value for new.<column_name>.
: >2. Use max(PrimaryColumnName) + 1 to set new value.
: >
: >Depending on the platform and oracle release you might be using,
: >you may receive "Mutating" error. Hope, you know how to solve
: >this.
 

: Solution 2 above is simply wrong, first because of the mutating
: problem and secondly because two concurrent transactions may well
: see the same value of max(PrimaryColumnName) + 1. Any way of cheating
: to get round there restriction on selecting from mutating tables
: will make the second effect more likely.
 

: The whole point of sequences is that they are outside the transaction
: structure, and can deliver guaranteed uniquness without blocking locks.
: The only cost is that sequence numbers can be 'lost' because NEXTVALs
: given to transaction which roll back are never given to anyone else.

Yes, but you can achieve this by use of one "counter table" simulating sequence numbers.

--
--
--
create table counter_table
( pk            varchar2(3) not null primary key,
  counter_value number(10) not null );

insert into counter_table values ( PrimaryKeyValue, 1);

function get_sequence
return number is
begin
  declare
  return_value number;
  cursor c1 is select counter_value from counter_table
  where pk = PrimaryKeyValue for update of counter_value nowait;
  pragma exception_init(locked,-54);
  begin
    open c1;
    fetch c1 into return_value;
    update counter_table set counter_value := counter_value + 1
    where current of c1;
    close c1;
    return(return_value);
    exception
      when locked then
        MyExceptionHandler
  end;
end; 
--
--
--

This function guarantees sequence numbers in required order with no "holes".
The cost is in the fact that counter_table can be locked and thus inaccessible 
for other users. If possible, isuuing commit after each call to get_sequence 
will solve this problem.

Cheers!.