On Friday 05 July 2002 07:23, Mercadante, Thomas F wrote: > Larry, > > I'm not as good as Jared, but maybe a little faster! Check the function > below out:
Faster? I published this 2 years ago. ;) Jared > > CREATE OR REPLACE FUNCTION Reset_Seq(in_seq_name IN VARCHAR2) RETURN NUMBER > IS > > current_value NUMBER; > TYPE refRS IS REF CURSOR; > p_refRS refRS; > select_str VARCHAR2(300) := 'select ' || in_seq_name || '.nextval from > dual'; > > BEGIN > > SELECT last_number > INTO current_value > FROM USER_SEQUENCES > WHERE sequence_name = UPPER(in_seq_name); > > EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || in_seq_name || > ' INCREMENT BY ' || (current_value-1) * -1; > > OPEN p_refRS FOR > select_str; > FETCH p_refRS INTO current_value; > CLOSE p_refRS; > > EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || in_seq_name || ' INCREMENT BY 1'; > > RETURN 1; > > END; > > > > Tom Mercadante > Oracle Certified Professional > > > -----Original Message----- > Sent: Friday, July 05, 2002 9:19 AM > To: Multiple recipients of list ORACLE-L > > > Jack, > > But then you have to redo grants, any invalidations. No big deal > automating. But if you set the increment to a negative to take you right > back down to zero, then set the increment to 1 (or whatever it was), there > you go, no dropping the sequence. > > 10 to 1 odds Jared posts a nice little script that automates this second > approach ;-) > > Regards, > > Larry G. Elkins > [EMAIL PROTECTED] > 214.954.1781 > > > -----Original Message----- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jack Silvey > > Sent: Friday, July 05, 2002 8:03 AM > > To: Multiple recipients of list ORACLE-L > > Subject: Re: resetting sequence numbers > > > > > > drop sequence, recreate sequence, rinse, repeat. > > > > Jack > > > > --- "Malik, Fawzia" <[EMAIL PROTECTED]> wrote: > > > Hi, > > > > > > Is there a way of resetting sequence numbers back to > > > zero???The application > > > we have needs to have the sequence numbers reset... > > > > > > Thanks in advance > > > > > > Rgds > > > > > > Fawzia > > > > ********************************************************************** > > > > > Information in this email is confidential and may be > > > privileged. > > > It is intended for the addressee only. If you have > > > received it in error, > > > please notify the sender immediately and delete it > > > from your system. > > > You should not otherwise copy it, retransmit it or > > > use or disclose its > > > contents to anyone. > > > Thank you for your co-operation. > > > > ********************************************************************** > > > > > -- > > > Please see the official ORACLE-L FAQ: > > > http://www.orafaq.com > > > -- > > > Author: Malik, Fawzia > > > INET: [EMAIL PROTECTED] > > > > > > 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: [EMAIL PROTECTED] (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). > > > > __________________________________________________ > > Do You Yahoo!? > > Sign up for SBC Yahoo! Dial - First Month Free > > http://sbc.yahoo.com > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Jack Silvey > > INET: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).