Oracle is not known for being the easiest database to work with. This is especially true when trying to implement auto increment for a column. It’s pretty normal to want to have a unique ID column that is automatically incremented for a table… In MySQL and Microsoft SQL, you can simply use the AUTO INCREMENT (or AUTO_INCREMENT) keyword when writing the DDL for a table. In Oracle, however, there is no built-in option for this. Instead, Oracle introduced a “sequence” object to handle auto increment of values.

There are some major downsides to using a sequence:

  • It’s fragile. You have to create and keep track of yet another database object.
  • It’s not automatic. You have to call my_sequence_name.nextval manually for every insert.
  • It’s messy. You must create a new, unique sequence for every field you wish to auto increment.
  • It’s not resettable. There is no easy way to “reset” a sequence, i.e., start it over from the original minimum value.

The last point is especially annoying when testing or developing new code. Sequences keep incrementing higher and higher and can’t be easily reset without dropping and recreating them all over again. Enjoy dealing with that in a system that has hundreds of sequences to manage. Yes, I have lived exactly that. To prevent insanity, here is a stored procedure I wrote that will dynamically reset a sequence to the original minimum value. It alters the sequence so that the “increment by” value will decrement it back to the original minimum starting point. It runs “nextval” to perform the reset, then restores the original “increment by’ setting for actual use. Note that you have to store the nextval result in a local variable (v_result in my procedure) even if you never use it, or else Oracle will act like it never ran nextval.

/*
PROCEDURE: reset_sequence
DESCRIPTION: Resets a sequence to the original minimum value
USAGE: exec reset_sequence('NAME_OF_SEQUENCE');
*/
create procedure reset_sequence(v_sequence_name_in in varchar2)
as
v_reset_value number;
v_increment_value number;
v_result number;
begin
execute immediate 'select'||v_sequence_name_in||'.currval-min_value
from user_sequences
where sequence_name = ''' || v_sequence_name_in || ''''
 into v_reset_value;

  execute immediate ‘select increment_by
from user_sequences
where sequence_name = ”’ || v_sequence_name_in || ””
into v_increment_value;

if v_reset_value > 0 then
execute immediate ‘alter sequence ‘ || v_sequence_name_in || ‘
increment by ‘ || -v_reset_value;

execute immediate ‘select ‘ || v_sequence_name_in || ‘.nextval
from dual’ into v_result;

execute immediate ‘alter sequence ‘ || v_sequence_name_in || ‘
increment by ‘ || v_increment_value;
end if;

end reset_sequence;

Personally, I can’t stand dealing with sequences. When I have a choice, I prefer to get around using them entirely. You can do this by creating a trigger that auto-increments the column value. For example, here is a trigger I might create to auto-increment the column MY_ID_COL on the table MY_TABLE. This simply adds 1 (or whatever increment you want) to the max value from the table column. It’s not as quick as using sequences, but it’s way easier to maintain.

create trigger my_table_trigger
before insert on my_table
for each row
begin
select nvl(max(my_id_col), 0) + 1
into :new.my_id_col
from my_table;

end;

Benefits of the trigger method are:

  • It’s standard. It uses a trigger object, which is standard SQL across the board.
  • It’s automatic. It runs automatically upon row insertion.
  • It self-resets. It detemines the next value from the table column itself, not an arbitrary counter.
Like this post? Share it!