| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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_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:
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:
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
"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 ListsReceived on Thu Sep 07 2000 - 12:57:54 CDT
--------------------------------------------------------------------
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> SELECT = seq_gn_special_offers_id.NEXTVAL</FONT> <BR><FONT SIZE=3D2> INTO = some_var</FONT> <BR><FONT SIZE=3D2> FROM dual;</FONT> <BR><FONT SIZE=3D2> </FONT> <BR><FONT SIZE=3D2> EXECUTE IMMEDIATE ' ALTER = SEQUENCE seq_gn_special_offers_id MINVALUE 1</FONT> <BR><FONT SIZE=3D2>MAXVALUE ' ||</FONT> <BR><FONT = SIZE=3D2> &nb= sp; some_var + 1 || ' CYCLE';</FONT> <BR><FONT SIZE=3D2> </FONT> <BR><FONT SIZE=3D2> SELECT = seq_gn_special_offers_id.NEXTVAL</FONT> <BR><FONT SIZE=3D2> INTO = some_var</FONT> <BR><FONT SIZE=3D2> FROM dual;</FONT> <BR><FONT SIZE=3D2> </FONT> <BR><FONT SIZE=3D2> EXECUTE IMMEDIATE ' ALTER = SEQUENCE seq_gn_special_offers_id MINVALUE 1</FONT> <BR><FONT SIZE=3D2>NOMAXVALUE ' ||</FONT> <BR><FONT = SIZE=3D2> &nb= sp; ' 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. 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. Am I = missing something? I am</FONT> <BR><FONT SIZE=3D2>writing a procedure to drop and recreate the = sequence instead. </FONT> </P> <P><FONT SIZE=3D2> 1* alter sequence seq_gn_special_offers_id = nextval =3D 1 </FONT> <BR><FONT SIZE=3D2>SQL> / </FONT> <BR><FONT SIZE=3D2>alter sequence seq_gn_special_offers_id nextval =3D = 1 </FONT> <BR><FONT = SIZE=3D2> &nb= sp; &nb= sp; &nb= sp; * </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> 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> &nb= sp; &nb= sp; &nb= sp; * </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 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> <<A = HREF=3D"http://www.qode.com" = TARGET=3D"_blank">http://www.qode.com</A>> </FONT> </P> <P><FONT SIZE=3D2>"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."</FONT> </P> <P><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Author: Toepke, Kevin M</FONT> <BR><FONT SIZE=3D2> INET: ktoepke_at_cms.cendant.com</FONT> </P> <P><FONT SIZE=3D2>Fat City Network Services -- (858) = 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>San Diego, = California -- 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 =
 |
 |