4

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

Community
  • 1
  • 1
Jason Baker
  • 192,085
  • 135
  • 376
  • 510
  • 1
    Erm, is there any chance your current sequence value is higher than the max value from the table column? You might want to change the script to only alter the sequence, if that is not the case. – Juergen Hartelt Sep 03 '09 at 17:38
  • 1
    The other thing is, remember to reset the INCREMENT BY after selecting NEXTVAL once. – APC Sep 04 '09 at 08:41

1 Answers1

3

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;
Justin Cave
  • 227,342
  • 24
  • 367
  • 384