2

I have a problem resetting Oracle Sequence to have MIN VALUE = 1 and starting next number used is 1.

I have followed through the answer of this question: How do I reset a sequence in Oracle? 

   create or replace procedure reset_seq( p_seq_name in varchar2 ) is
        l_val number; 
begin
        execute immediate
        'select ' || p_seq_name || '.nextval from dual' INTO l_val;

        execute immediate
        'alter sequence ' || p_seq_name || ' increment by -' || l_val || 
                                                              ' minvalue 0';

        execute immediate
        'select ' || p_seq_name || '.nextval from dual' INTO l_val;

        execute immediate
        'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0'; 
end;

But, the problem is the code above is resetting the sequence with min value = 0, and the next val to be used is 2.

Is it possible to set the min_value = 1, and nextval to be 1, after the reset is done?

I tried to set it but getting the error mentioning approximately as below:

The current value cannot be higher then Minimum Value.

Thanks!

Community
  • 1
  • 1
Hatjhie
  • 1,366
  • 2
  • 13
  • 27

1 Answers1

1

Is it possible to set the min value = 1, and next val to be 1, after the reset is done?

You could do it in two steps:

  • increment_by value one less than the current value of the sequence.
  • reset increment_by back to 1.

The logic is that, you shouldn't decrement the sequence back to zero, since the minvalue you want is 1, so, the nextval cannot be less than the minval.

For example,

SQL> CREATE SEQUENCE s START WITH 20 MINVALUE 0 INCREMENT BY 1;

Sequence created.

SQL> SELECT s.nextval FROM dual;

   NEXTVAL
----------
        20

SQL> ALTER SEQUENCE s INCREMENT BY -19 MINVALUE 1;

Sequence altered.

SQL> SELECT s.nextval FROM dual;

   NEXTVAL
----------
         1

SQL> ALTER SEQUENCE s INCREMENT BY 1 MINVALUE 1;

Sequence altered.

SQL> SELECT s.nextval FROM dual;

   NEXTVAL
----------
         2

SQL> SELECT min_value, increment_by FROM user_sequences WHERE sequence_name='S';

 MIN_VALUE INCREMENT_BY
---------- ------------
         1            1

So, the min_value and increment_by is now reset to 1 respectively. The nextvalue could be 1 only once before you reset the increment_by to 1 again.

So, I don't see any practical use of what you want to achieve. However, it could be done as demonstrated above.

To implement the above logic in your procedure, do as following:

Setup

SQL> DROP SEQUENCE S;

Sequence dropped.

SQL> CREATE SEQUENCE s START WITH 20 MINVALUE 0 INCREMENT BY 1;

Sequence created.

SQL> SELECT s.nextval FROM dual;

   NEXTVAL
----------
        20

Modify your procedure as:

SQL> CREATE OR REPLACE PROCEDURE reset_seq(
  2      p_seq_name IN VARCHAR2 )
  3  IS
  4    l_val NUMBER;
  5  BEGIN
  6    EXECUTE IMMEDIATE 'select ' || p_seq_name || '.nextval from dual' INTO l_val;
  7    l_val := l_val - 1;
  8    dbms_output.put_line('l_val = '||l_val);
  9    EXECUTE IMMEDIATE 'alter sequence ' ||
 10                       p_seq_name || ' increment by -' || l_val || ' minvalue 1';
 11    EXECUTE IMMEDIATE 'select ' || p_seq_name || '.nextval from dual' INTO l_val;
 12    dbms_output.put_line('1st Nextval is '||l_val);
 13    EXECUTE IMMEDIATE 'alter sequence ' || p_seq_name ||
 14                      ' increment by 1 MINVALUE 1';
 15    EXECUTE IMMEDIATE 'select ' || p_seq_name || '.nextval from dual' INTO l_val;
 16    dbms_output.put_line('2nd Nextval is '||l_val);
 17  END;
 18  /

Procedure created.

SQL> SET serveroutput ON
SQL> EXEC reset_seq('S');
l_val = 20
1st Nextval is 1
2nd Nextval is 2

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT min_value, increment_by FROM user_sequences where sequence_name='S';

 MIN_VALUE INCREMENT_BY
---------- ------------
         1            1

As I said, I don't see any practical use of it. Your nextval is practically usable only from 2. When it is 1, you need to do an ALTER SEQUENCE once again to reset the increment_by back to 1.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124