Mar 3, 2010

Reset oracle sequence procedure

Oracle sequence cannot reset by command , need workaround to achieve original start value.

CREATE OR REPLACE PROCEDURE reset_sequence (
seq_name IN VARCHAR2) AS
cval INTEGER;
inc_by VARCHAR2(30);
BEGIN
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' ||seq_name||' MINVALUE 0';
EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM dual' INTO cval;
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;
/

0 意見: