Message-Id: <10612.116429@fatcity.com> From: "Koivu, Lisa" Date: Thu, 7 Sep 2000 13:57:54 -0400 Subject: RE: ALTER SEQUENCE NEXTVAL This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C018F5.25BFA186 Content-Type: text/plain; charset="iso-8859-1" Kevin, thanks for your clever response! Lisa -----Original Message----- From: Toepke, Kevin M [mailto:ktoepke@cms.cendant.com] Sent: Thursday, September 07, 2000 10:20 AM To: Multiple recipients of list ORACLE-L Subject: RE: ALTER SEQUENCE NEXTVAL 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----- 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. 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 "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." -- Author: Toepke, Kevin M INET: ktoepke@cms.cendant.com 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@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 also send the HELP command for other information (like subscribing). ------_=_NextPart_001_01C018F5.25BFA186 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: ALTER SEQUENCE NEXTVAL

Kevin, thanks for your clever response!
Lisa

-----Original Message-----
From: Toepke, Kevin M [mailto:ktoepke@cms.cendant.com]
Sent: Thursday, September 07, 2000 10:20 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: ALTER SEQUENCE NEXTVAL


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 ' ||
          &nb= sp;     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 ' ||
          &nb= sp;     ' 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.

  1* alter sequence seq_gn_special_offers_id = nextval =3D 1
SQL> /
alter sequence seq_gn_special_offers_id nextval =3D = 1
          &nb= sp;           &nb= sp;           &nb= sp;     *
ERROR at line 1:
ORA-02286: no options specified for ALTER SEQUENCE =

SQL> alter sequence seq_gn_special_offers_id set = nextval =3D 1;
alter sequence seq_gn_special_offers_id set nextval = =3D 1
          &nb= sp;           &nb= sp;           &nb= sp;     *
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."

--
Author: Toepke, Kevin M
  INET: ktoepke@cms.cendant.com

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@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 =