RE: ALTER SEQUENCE NEXTVAL

From: Toepke, Kevin M <ktoepke_at_cms.cendant.com>
Date: Thu, 7 Sep 2000 09:08:44 -0400
Message-Id: <10612.116401@fatcity.com>

Couldn't you do the following to set the sequence's nextval (in PL/SQL):

    SELECT seq_gn_special_offers_id.NEXTVAL     INTO some_var
    FROM dual;  

    EXECUTE IMMEDIATE ' ALTER SEQUENCE seq_gn_special_offers_id MINVALUE 1 MAXVALUE ' ||

                some_var + 1 || ' CYCLE';
 

    SELECT seq_gn_special_offers_id.NEXTVAL     INTO some_var
    FROM dual;  

    EXECUTE IMMEDIATE ' ALTER SEQUENCE seq_gn_special_offers_id MINVALUE 1 NOMAXVALUE ' ||

                ' NOCYCLE';

-----Original Message-----
From: Koivu, Lisa [mailto:lkoivu_at_qode.com] Sent: Thursday, September 07, 2000 9:30 AM To: Multiple recipients of list ORACLE-L Subject: ALTER SEQUENCE NEXTVAL

I thought I saw someone refer to doing this yesterday on the list. I swear I thought it was possible to alter a sequence to reset the nextval to a number you choose, ex., set it back to 1. Turns out the documentation doesn't state you can, and it errors out. Am I missing something? I am writing a procedure to drop and recreate the sequence instead.

  1* alter sequence seq_gn_special_offers_id nextval = 1 SQL> /
alter sequence seq_gn_special_offers_id nextval = 1

*
ERROR at line 1:
ORA-02286: no options specified for ALTER SEQUENCE

SQL> alter sequence seq_gn_special_offers_id set nextval = 1; alter sequence seq_gn_special_offers_id set nextval = 1

*
ERROR at line 1:
ORA-02286: no options specified for ALTER SEQUENCE

Lisa Rutland Koivu
Oracle Database Administrator (do I dare call myself that??) Qode.com
4850 North State Road 7
Suite G104
Fort Lauderdale, FL 33319

V: 954.484.3191, x174 
F: 954.484.2933 
C: 954.658.5849 

http://www.qode.com <http://www.qode.com>

"The information contained herein does not express the opinion or position of Qode.com and cannot be attributed to or made binding upon Qode.com." Received on Thu Sep 07 2000 - 08:08:44 CDT