Total Pageviews

2020/09/07

[Oracle] How to reset sequence value

How-To


Example

    PROCEDURE SP_RESET_SEQUENCE AS
        seq_exists VARCHAR2(1) := 'T';
    BEGIN
        SELECT CASE WHEN COUNT(*) > 0 THEN 'T' ELSE 'F' END
            INTO seq_exists
        FROM all_sequences
        WHERE sequence_name = 'SEQ_TEST' AND sequence_owner = 'TEST' ;

        IF seq_exists = 'T'
            THEN execute immediate 'DROP SEQUENCE TEST.SEQ_TEST';
        END IF;

        execute immediate 'create sequence TEST.SEQ_TEST start with 1 increment by 1 MAXVALUE 9999 cycle';

    END SP_RESET_SEQUENCE;


No comments: