How to get MINVALUE sequence in Oracle 10g PL / SQL?

Question

I wrote a PL / SQL script to set the sequence value to the maximum value of the primary key of the table:

DECLARE max_idn NUMERIC(18, 0); seq_nextval NUMERIC(18, 0); increment_amount NUMERIC(18, 0); BEGIN SELECT MAX(mbr_idn) INTO max_idn FROM mbr; SELECT mbr_seq.nextval INTO seq_nextval FROM DUAL; increment_amount := max_idn - seq_nextval; EXECUTE IMMEDIATE 'ALTER SEQUENCE mbr_seq increment by ' || increment_amount; END; 

However, I get an error if the MINVALUE sequence is greater than the maximum primary key:

ORA-08004: the sequence MBR_SEQ.NEXTVAL goes below MINVALUE and cannot be created

ORA-06512: on line 10

What is the easiest way to say: "Incrementing a sequence using increment_amount, but not lower than MINVALUE"?

Answer 1

You can query table DBA_SEQUENCES to get MIN_VALUE

 SELECT min_value INTO l_min_value FROM all_sequences WHERE sequence_name = 'MBR_SEQ' AND owner = <<sequence owner>> 

Then you can include this in your code, i.e.

 increment_amount := GREATEST( max_idn, l_min_value ) - seq_nextval;