How do I get a sequence's MINVALUE in Oracle 10g PL/SQL?

Question

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

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 sequence's MINVALUE is greater than the max primary key:

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

ORA-06512: at line 10

What's the easiest way to say "increment the sequence by increment_amount, but don't go below MINVALUE"?

Answer 1

You can query the DBA_SEQUENCES table to get the MIN_VALUE

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

You could then incorporate that into your code, i.e.

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