Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: ALTER SEQUENCE NEXTVAL

RE: ALTER SEQUENCE NEXTVAL

From: Koivu, Lisa <lkoivu_at_qode.com>
Date: Thu, 7 Sep 2000 13:57:54 -0400
Message-Id: <10612.116429@fatcity.com>


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_at_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 <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_at_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_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 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 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2650.12"> <TITLE>RE: ALTER SEQUENCE NEXTVAL</TITLE> </HEAD> <BODY> <P><FONT SIZE=3D2>Kevin, thanks for your clever response!</FONT> <BR><FONT SIZE=3D2>Lisa</FONT> </P> <P><FONT SIZE=3D2>-----Original Message-----</FONT> <BR><FONT SIZE=3D2>From: Toepke, Kevin M [<A = HREF=3D"mailto:ktoepke_at_cms.cendant.com">mailto:ktoepke_at_cms.cendant.com</= A>]</FONT> <BR><FONT SIZE=3D2>Sent: Thursday, September 07, 2000 10:20 AM</FONT> <BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=3D2>Subject: RE: ALTER SEQUENCE NEXTVAL</FONT> </P> <BR> <P><FONT SIZE=3D2>Couldn't you do the following to set the sequence's = nextval (in PL/SQL):</FONT> <BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp; SELECT = seq_gn_special_offers_id.NEXTVAL</FONT> <BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp; INTO&nbsp;&nbsp;&nbsp; = some_var</FONT> <BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp; FROM dual;</FONT> <BR><FONT SIZE=3D2>&nbsp;</FONT> <BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp; EXECUTE IMMEDIATE ' ALTER = SEQUENCE seq_gn_special_offers_id MINVALUE 1</FONT> <BR><FONT SIZE=3D2>MAXVALUE ' ||</FONT> <BR><FONT = SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb= sp;&nbsp;&nbsp;&nbsp;&nbsp; some_var + 1 || ' CYCLE';</FONT> <BR><FONT SIZE=3D2>&nbsp;</FONT> <BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp; SELECT = seq_gn_special_offers_id.NEXTVAL</FONT> <BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp; INTO&nbsp;&nbsp;&nbsp; = some_var</FONT> <BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp; FROM dual;</FONT> <BR><FONT SIZE=3D2>&nbsp;</FONT> <BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp; EXECUTE IMMEDIATE ' ALTER = SEQUENCE seq_gn_special_offers_id MINVALUE 1</FONT> <BR><FONT SIZE=3D2>NOMAXVALUE ' ||</FONT> <BR><FONT = SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb= sp;&nbsp;&nbsp;&nbsp;&nbsp; ' NOCYCLE';</FONT> </P> <P><FONT SIZE=3D2>-----Original Message-----</FONT> <BR><FONT SIZE=3D2>Sent: Thursday, September 07, 2000 9:30 AM</FONT> <BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT> </P> <BR> <BR> <P><FONT SIZE=3D2>I thought I saw someone refer to doing this yesterday = on the list.&nbsp; I swear</FONT> <BR><FONT SIZE=3D2>I thought it was possible to alter a sequence to = reset the nextval to a</FONT> <BR><FONT SIZE=3D2>number you choose, ex., set it back to 1. Turns out = the documentation</FONT> <BR><FONT SIZE=3D2>doesn't state you can, and it errors out.&nbsp; Am I = missing something?&nbsp; I am</FONT> <BR><FONT SIZE=3D2>writing a procedure to drop and recreate the = sequence instead. </FONT> </P> <P><FONT SIZE=3D2>&nbsp; 1* alter sequence seq_gn_special_offers_id = nextval =3D 1 </FONT> <BR><FONT SIZE=3D2>SQL&gt; / </FONT> <BR><FONT SIZE=3D2>alter sequence seq_gn_special_offers_id nextval =3D = 1 </FONT> <BR><FONT = SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb= sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb= sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb= sp;&nbsp;&nbsp;&nbsp;&nbsp; * </FONT> <BR><FONT SIZE=3D2>ERROR at line 1: </FONT> <BR><FONT SIZE=3D2>ORA-02286: no options specified for ALTER SEQUENCE = </FONT> </P> <P><FONT SIZE=3D2>SQL&gt; alter sequence seq_gn_special_offers_id set = nextval =3D 1; </FONT> <BR><FONT SIZE=3D2>alter sequence seq_gn_special_offers_id set nextval = =3D 1 </FONT> <BR><FONT = SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb= sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb= sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb= sp;&nbsp;&nbsp;&nbsp;&nbsp; * </FONT> <BR><FONT SIZE=3D2>ERROR at line 1: </FONT> <BR><FONT SIZE=3D2>ORA-02286: no options specified for ALTER SEQUENCE = </FONT> </P> <P><FONT SIZE=3D2>Lisa Rutland Koivu </FONT> <BR><FONT SIZE=3D2>Oracle Database Administrator (do I dare call myself = that??) </FONT> <BR><FONT SIZE=3D2>Qode.com </FONT> <BR><FONT SIZE=3D2>4850 North State Road 7 </FONT> <BR><FONT SIZE=3D2>Suite G104 </FONT> <BR><FONT SIZE=3D2>Fort Lauderdale, FL&nbsp; 33319 </FONT> </P> <P><FONT SIZE=3D2>V: 954.484.3191, x174 </FONT> <BR><FONT SIZE=3D2>F: 954.484.2933 </FONT> <BR><FONT SIZE=3D2>C: 954.658.5849 </FONT> <BR><FONT SIZE=3D2><A HREF=3D"http://www.qode.com" = TARGET=3D"_blank">http://www.qode.com</A> &lt;<A = HREF=3D"http://www.qode.com" = TARGET=3D"_blank">http://www.qode.com</A>&gt;&nbsp; </FONT> </P> <P><FONT SIZE=3D2>&quot;The information contained herein does not = express the opinion or position</FONT> <BR><FONT SIZE=3D2>of Qode.com and cannot be attributed to or made = binding upon Qode.com.&quot;</FONT> </P> <P><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Author: Toepke, Kevin M</FONT> <BR><FONT SIZE=3D2>&nbsp; INET: ktoepke_at_cms.cendant.com</FONT> </P> <P><FONT SIZE=3D2>Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) = 538-5051&nbsp; FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>San Diego, = California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet = access / Mailing Lists</FONT> <BR><FONT = SIZE=3D2>---------------------------------------------------------------=
-----</FONT>
<BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an = E-Mail message</FONT> <BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of = 'ListGuru') and in</FONT> <BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB = ORACLE-L</FONT> <BR><FONT SIZE=3D2>(or the name of mailing list you want to be removed =
Received on Thu Sep 07 2000 - 12:57:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US