Script to reset sequence begin with 1


Create this procedure:

CREATE OR REPLACE PROCEDURE reset_sequence (
seq_name IN VARCHAR2, -- nama sequence
startvalue IN PLS_INTEGER -- reset ke nilai ini
) AS
cval INTEGER;
inc_by VARCHAR2(25);
BEGIN
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' ||seq_name||' MINVALUE 0';
EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM dual' INTO cval;
cval := cval - startvalue + 1;
IF cval < 0 THEN
inc_by := ' INCREMENT BY ';
cval:= ABS(cval);
ELSE
inc_by := ' INCREMENT BY -';
END IF;
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || inc_by || cval;
EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM dual' INTO cval;
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || ' INCREMENT BY 1';
END reset_sequence;
/

Run the procedure with parameter sequence_name
BEGIN reset_sequence('ABSENT_NOTIFICATION_SEQ',1); END;
/

How the procedure works?

  1. Get the last value of the sequence.
  2. Change the increment with – the last value of the sequence so the last value become 0.
  3. Change the increment with 1.
  4. Run the Nextval to set the sequence to 1.

4 responses to “Script to reset sequence begin with 1”

  1. yani says :

    kayaknya lbh gampang:
    1. drop sequence
    2. create sequence

    • ericwijaya says :

      hehe.. ini mau reset semua user_sequence soalnya. jadi perlu instan bisa dipake berulang2..

      • yani says :

        begin
        drop sequence
        drop sequence
        drop sequence

        end;

        begin
        create sequence
        create sequence
        create sequence

        end;

        hehehee

  2. ericwijaya says :

    hmm… cara baru boleh juga… mungkin oracle perlu tambahin sintaks alter sequence seq restart from 1. :p

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: