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
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.