RE: ALTER SEQUENCE NEXTVAL

From: Tim Sawmiller <sawmillert_at_state.mi.us>
Date: Thu, 07 Sep 2000 10:00:01 -0400
Message-Id: <10612.116405@fatcity.com>

Well, you could really be sneaky by changing the increment value (even to = a minus value!), fire the sequence select, then change the increment value = back.

>>> ktoepke_at_cms.cendant.com 09/07/00 10:20AM >>>
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;
=20

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

                some_var + 1 || ' CYCLE';

=20

    SELECT seq_gn_special_offers_id.NEXTVAL     INTO some_var
    FROM dual;
=20

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

                ' NOCYCLE';

-----Original Message-----
Sent: Thursday, September 07, 2000 9:30 AM To: Multiple recipients of list ORACLE-L

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.=20

  1* alter sequence seq_gn_special_offers_id nextval =3D 1=20 SQL> /=20
alter sequence seq_gn_special_offers_id nextval =3D 1=20

                                        *=20

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

SQL> alter sequence seq_gn_special_offers_id set nextval =3D 1;=20 alter sequence seq_gn_special_offers_id set nextval =3D 1=20

                                        *=20

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

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

V: 954.484.3191, x174=20
F: 954.484.2933=20
C: 954.658.5849=20

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

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

--=20
Author: Toepke, Kevin M
  INET: ktoepke_at_cms.cendant.com=20

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may Received on Thu Sep 07 2000 - 09:00:01 CDT