Posted by

0 Flares Twitter 0 Facebook 0 Google+ 0 StumbleUpon 0 Email -- Filament.io 0 Flares ×

Using Oracle sequence nextval you can autonumber a column in a table, that column is usually the primary key. This is useful if you want to increment that column with each new record. This way you do not have to know the max of that column before issuing an insert statement. Assuming the sequence is called CUSTOMER_SEQ and you are inserting a record into the CUSTOMER table is would look like this.

INSERT INTO CUSTOMER VALUES(CUSTOMER_SEQ.NEXTVAL,'Bo','Smith');

The sequence keeps the following information

  • CREATED
  • LAST_DDL_TIME
  • SEQUENCE_OWNER
  • SEQUENCE_NAME
  • MIN_VALUE
  • MAX_VALUE
  • INCREMENT_BY
  • CYCLE_FLAG
  • ORDER_FLAG
  • CACHE_SIZE
  • LAST_NUMBER

The most relevant part of the sequence are the MIN_VALUE,MAX_VALUE,LAST_NUMBER and INCREMENT_BY .

If a table has a sequence and you do not use oracle sequence nextval when inserting new records then you will have to reset(increment) the sequence. The sequence does not know when you insert a row without using it. So if you where to do

SELECT MAX(CUSTOMER_NUMBER) FROM CUSTOMER;

and receive 123 back as the result of the query then you would know to use 124 as the next value for CUSTOMER_NUMBER when inserting a new record.

If this occurs, the next time oracle sequence nextval is used to insert a new record into that table is will try to use 124 as the next value which is already taken and will cause an error. You will then have to reset the sequence as said before. I have a script that will do this for you and here’s the code

Code to reset the Oracle Sequence Nextval

create or replace
procedure Reset_Sequence( p_seq_name in varchar2, 
p_val in number default 0) 
is 
l_current number := 0; 
l_difference number := 0; 
l_minvalue user_sequences.min_value%type := 0; 

--takes sequence name and new start number, 
--then sets that value for specified sequence
begin 
--get min value for sequence
select min_value 
into l_minvalue 
from user_sequences 
where sequence_name = UPPER(p_seq_name); 

--get current sequence value
execute immediate 
'select ' || UPPER(p_seq_name) || '.nextval from dual' 
INTO l_current; 

--can't set the value to something smaller than the min value
if p_Val < l_minvalue then 
l_difference := l_minvalue - l_current; 
else 
l_difference := p_Val - l_current; 
end if; 

if l_difference = 0 then 
return; 
end if; 

execute immediate 
--set increment to difference
'alter sequence ' || UPPER(p_seq_name) || ' increment by ' 
|| l_difference || 
' minvalue ' || l_minvalue; 

--run sequence to imcreament to value specified
execute immediate 
'select ' || UPPER(p_seq_name) || '.nextval from dual' 
INTO l_difference; 

--reset increment value back to 1
execute immediate 
'alter sequence ' || UPPER(p_seq_name) ||
 ' increment by 1 minvalue ' || l_minvalue; 
end Reset_Sequence;

This script takes the sequence name which can be in lower or upper case, and the new LAST_NUMBER value which you can get by querying for the max column that the sequence uses.

To create a sequence mentioned above you would use the code

CREATE SEQUENCE CUSTOMER_SEQ
 START WITH     1
 INCREMENT BY   1
 NOCACHE

NOCACHE means that the sequence does not preallocate numbers. You can specify a CACHE number for the sequence to preallocate for performance purposes.