sequence reset sequence

language/DB 2013. 9. 10. 12:59

http://stackoverflow.com/questions/51470/how-do-i-reset-a-sequence-in-oracle 


CREATE or REPLACE PROCEDURE reset_sequence (sequencename IN VARCHAR2) as

      curr_val INTEGER;

   BEGIN

     EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' MINVALUE 0';

       EXECUTE IMMEDIATE 'SELECT ' ||sequencename ||'.nextval FROM dual' INTO curr_val;

       EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' increment by -'||curr_val;

       EXECUTE IMMEDIATE 'SELECT ' ||sequencename ||'.nextval FROM dual' INTO curr_val;

      EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' increment by 1';

    END reset_sequence;

'language > DB' 카테고리의 다른 글

sql 정규식  (0) 2013.09.26
여러행을 한줄로  (0) 2013.09.26
merge 문  (0) 2013.09.25
DB2 procedure  (0) 2013.08.16
ORA-01502 오류  (0) 2013.07.26
: