Reset Oracle sequence which increments by 50

Question

Issue is I'm unable to reset the sequence back to '1'. I Have created the sequence as below...

DROP SEQUENCE TEST_SEQ;
CREATE SEQUENCE TEST_SEQ MINVALUE 1 START WITH 1 INCREMENT BY 50;

when doing 'nextval', the output is 1.

SELECT TEST_SEQ.NEXTVAL FROM DUAL; --> output is '1'

Below procedure is from this community which I am using for resetting the sequence

CREATE OR replace PROCEDURE RESET_SEQ(p_seq_name IN VARCHAR2) 
IS 
  l_val      NUMBER; 
  l_minvalue user_sequences.min_value%TYPE; 
BEGIN 
    SELECT min_value 
    INTO   l_minvalue 
    FROM   user_sequences 
    WHERE  sequence_name = Upper(p_seq_name); 

    EXECUTE IMMEDIATE 'SELECT '||p_seq_name||'.NEXTVAL FROM DUAL' INTO l_val; 

    l_val := l_val - l_minvalue; 

    EXECUTE IMMEDIATE 'ALTER SEQUENCE '||p_seq_name||'INCREMENT BY -'||l_val|| 
    'MINVALUE '||l_minvalue; 

    EXECUTE IMMEDIATE 'SELECT '||p_seq_name||'.NEXTVAL FROM DUAL' INTO l_val; 

    EXECUTE IMMEDIATE 'ALTER SEQUENCE '||p_seq_name||'INCREMENT BY 50 MINVALUE ' 
    ||l_minvalue; 
END; 
/

BEGIN
RESET_SEQ('TEST_SEQ');
END;
/

After resetting the sequence when I check nextvalue, I am getting the output as 51. But I need the output as 1.

SELECT TEST_SEQ.NEXTVAL FROM DUAL; --> output is '51'.

Only when I drop and recreate the sequence I am able to get the nextvalue as '1'.

Please help me in modifying the above procedure so that my output is '1'.

Possible duplicate of [How do I reset a sequence in Oracle?](http://stackoverflow.com/questions/51470/how-do-i-reset-a-sequence-in-oracle) — Aleksej, Jan 17 '17 at 18:02
Possible duplicate of [Reset Oracle Sequence to have MIN VALUE = 1 and STARTING number from 1](http://stackoverflow.com/questions/30158866/reset-oracle-sequence-to-have-min-value-1-and-starting-number-from-1) — Gurwinder Singh, Jan 17 '17 at 19:06

score 1 · Accepted Answer · answered Jan 17 '17 at 18:06

You should check for errors and print out the SQL statements you want to execute. Then you wouldn't have to both with Stack Overflow:

EXECUTE IMMEDIATE 'SELECT '||p_seq_name||'.NEXTVAL FROM DUAL' INTO l_val; 

    l_val := l_val - l_minvalue; 

    EXECUTE IMMEDIATE 'ALTER SEQUENCE '||p_seq_name||'INCREMENT BY -'||l_val|| 
    'MINVALUE '||l_minvalue; 

    EXECUTE IMMEDIATE 'SELECT '||p_seq_name||'.NEXTVAL FROM DUAL' INTO l_val; 

    EXECUTE IMMEDIATE 'ALTER SEQUENCE '||p_seq_name||' INCREMENT BY 50 MINVALUE ' 
------------------------------------------------------^ space
    ||l_minvalue; 
END;

For this reason, I prefer using replace() to construct such strings. For instance:

v_sql := 'ALTER SEQUENCE [p_seq_name] INCREMENT BY -[l_val] MINVALUE [l_minvalue]';
v_sql := replace(v_sql, '[p_seq_name]', p_seq_name);
v_sql := replace(v_sql, '[l_val]', l_val);
v_sql := replace(v_sql, '[l_minvalue]', l_minvalue);

score 0 · Answer 2 · answered Jan 18 '17 at 09:41

You had to insert a space to

EXECUTE IMMEDIATE 'ALTER SEQUENCE '||p_seq_name||'INCREMENT BY -'||l_val|| 
'MINVALUE '||l_minvalue;

in order for it to be

EXECUTE IMMEDIATE 'ALTER SEQUENCE '||p_seq_name||' INCREMENT BY -'||l_val|| 
'MINVALUE '||l_minvalue;

The same goes for

EXECUTE IMMEDIATE 'ALTER SEQUENCE '||p_seq_name||'INCREMENT BY 50 MINVALUE ' 
||l_minvalue;

After that it should work.

DROP SEQUENCE TEST_SEQ;
/
CREATE SEQUENCE TEST_SEQ MINVALUE 1 START WITH 1 INCREMENT BY 50;
/
SELECT TEST_SEQ.NEXTVAL FROM DUAL; --> output is '1'
/
SELECT TEST_SEQ.NEXTVAL FROM DUAL; --> output is '51'
/
SELECT TEST_SEQ.CURRVAL FROM DUAL; --> output is '51'
/
CREATE OR replace PROCEDURE RESET_SEQ(p_seq_name IN VARCHAR2) 
IS 
l_val      NUMBER; 
l_minvalue user_sequences.min_value%TYPE; 
BEGIN 
SELECT min_value 
INTO   l_minvalue 
FROM   user_sequences 
WHERE  sequence_name = Upper(p_seq_name); 

EXECUTE IMMEDIATE 'SELECT '||p_seq_name||'.NEXTVAL FROM DUAL' INTO l_val; 

l_val := l_val - l_minvalue; 

EXECUTE IMMEDIATE 'ALTER SEQUENCE '||p_seq_name||' INCREMENT BY -'||l_val|| 
'MINVALUE '||l_minvalue; 

EXECUTE IMMEDIATE 'SELECT '||p_seq_name||'.NEXTVAL FROM DUAL' INTO l_val; 

EXECUTE IMMEDIATE 'ALTER SEQUENCE '||p_seq_name||' INCREMENT BY 50 MINVALUE ' 
||l_minvalue; 
END; 
/
BEGIN
RESET_SEQ('TEST_SEQ');
END;
/
SELECT TEST_SEQ.CURRVAL FROM DUAL; --> output is '1'
/

Reset Oracle sequence which increments by 50

2 Answers2