Reset Sequence in Oracle 10g

[lang_it]
Cercando in rete non sono riuscito a trovare una funzione che mi permettesse di cambiare il valore ad una sequence definita sul mio DB oracle, spesso accade che dopo un importazione di dati ci sia un disallinamento delle chiavi fisiche del DB con le relative sequence. A questo proposito ho scritto una funzione che permette appunto di cambiare alcuni parametri di una sequence esistente sul vostro database senza dover ogni volta scrivere una decina di istruzioni SQl.

Il codice di tale funzione :

CREATE OR REPLACE FUNCTION F_RESET_SEQUENCE(SEQUNECE_NAME VARCHAR, CURRENT_VALUE VARCHAR DEFAULT '0', INCREMENT_BY VARCHAR DEFAULT '1', MINVAL VARCHAR DEFAULT '0') RETURN NUMBER IS
tmpVar NUMBER;
I  INTEGER;
/******************************************************************************
   NAME:       F_RESET_SEQUENCE
   PURPOSE:    

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        30/11/2010          1. Created this function.

   NOTES:

   Automatically available Auto Replace Keywords:
      Object Name:     F_RESET_SEQUENCE
      Sysdate:         30/11/2010
      Date and Time:   30/11/2010, 12.40.53, and 30/11/2010 12.40.53
      Username:         (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/
 TYPE EmpCurTyp IS REF CURSOR;  -- define weak REF CURSOR type
 emp_cv   EmpCurTyp;  -- declare cursor variable
 emp_cv1   EmpCurTyp;  -- declare cursor variable
 
 PRAGMA AUTONOMOUS_TRANSACTION;
 
BEGIN

  OPEN emp_cv FOR  -- open cursor variable
	      'SELECT (0-(CONFIGURAZIONE_SEQ.CURRVAL - 1)) AS VAL FROM DUAL';
	   LOOP
	     FETCH emp_cv INTO tmpVar;  -- fetch next row
		 EXIT WHEN emp_cv%NOTFOUND;  -- exit loop when last row is fetched
	     -- process row
	   END LOOP;
   
  CLOSE EMP_CV;

  IF (TMPVAR <> 0) THEN
     EXECUTE IMMEDIATE('ALTER SEQUENCE ' || SEQUNECE_NAME || ' INCREMENT BY ' || CAST(TMPVAR AS VARCHAR) ||' MINVALUE 0');
	  
	  
	  
	  OPEN emp_cv FOR  -- open cursor variable
		      'SELECT ' || SEQUNECE_NAME || '.NEXTVAL AS VAL FROM dual';
		   LOOP
		     FETCH emp_cv INTO tmpVar;  -- fetch next row
			 EXIT WHEN emp_cv%NOTFOUND;  -- exit loop when last row is fetched
		     -- process row
		   END LOOP;
	   
	  CLOSE EMP_CV;
	  
  END IF;
  
  IF (CURRENT_VALUE <> '0') THEN
  
	  EXECUTE IMMEDIATE('ALTER SEQUENCE ' || SEQUNECE_NAME || ' INCREMENT BY ' || CURRENT_VALUE ||' MINVALUE 0');
	  
	  OPEN emp_cv FOR  -- open cursor variable
		      'SELECT ' || SEQUNECE_NAME || '.NEXTVAL FROM dual';
		   LOOP
		     FETCH emp_cv INTO tmpVar;  -- fetch next row
			 EXIT WHEN emp_cv%NOTFOUND;  -- exit loop when last row is fetched
		     -- process row
		   END LOOP;
	   
	  CLOSE EMP_CV;
  END IF;
  
  EXECUTE IMMEDIATE('ALTER SEQUENCE ' || SEQUNECE_NAME || ' INCREMENT BY ' || INCREMENT_BY);
  
  OPEN emp_cv FOR  -- open cursor variable
	      'SELECT ' || SEQUNECE_NAME || '.CURRVAL FROM dual';
	   LOOP
	     FETCH emp_cv INTO tmpVar;  -- fetch next row
		 EXIT WHEN emp_cv%NOTFOUND;  -- exit loop when last row is fetched
	     -- process row
	   END LOOP;
   
  CLOSE EMP_CV; 
  COMMIT;
  
  RETURN tmpVar;
  
END F_RESET_SEQUENCE;

per usare la funzione ad esempio , suppenendo che ho una sequnce che si chiama “configurazione_seq” e volessi portare il suo valore attuale a 1000 basterebbe eseguire il seguente comando :

SELECT F_RESET_SEQUENCE('CONFIGURAZIONE_SEQ','1000') FROM dual;

[/lang_it]

[lang_en]
here is a function that allow to change the state of a sequence defined in an oracle db without drop and recreate the sequence.

the code of the function :

CREATE OR REPLACE FUNCTION F_RESET_SEQUENCE(SEQUNECE_NAME VARCHAR, CURRENT_VALUE VARCHAR DEFAULT '0', INCREMENT_BY VARCHAR DEFAULT '1', MINVAL VARCHAR DEFAULT '0') RETURN NUMBER IS
tmpVar NUMBER;
I  INTEGER;
/******************************************************************************
   NAME:       F_RESET_SEQUENCE
   PURPOSE:    

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        30/11/2010          1. Created this function.

   NOTES:

   Automatically available Auto Replace Keywords:
      Object Name:     F_RESET_SEQUENCE
      Sysdate:         30/11/2010
      Date and Time:   30/11/2010, 12.40.53, and 30/11/2010 12.40.53
      Username:         (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/
 TYPE EmpCurTyp IS REF CURSOR;  -- define weak REF CURSOR type
 emp_cv   EmpCurTyp;  -- declare cursor variable
 emp_cv1   EmpCurTyp;  -- declare cursor variable
 
 PRAGMA AUTONOMOUS_TRANSACTION;
 
BEGIN

  OPEN emp_cv FOR  -- open cursor variable
	      'SELECT (0-(CONFIGURAZIONE_SEQ.CURRVAL - 1)) AS VAL FROM DUAL';
	   LOOP
	     FETCH emp_cv INTO tmpVar;  -- fetch next row
		 EXIT WHEN emp_cv%NOTFOUND;  -- exit loop when last row is fetched
	     -- process row
	   END LOOP;
   
  CLOSE EMP_CV;

  IF (TMPVAR <> 0) THEN
     EXECUTE IMMEDIATE('ALTER SEQUENCE ' || SEQUNECE_NAME || ' INCREMENT BY ' || CAST(TMPVAR AS VARCHAR) ||' MINVALUE 0');
	  
	  
	  
	  OPEN emp_cv FOR  -- open cursor variable
		      'SELECT ' || SEQUNECE_NAME || '.NEXTVAL AS VAL FROM dual';
		   LOOP
		     FETCH emp_cv INTO tmpVar;  -- fetch next row
			 EXIT WHEN emp_cv%NOTFOUND;  -- exit loop when last row is fetched
		     -- process row
		   END LOOP;
	   
	  CLOSE EMP_CV;
	  
  END IF;
  
  IF (CURRENT_VALUE <> '0') THEN
  
	  EXECUTE IMMEDIATE('ALTER SEQUENCE ' || SEQUNECE_NAME || ' INCREMENT BY ' || CURRENT_VALUE ||' MINVALUE 0');
	  
	  OPEN emp_cv FOR  -- open cursor variable
		      'SELECT ' || SEQUNECE_NAME || '.NEXTVAL FROM dual';
		   LOOP
		     FETCH emp_cv INTO tmpVar;  -- fetch next row
			 EXIT WHEN emp_cv%NOTFOUND;  -- exit loop when last row is fetched
		     -- process row
		   END LOOP;
	   
	  CLOSE EMP_CV;
  END IF;
  
  EXECUTE IMMEDIATE('ALTER SEQUENCE ' || SEQUNECE_NAME || ' INCREMENT BY ' || INCREMENT_BY);
  
  OPEN emp_cv FOR  -- open cursor variable
	      'SELECT ' || SEQUNECE_NAME || '.CURRVAL FROM dual';
	   LOOP
	     FETCH emp_cv INTO tmpVar;  -- fetch next row
		 EXIT WHEN emp_cv%NOTFOUND;  -- exit loop when last row is fetched
	     -- process row
	   END LOOP;
   
  CLOSE EMP_CV; 
  COMMIT;
  
  RETURN tmpVar;
  
END F_RESET_SEQUENCE;

an example of use , suppose we have a sequence whose name is “configurazione_seq” and we need to set the current value to 1000 :

SELECT F_RESET_SEQUENCE('CONFIGURAZIONE_SEQ','1000') FROM dual;

[/lang_en]

ciao
Ivan