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

aggregate-functions, oracle, oracle10g, plsql, sequences

Solution

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;

Problem

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"?

Original source