START NEW VALUE OF SEQUENCE WITHOUT DROP THE SEQUENCE

SQL> CREATE OR REPLACE PROCEDURE SEQUENCE_NEWVALUE(
  2  seqname VARCHAR2,
  3  newvalue NUMBER) AS
  4  ln NUMBER;
  5  ib NUMBER;
  6  BEGIN
  7  SELECT last_number, increment_by
  8  INTO ln, ib
  9  FROM USER_sequences
 10  WHERE  sequence_name = upper(seqname);
 11  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seqname ||
 12  ' INCREMENT BY ' || (newvalue - ln);
 13  EXECUTE IMMEDIATE 'SELECT ' || seqname ||
 14  '.NEXTVAL FROM DUAL' INTO ln;
 15  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seqname
 16  || ' INCREMENT BY ' || ib;
 17  END;
 18  /

Procedure created.
SQL> CREATE SEQUENCE TEST;

Sequence created.

SQL> SET LONG 10000
SQL> SET PAGESIZE 0
SQL> SELECT DBMS_METADATA.GET_DDL('SEQUENCE','TEST') FROM DUAL;

   CREATE SEQUENCE  "NETVERTEX"."TEST"  MINVALUE 1 MAXVALUE 99999999999999999999
99999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE


SQL> SET SERVEROUTPUT ON
SQL> EXEC SEQUENCE_NEWVALUE('TEST',100);

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_METADATA.GET_DDL('SEQUENCE','TEST') FROM DUAL;

   CREATE SEQUENCE  "NETVERTEX"."TEST"  MINVALUE 1 MAXVALUE 99999999999999999999
99999999 INCREMENT BY 1 START WITH 100 CACHE 20 NOORDER  NOCYCLE


SQL> SELECT TEST.NEXTVAL FROM DUAL;
       100

SQL> /
       101

SQL> /
       102

🙂

Advertisements

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